【効率化ナレッジ】Google Apps Script（GAS）を使って月別数値レポートを自動複製する方法

広告運用をしていると、Google スプレッドシートでデータを整理することがあると思います。毎月、コピーしてシートを作成しても、時間がかかってしまいますよね。用途別にシートがあると、数が多くなりなおさらです。必要なタイミングでシートが自動で複製されたらどんなに助かることか・・・。

そこで今回は、未経験の私でもできた、GAS（Google Apps Script）を使って、シートを複製する作業の自動化の方法をお教えします！

この記事では Google スプレッドシートの複製を自動化して、手動複製の課題点を解決した事例の紹介と、 Google スプレッドシートの複製を GAS で毎月末自動的におこなえるようにする方法を解説します。

コピー＆ペーストで設定できるようにサンプルコードを掲載していますので、プログラミングをしたことがない方も安心してチャレンジしてくださいね。ぜひ GAS を使って小さな手間を減らしていきましょう！

Google Apps Script（GAS）とは

Google Apps Script（GAS） とは、Google が提供しているプログラミング言語で、Google の各種サービスやその他のツールなどと連携ができます。また、Web サイトから任意の情報の収集や簡易的な Web アプリケーションを作成することもできます。特に、Google サービス内でおこなう作業を自動化するのに最適な言語です。

なお、プログラミング言語の中でも比較的人間的な感覚で読み書きしやすいものはスクリプト言語と呼ばれ、JavaScript をもとに作られた GAS もその1つです。

Google Apps Script を使うには？

プログラミング初心者の場合は、開発環境の構築と呼ばれるプログラムを書く準備だけでも苦労することが多いと思います。しかし、GAS の場合は Google アカウントとインターネットに接続されたパソコン、ブラウザがあれば準備完了です。また、Google アカウントは無料で作れるため、興味があればすぐに使うことができます。

GAS を使ってシート複製作業を自動化

広告配信の成果を記録する際には、インプレッションやクリック、コストなどの項目が必要です。また、広告を配信している媒体ごとに数値を見ることができると便利ですよね。

弊社では、以下の画像のようなスプレッドシートを使って広告運用結果のパフォーマンスを毎日記録しています。

運用担当者やクライアントの要望に合わせて、シートの形式には細かい差があるため、複製作業は思った以上に複雑です。

しかし GAS を活用することで、スプレッドシートなどの規則性ある作業を自動でおこなうことができます。シートを毎月末に複製して、翌月に使えるように整える作業を手動でしていた時は下記のような課題がありました。

• 複製のミス（数式を誤って削除する、複製元の数値を消し忘れるなど）
• 単純作業に時間を費やす

そこで GAS を用いてスプレッドシートの複製を自動化することで上記の課題が解決できました。節約できた分の時間は広告の入稿や複雑な数値集計作業など、自動化が難しい業務に活用しています。

実際に GAS を使ってみましょう

ここからは、GAS を使ってスプレッドシートを自動で複製する方法をお伝えします。コピー＆ペーストで使うことができるサンプルコードを掲載しているので、ぜひ順を追って挑戦してみてください！

シートの準備

まずはコードを書く前の準備をしましょう。はじめに、スプレッドシートを用意します。スプレッドシートのオーナーでないとプログラムの認証に手間がかかったり、プロジェクトの削除をおこなえないなど不便なので注意が必要です。

今回使うシートはこちらからアクセスし、左上の「ファイル」から「コピー」を作成をクリック、その後出てくる小さなウィンドウで「OK」をクリックすれば、マイドライブにコピーができます。

最初にシート名をつけます。今回解説するコードではシート名を元に複製元のシートを判断するため、シート名は「西暦_月（YYYY_MM）」の形式でつけています。

コピーしたスプレッドシート上の  YYYY_MM というシートの名前を、作業する日に合わせて今月に変更しましょう。

例えば、作業日が2022年1月25日ならば、「2022_01」とします。また、このシートの B2セルも同様に作業する日に合わせて「西暦/月/01」と変更してみてください。これでシートの準備は完了です。

GAS のエディタの準備

GAS のコードを書き込むエディタを起動しましょう。先ほど用意したスプレッドシートの上部に表示されている、「拡張機能」をクリックします。次に「Apps Script」の文字をクリックすると別タブに GAS のエディタが開きます。

初回起動時は読み込み途中で止まってしまうことがあるため、その際はシートごと閉じてから再度同じ手順をおこなうとエディタが開きます。

