【GAS】Google Apps Script 活用事例　Google Forms送信と同時にGoogle Calendarのスケジュール登録、Spreadsheetへの転記、Slackへの投稿の3つをこなす欲張りなスクリプト

以前、このブログでご紹介したGoogle Formsの送信時に労務課へ、労災発生を報告するGoogle Formsですが、カスタマイズして、さらに痒いところに手が届くようになりました。

1.　選択内容によって、Googleカレンダーへの登録
2.　Slackへの投稿
3.　Spreadsheetへの行と列を入れ替えた状態で転記
4.　選択内容によって、労務課への報告メールの自動化

改めて書き出してみると、業務効率化度合いが凄くないか？コレ.....。こんなのが自分で作れるようになったと半年前の俺に話したら、びっくりするだろうな。

長過ぎやろ.....こんなの誰も読まない。

実務では、日本語が上手く話せない、緊急時に医療費の備えがない外国人スタッフのために同行して立替をする業務があります。病院対応に行くと、そのあと交通費申請、次回の診察のスクジュール登録、立替経費申請、スタッフの様子、稼働の可否をSpreadsheetに書き込むetc......作業があります。
その業務を効率化しようというのが、今回の目的です。

そもそも、もう少し日本語が話せる人を連れてきてよ......連れてくるにしても受入可能な人数を考えろ！！経営層！！ってな感じなんですが、.....仕方ない。嫌なら転職する他ない。

まず、一番、最初に試みたのが、Google Formsの質問と回答をそのままSlackへ投稿するという方法です。必要な質問項目を網羅したところ、質問項目が合計で30以上になってしまいました。（必須回答が30以上という訳ではありません。）.....流石に長過ぎと思って、考え直しました。

Google Formsの質問・回答をそのままSlackに投稿するスクリプト

function submitForm(e){
 var itemResponses = e.response.getItemResponses();
 var message = '';
 for (var i = 0; i < itemResponses.length; i++) {
   var itemResponse = itemResponses[i];
   var question = itemResponse.getItem().getTitle();
   var answer = itemResponse.getResponse();
   
   message += '質問：' + question + '\n' + '回答：' + answer + '\n\n';
   
 }
 
 //SpreadsheetのURL
 var url     = 'https://docs.google.com/spreadsheets/d/***********/edit#gid=*********';
 var content = 'このポストは、Google Apps Scriptによって自動で通知されています。' + '\n' +
               '詳細は下記の、Spreadsheetにて確認してください。' + '\n\n' + message + url;
       
 var webhook = '***************************';
 
 Logger.log(message);
 
 var text = message;
 var payload = { "text" : text }
 var options = {
   "method" : "POST",
   "contentType" : 'application/json; charset=utf-8',
   "payload" : JSON.stringify(payload)
 }
 
 UrlFetchApp.fetch(webhook, options);
}

今回、上記スクリプトについては、解説しません。
var urlの部分をお手持ちのシートのURLに切替、webhookをSlack APIに記載されているIncoming webhookに書き換えるだけで、コピペで使いまわせます。過去記事で書いたこちらの投稿をベースにしています。

Slackへ投稿　本採用ver

どうしても知る必要があるのが、IDやスタッフ名、スタッフは現在サービスが提供出来る状態なのか、もし無理なら復帰はいつ頃を予定しているのか？といった情報です。それを実現したのが、次のスクリプトです！！

