【GAS】Google Apps Script 活用事例　異なるファイル間のコピペの自動化とINDIRECT関数を併用して月次集計を完全に自動化する方法

毎日コツコツ自動化で、大きなリターンをッ！！

毎日、コツコツ自動化して、勉強して、毎日繰り返す作業を分析し、「これ自動化したら楽そうだな....こういう感じで出来ないかな？」みたいな事をひたすら考えています。

プログラミングは、そんな簡単ではありません。特にロジックからゼロベースで考えなければいけないものは、それ相応に時間が掛かります。ただし、そうして積み上げたものは、30日、60日と長期スパンで考えると、仮に1日1つしか自動化が出来なくても、60日で考えたとき、60もの作業や工程が効率化ないし自動化されたという事になります。

バックオフィス業務だと、月次作業で毎月繰り返すものが結構あります。そうしたものの自動化に取り組んできて、最近、そのリターンが計り知れないくらい大きくなってきました。

特定のシートを他のスプレッドシートにコピーする

function setValues() {
 var spreadSheet     = SpreadsheetApp.getActiveSpreadsheet();
 var sheet           = spreadSheet.getSheetByName('コピーしたいシート名');
 var values          = sheet.getDataRange().getValues();
 
 /*コピー先のファイルに、新規シートを作成*/
 var anotherSpreadsheet = SpreadsheetApp.openById('*************');
 var getSheetsLength    = anotherSpreadsheet.getSheets().length;
 Logger.log(getSheetsLength);
 
 /*シートの作成場所の指定　一番右に配置、シート40などの名前でシートを取得*/
 var newSheet     = anotherSpreadsheet.insertSheet(getSheetsLength);
 var newSheetName = newSheet.getName();
 Logger.log(newSheetName);
 
 /*日付*/
 var date  = new Date(); //現在日時のDateオブジェクトを作る
 var today = Utilities.formatDate(date, 'JST', 'yyyy/MM/dd');
 var month = date.getMonth()+1; //※getMonthの返り値が0～11(1月～12月)なので、+1してる
 
 //Dateオブジェクトに1ヶ月前の月を設定したいので、「月」に-2をセットする
 date.setMonth(month-2);
 var lastMonth = Utilities.formatDate(date, 'JST', 'yyyy/MM/');
 
 /*リネームした後のシートを取得*/
 var activeSheet  = anotherSpreadsheet.getSheetByName(newSheetName);
 var rename       = activeSheet.setName(lastMonth + '01').getName();//当月が2月だったら2020/01/01
 var renameSheet  = anotherSpreadsheet.getSheetByName(rename);
 Logger.log(renameSheet);
 
 /*月次集計内容を集計ファイルの新規シートに貼り付け*/
 var setValues    = renameSheet.getRange(1, 1, values.length, values[0].length).setValues(values); 
 
}

var anotherSpreadsheet = SpreadsheetApp.openById('*************');

コピーしたい先のファイルをIDで指定します。https://docs.google.com/spreadsheets/d/この部分がID/edit#gid=0

このスクリプトを実行すると、下記のスクリーンショットのようにシートの右側に月次データがリネームされた状態でコピーされます。

集計用シート

E列以降に月次データ、実務では、各スタッフに寄せられたクレーム件数を集計する作業に使いましたが、個人別の売り上げとか工数管理とか、そんなのにも使えそうな気がします。

D列にSUMが入っていて、それぞれの行データを集計します。

シート名が変わったら、関数を修正する作業と、さよなら

=VLOOKUP(A2,INDIRECT(TEXT(E1,"YYYY/MM/DD")&"!A:Z"),3,0)

/*元の形*/
=VLOOKUP(A2,'2020/01/01'!A:Z,3,0)

INDIRECT関数の中にTEXT関数を入れ子にしてあげると、シート名が変わっていても、関数をコピーするだけで、各月次データが、自動反映されます。
今回の場合で言えば、見出しとシート名が完全一致になっています。シートのコピー、リネームをスクリプトで行ない、集計を関数で行なっています。

この関数を組むまでは、月初になったらシート名の部分を編集し直すという事をしていたのですが、それが無くなりました。素敵。

全部スクリプトでやろうとすると苦しくなるので、関数でカバーできる所は関数でやってしまい、使い分けをした方が個人的には楽かなと思います。

EDATE関数で○ヶ月後をお手軽記入

EDATE関数は、1ヶ月後など、起算月から何ヶ月後というのを、簡単に算出してくれます。便利です。

EOMONTH関数　も便利です。当月データの月末日を確認する事が出来ます。End Of Monthだから、EOMONTHらしいです。今回は使う機会がありませんでしたが、覚えておいて損はありません。

今回の自動化では、何か超絶すごいスクリプトとかがある訳ではなく、単に他のスプレッドシートに特定のシートをコピーするだけです。しかし、組み合わせ方によって、普段無意識に繰り返している作業を完全自動にすることが出来ます。Let's自動化ライフを送ってみてください。