エディタ上部の「無題のプロジェクト」という箇所はプロジェクト名です。特に決まりはないので、分かりやすい名称に変更しておきましょう。

エディタを開いた直後、コードを書く場所は、「function myFunction() {}」と記述されていますが、今回は不要なのですべて削除します。

function myFunction() {
}

GAS のエディタの保存は手動でおこなう必要があるため、編集をおこなった際には上部にある保存ボタンか、 Ctrl + S での保存を忘れないようにしましょう。

また、コードを書く際はアルファベットと数字、記号は必ず半角で入力します。さらに、大文字小文字は別のものとして判断されるので注意が必要です。

実際にコードを使ってみよう！

今回自動で複製したいシートには、画像のように値とメモを削除したい範囲が3つ、背景色を削除したい範囲が1つあるという想定です。

下記は今回のシートに対応したコードです。シートを複製し、シート名に翌月の名前を付けたうえで、シート内の不要な値とメモ、背景色を削除することができます。

//動作時の今月YYYY_MMのシートを複製する関数
function AutoSheetCopy(){
  
  //このプログラムが入っているスプレッドシートを操作する
  var ssActiveSheet = SpreadsheetApp.getActiveSpreadsheet(); 
  //今日の日付を取得する
  var datToday = new Date(); 
  //今月YYYY_MMをつくる
  var datThisMonth = Utilities.formatDate(datToday,"JST","yyyy_MM");
  //今日の年と月を取得する
  var todayYear = datToday.getFullYear();
  var todayMonth = datToday.getMonth();
  //翌月1日をつくる
  var datNextMonth1st　= new Date(todayYear,todayMonth + 1,1);
  //翌月YYYY_MMをつくる
  var datNextMonth　=　Utilities.formatDate(datNextMonth1st,"JST","yyyy_MM");  
   
  //今月YYYY_MMのシートを取得する
  var ssTemplateSheet = ssActiveSheet.getSheetByName(datThisMonth);
  //シートを複製し、できたシートの名前を翌月YYYY_MMにする
  ssActiveSheet.insertSheet(datNextMonth, 0, {template: ssTemplateSheet}); 
  
  //複製してできたシートのB2セルに、翌月1日の日付を入れる
  ssActiveSheet.getRange('B2').setValue(datNextMonth1st);
   
  //Yahoo!検索
  //M4:Q34の値とメモを削除する
  ssActiveSheet.getRange('M4:Q34').clear({contentsOnly: true,commentsOnly: true});
  //Google
  //W4:AA34の値とメモを削除する
  ssActiveSheet.getRange('W4:AA34').clear({contentsOnly: true,commentsOnly: true});
  //Facebook
  //AG4:AK34の値とメモを削除する
  ssActiveSheet.getRange('AG4:AK34').clear({contentsOnly: true,commentsOnly: true});
  //日付の列
  //B4:B34の背景色のみを削除する
  ssActiveSheet.getRange('B4:B34').setBackground(null);  
}

記載されたコードで何度もでてくる表記を以下にまとめました。

コードの詳細については、どんな作業をおこなっているか解説します。また、シートに合わせて編集した方がよい箇所はその都度説明するので、お手持ちのシートの複製を自動化したい場合は参考にしてみてください。

コード解説1.日付に関わる処理とシートの複製

2行目にある function と () の間の部分を関数名といいます。エディタを起動した時に用意されている myFunction のままでも問題ないですが、プログラムの動作を表す名前を付けると分かりやすいため、今回は AutoSheetCopy としています。なお、関数名の1文字目に数字を使用することはできません。

そして、シートを操作する際には、まず操作したいスプレッドシートを取得する必要があります。今回は5行目で、コードが格納されているスプレッドシート自身を取得しています。

ここから日付に関わるコードです。7行目にある「new Date」は、「Fri Jan 21 2022 19:27:54 GMT+0900 (Japan Standard Time)  」のような形でコードが実行された日時を取得します。9行目の「Utilities」で日時を YYYY_MMの形式に整えます。

11行目と12行目で、先ほど取得したコードが実行された日時から年と月を取り出します。14行目ではこれを使って翌月の1日を作り、16行目で翌月のシート名に使用したいYYYY_MM の形式に整えます。

ここまでで操作に必要な日付が揃ったのでシートを複製します。