function postSlack() {
 var spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 var formsRawData = spreadsheet.getSheetByName('Forms');
 var lastRow      = formsRawData.getLastRow();
 var lastColumn   = formsRawData.getLastColumn();
 var values       = formsRawData.getDataRange().getValues();
 
 /*最終行の内容を取得*/
 var rowContents  = formsRawData.getRange(lastRow, 1, 1, lastColumn).getDisplayValues();
 
 /*SpreadsheetURLの生成*/
 var fileName     = spreadsheet.getName();
 var url          = spreadsheet.getUrl();
 var sheet        = spreadsheet.getSheetByName('傷病対応一覧');
 var sheetId      = sheet.getSheetId();
 var marge        = url + "#gid=" + sheetId;
 
 
 /*スタッフ名を新しい配列に追加する*/
 var staffName    = vlookup();
 rowContents[0].splice(3, 0, staffName);
 
//  Logger.log('スタッフID：　%s', rowContents[0][2]);//スタッフID
//  Logger.log('スタッフ名：　%s', rowContents[0][3]);//スタッフ名
//  Logger.log('病気の詳細・本人の様子：　%s', rowContents[0][9]);
//  Logger.log('次回サービス時の鍵の有無：　%s', rowContents[0][29]);
//  Logger.log('代理手配の必要性：　%s', rowContents[0][12]);
//  Logger.log('復帰見込み：　%s', rowContents[0][11]);
 
 var message      = '<!channel>' + '病院アテンド報告のGoogle Formsが送信されました。' + '概要は以下の通りです。' + '\n\n';
 
 message += '同行者：　' + rowContents[0][1] +  '\n' + 'スタッフID：　' + rowContents[0][2] + '\n' + 'スタッフ名：　' + rowContents[0][3] + '\n' + '病気の詳細や本人の様子：　' + rowContents[0][9] + '\n' + 
            '次回サービス時の鍵の有無：　' + '`' + rowContents[0][29] + '`' + '\n' + '代理手配の必要性：　' + '`' + rowContents[0][12] + '`' + '\n' + '復帰見込み：　' + rowContents[0][11] + '\n\n' +
            fileName + '\n' + marge + '\n\n' + 'Google Forms' + '\n' + 'https://docs.google.com/forms/d/e/**************' + '\n\n' + '基幹システムのスタッフページ' + '\n' + 'https://***************.com/staffs/' + rowContents[0][2];
                    
 var webhook = '****************';
 
 
 var payload = {
   "text" : message
 }
 
 var json = JSON.stringify(payload);//エンコード
 
 var options = {
   "method" : "POST",
   "contentType" : 'application/json; charset=utf-8',
   "payload" : json
 }
 
 UrlFetchApp.fetch(webhook, options);                  
}

var rowContents　というのが、Spreasheet連携時、Google Forms送信によって転記された情報です。最新の情報、つまり最終行しか読まないので、わずか1行のみの2次元配列となります。なので、rowContents[0][2]みたいな感じで指定しています。

トリガーは、フォーム送信時を選択する事で、フォームが送信した時にSlackがリアルタイムで送られます。めちゃ便利！！SlackのTipsなのですが、強調したい単語を　``で囲むと色が変って読みやすくなります。**で囲むと太字とか >で引用とかあります。

Google カレンダーへの登録を実現するスクリプト

上記の画像は、実は、Googleカレンダーへの登録スクリプトを書いた際のエラー内容をノンプロ研のコミュニティに相談した時のスクショです。エラーはあったのですが、今は無事解決し、即戦力なスクリプトとして働いてくれています。

/*次回の診察予定をカレンダーに登録する*/
function registarCal() {
 var calendar     = CalendarApp.getDefaultCalendar();
 Logger.log(calendar.getName());
 
 var spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 var formsRawData = spreadsheet.getSheetByName('Forms');
 var lastRow      = formsRawData.getLastRow();
 var lastColumn   = formsRawData.getLastColumn();
 
 
 /*Formsシートの最終行を取得*/
 var rowContents  = formsRawData.getRange(lastRow, 1, 1, lastColumn).getDisplayValues();
 
 /*スタッフ名を新しい配列に追加する*/
 var staffName    = vlookup();
 rowContents[0].splice(3, 0, staffName);
 
 
 var calTitle  = rowContents[0][2] +'　'+ rowContents[0][3] + '　病院アテンド';
 
 
 var startTime = new Date(rowContents[0][21] + ' ' + rowContents[0][22]);
 var endTime   = new Date(rowContents[0][21] + ' ' + rowContents[0][35]);
 
 Logger.log(typeof startTime);
 Logger.log(typeof endTime);
 
 var options    = {
   description: rowContents[0][8],
   guests:      rowContents[0][30],
   sendInvites: true
 }
 
 Logger.log(calTitle);
 Logger.log(startTime);
 Logger.log(endTime);
 Logger.log('次回の診察予約の有無　%s', rowContents[0][23]);
 Logger.log('Google カレンダーのアドレス %s', rowContents[0][30]);
 
 if(rowContents[0][23] == 'あり'){
   calendar.createEvent(calTitle, startTime, endTime, options);
 }
 
 
}//end

エラーでハマったポイント

