【GAS】Google Apps Script 活用事例　週次集計の自動化とSlack通知機能の実装

「月次クレームの集計データを、もっと早く、週単位で把握する事が出来ないかな？」という一言で始まった、週次集計。何曜日を起点に1週間と捉えるのか？月を跨いだ時はどうするのか？難しいだろうなぁと思いっていたのですが、あるんですね！！こういう状況にピッタリな模範解答を用意してくれる関数が！！

ISOWEEKNUM関数

1年を53週で捉え、何週目かを判定する関数です。これとCOUNTIFなどを組み合わせれば、その週に起きたクレーム件数などを集計出来ます。出来るだけ手作業を無くし、効率的に仕組みを回す方法を考えたいと思います。

=ISOWEEKNUM(A1)

シートの作り方（データ入力、データ処理、閲覧）

V8になってから、ARRAYFORMULA関数をシート側で使用していると、.filter(String)で最終行の取得が出来なくなった？ような気がします。そのため、データ入力、データ処理、閲覧の3つにシートを分けて集計する事にしました。

プログラマが考える劇的に効率が上がるExcelシートの作り方

ExcelのワークシートをDOVパターンで構成する【方法とメリット】

データ処理シート

RAWデータの読み込み
WHERE A IS NOT NULLというのは、A列が空白だったら不要ですみたいな意味です。

=QUERY(RAW!A1:J,"WHERE A IS NOT NULL")

日付けの変換

=ARRAYFORMULA(IF($E$2:$E<>"",TEXT($E$2:$E,"yyyy/mm/dd"),""))

基幹システムのリンクを生成。クレームIDが発番されていて、URLの文字列と合体させると、リンクが生成されます。spreadsheetのセルをクリックするだけで基幹システムの関連ページが開きます。

=ARRAYFORMULA(IF($H$2:$H<>"",HYPERLINK("https://＊＊＊＊＊＊＊＊＊＊＊.com/complaints/"&ENCODEURL(H2:H),"基幹システムリンク"),""))

週判定

=ARRAYFORMULA(IF($L$2:$L<>"",ISOWEEKNUM($L$2:$L),""))

集計シート

データ処理で週判定したシートをCOUNTIFSを使って、集計します。

Slack通知

/*Slackへ週次のクレーム件数を通知する*/
function reportWeeklyComplaint() {
 const spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 const sheet        = spreadsheet.getSheetByName('集計');
 const values       = sheet.getRange('C2:D7').getValues();

 //日付の処理
 const date         = new Date(); //現在日時のDateオブジェクトを作る
 const today        = Utilities.formatDate(date, 'JST', 'yyyy/MM/dd');
 const sixDaysAgo   = returnDate();//6日前
 
 const url          = spreadsheet.getUrl();
 const sheetId      = sheet.getSheetId();
 const fileName     = spreadsheet.getName();
 const marge        = url + "#gid=" + sheetId;

 let results        = '<!channel>' + '\n' + 
                      '関数での自動計算の都合上、1/1　を起算日として、（日曜 - 土曜）の1週間単位で集計しています。' + '\n' +
                      '今回の集計期間は、（　' + sixDaysAgo + ' - ' + today + '　）です。' + '\n' +
                      'Google Apps Scriptによって自動的に通知されています。' + '\n\n';
                      
 let count          = 0;
 
 console.log(values);
 
 /*ゼロ件を省く処理*/
 for(let i = 0; i < values.length; i++){
   if(values[i][1] === 0){continue};
   count         += values[i][1];
   results       += values[i][0] + '　　' + '`' + values[i][1] + '`' + '　per week' + '\n';
 }//for
 
 results +=  '\n' + 'Total　　' + '`' + count + '`' +  '　per week' + '\n\n';
 results += fileName + '\n' + marge;
 
 console.log(results);
 
 
 const payload = { "text" : results }
 const options = {
   "method" : "POST",
   "contentType" : 'application/json; charset=utf-8',
   "payload" : JSON.stringify(payload),
 }
 
 const webhook = '＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊';
 
 UrlFetchApp.fetch(webhook, options);
}

6日前の日付けを算出

function　returnDate() {
 
 const date       = new Date();
 const sixDaysAgo = createStringDate(date, -6);
 
 console.log(sixDaysAgo);
 return sixDaysAgo;
}


function createStringDate(date, number) {
 
 date.setDate(date.getDate() + (number || 0));
 return Utilities.formatDate(date, 'JST', 'YYYY/MM/dd');
 
}

etauさんに書いて頂いたコードを一部、修正して6日前を算出しています。