19行目では、先ほど9行目で作成した YYYY_MM の名前のシート（今月のシート）を取得します。21行目では、今月のシートを複製元にして新規シートを作成し、同時に先ほど作成した翌月 YYYY_MM をシート名に設定することを指示しています。

今回のシートでは B2セルに毎月1日の日付が必要です。新しく作成された翌月 YYYY_MM シートの B2セルに、14行目で作成した翌月の1日の日付を入力します。また、getRange()の中に入力するセル番地によって、操作するセルを指定することができます。

日付を入力したいセルの位置が違う場合は、書き換えてみてください。例えば、翌月の1日の日付をG10セルに入力したい場合は、「ssActiveSheet.getRange(‘G10’).setValue(datNextMonth1st);」のようにします。

コード解説2.不要な値とメモ、背景色の削除

ここまでの処理では、B2セルの日付が変更された点以外、翌月シートの中身は今月シートとまったく同じ状態です。表の中には今月シートに入力された数値が残っているので削除していきましょう。

以下は、26行目から34行目の不要な値とメモを削除するコードです。clear()はセルの書式と値をすべて削除するものです。

表の枠組みや背景色など書式を維持したまま値のみを削除したい場合は clear({contentsOnly: true})とすることで可能です。

また、メモのみを削除したい場合は clear({commentsOnly: true}) とします。今回の場合は書式を維持したまま値とメモのみを削除するため、clear({contentsOnly: true,commentsOnly: true}) と書きます。

値とメモを削除したいセル範囲が複数ある場合は、それらに合わせてコードを複数回書きます。また、操作したいセル範囲に合わせて、getRange(‘M4:Q34’)の部分を書き換えます。

例えば、下記の画像の（1）の場合は getRange(‘B2:B9’)、（2）の場合は getRange(‘D2:E6’)のようにします。

次にセルに塗られた色を消すコードです。施策の開始日や祝日など、目印でセルに色を塗ることはありませんか。これが翌月分シートに残ると紛らわしいので消してしまいましょう。

こちらも先ほどと同様に不要な数値などを削除したいセル範囲に合わせて、必要に応じて getRange(‘B4:B34’)の部分を書き換えます。

プログラムの実行

必要なコードをすべて書き終えたら、一度手動で実行してみましょう。エディタ上部に今回作成した関数名「AutoSheetCopy」が表示されている状態で、実行をクリックします。

プログラムをはじめて実行する際は認証画面が出てきます。プログラムでシートを操作する権限を与えるための認証です。

「権限を確認」をクリックし、次の画面で自分のアカウントを選択してから「認証」をクリックして問題ありません。その後翌月のシートが作成されることを確認できれば、プログラムの完成です。

トリガー設定をし、指定のタイミングで自動的にプログラムを動かす

現時点では、先ほどのようにプログラムを手動で実行させなければシートが複製されないため、トリガーを設定します。

トリガーとは、指定したタイミングで自動的にプログラムを起動してくれるものです。GAS の処理は Google のサーバーでおこなわれます。

そのため、トリガーを設定してしまえばパソコンを起動していなかったり、スプレッドシートを開いていない場合でも自動的にシートが複製されます。

今回は毎月月末頃に翌月のシートを複製することを想定して、28日がきたらプログラムを動かしてくれるトリガーを設定してみましょう。

まず、エディタの画面の左上にある時計マークをクリックすると、トリガー設定画面が開くので、右下の「トリガーを追加」をクリックします。

次にトリガーの詳細設定画面が開きます。以下のように実行する関数や時刻などを設定して右下の保存をクリックしてください。日や時刻は場合に合わせて設定を変更できます。

この設定をおこなった月の28日からは、自動的に翌月のシートが複製されるはずです。ただし、下記の場合はプログラムが正常に動かないため注意が必要です。共同編集者がいる場合には事前に注意事項を共有しておきましょう。

• 複製元となる今月のシート名が変更されたり、シート自体が削除されている
• 翌月の名前がついたシートがすでに存在している

小さな業務効率化の積み重ねが余裕を生み出す

この記事を読んで、普段使っているあのシートも自動複製できるな、GAS って便利だなと思ってもらえたら嬉しいです。早速チャレンジしようと思った方、ぜひやってみてくださいね！

今回のようにシートを複製するというシンプルな作業でも、それらが積み重なり私たちの日々を忙しくしています。つまり、小さな業務の効率化ひとつひとつが余裕を生み出すチャンスだと思います。この記事を読んだ方の毎日に少しでも余裕が生まれますように！