ここでも、オブジェクトの型がとっても大切で、dateオブジェクトではないがためにエラーが出ていました。過去記事では出来ていたのに、なぜか今回は上手くいかず焦る焦る......。「あれ！？出来ない。なんで、出来ないの！？」ちょっと軽いパニックでした。

values[i][1] = Utilities.formatDate(new Date(values[i][1]),'Asia/Tokyo','yyyy/MM/dd')

今までメールへの記載、Slackへの投稿では、日付を文字列化していたようです。Dateオブジェクトへの変換は下記のように書けばOK

var startTime = new Date(rowContents[0][21] + ' ' + rowContents[0][22]);
var endTime   = new Date(rowContents[0][21] + ' ' + rowContents[0][35]);

日付 + 時間　('2020/02/14 09:00')　こんな感じの情報をDateオブジェクト化しています。Google Formsの仕様として、質問項目を作った順に配列に追加されていくようです。それにしても、[35]って流石に作り過ぎやろって思います。

Google のレファレンスでは、スケジュール登録のタイトルは文字列でOKだですが、開始時刻、終了時刻は、Dateオブジェクトじゃないとダメという事が分かりました。高橋さんのご指摘通りでした。

Google Formsの質問項目をこんな感じで作ると便利

プルダウンで選ぶだけにしてあげると便利です。9:30ではなく、09:30みたいにしておくと、後々楽かもしれないなと思ったので桁数を統一しています。

カレンダー登録を自動化するために必要な質問項目

var options    = {
   description: rowContents[0][8],
   guests:      rowContents[0][30],
   sendInvites: true
 }

guests:　の項目を設定しておく事で、フォーム送信と共にスケジュール登録を完了させる事が出来ます。sendInvites　をTRUEにしておくと、イベントが登録された際に招待メールが届きます。

if(rowContents[0][23] == 'あり'){
   calendar.createEvent(calTitle, startTime, endTime, options);
 }

何でもかんでも予定に登録されてしまっては困るので、次回の診察予定日がありの時だけ、スケジュールに登録するようにします。

Spreadsheetへの行と列を入れ替えて転記

function setValues() {
 var spreadsheet  = SpreadsheetApp.getActiveSpreadsheet();
 var formsRawData = spreadsheet.getSheetByName('Forms');
 var lastRow      = formsRawData.getLastRow();
 var lastColumn   = formsRawData.getLastColumn();
 
 var rowContents  = formsRawData.getRange(lastRow, 1, 1, lastColumn).getDisplayValues();
 Logger.log(rowContents[0][32]);
 var arrayLength  = rowContents[0].length;
 
 /*URLの生成 病院アテンドが1回目の時のみ、病気対応一覧のシートに転機する。*/
 if(rowContents[0][32] == 1){
   var fileName     = spreadsheet.getName();
   var url          = spreadsheet.getUrl();
   var sheetId      = formsRawData.getSheetId();
   var marge        = url + "#gid=" + sheetId;
   
   
   /*スタッフ名を新しい配列に追加する*/
   var staffName    = vlookup();
   rowContents[0].splice(3, 0, staffName);
   rowContents[0].splice(arrayLength, 0, '対応中');
   
   /*一覧シートに移す*/
   var targetSheet  = spreadsheet.getSheetByName('傷病対応一覧');
   var targetColumn = targetSheet.getLastColumn() + 1;//最終列 + 1列で空白行の算出
   
   Logger.log('一覧シートの最終列は、 %s　列です。',targetColumn);
   Logger.log(targetSheet.getRange(2, targetColumn, rowContents[0].length, 1).getA1Notation());
   
   var _ = Underscore.load();
   var arrayTranspose = _.zip.apply(_, rowContents);
   
   targetSheet.getRange(2, targetColumn, rowContents[0].length, 1).setValues(arrayTranspose);
   targetSheet.autoResizeRows(1, rowContents[0].length);
   targetSheet.autoResizeColumns(1, targetColumn);
 }
}

TRANSPOSE関数と同じ動きで再現するために、Underscoreというライブラリーを使います。

var _ = Underscore.load();
var arrayTranspose = _.zip.apply(_, rowContents);

ちょっと書くのが長くなって疲れてきました......。

Google Apps Scriptで二次元配列の行と列を入れ替える方法とその革命的な効果

Google Apps Scriptで100以上の便利関数が使える定番ライブラリUnderscore for GAS