GAS+Googleフォームを活用してサークル活動参加者名簿を作成したり通知したり色々する

コロナワクチン5回目で寝込んでおり投稿が遅くなったことをお詫びします。
車輪の再発明と言われて久しい記事ですが、アウトプットのために残しておこうと思います。

はじめに

弊学ではクラブ活動が盛んであり大小様々なサークルが活動しています。
コロナによるサークル活動全面禁止期間が終了し、活動解禁となったものの、問題がありました。
参加者名簿を書きそれを翌日までに提出する必要があることです。
しかし部員が多く団結力があるサークルならともかく、個人活動がメインだと
「クラブ室に名簿の紙を置いておき書いておいてもらい、それをサークル長が回収する」という手間が毎日生じ、これは現実的ではありません。
そこで考えました、自動化しちまおう...

実装する機能

・フォームから入力されたデータをもとに名簿を自動作成
・名簿は自動的にpdfに変換され、部長/副部長のメールに自動送信される、pdfはGoogleドライブにも保管される
・フォームに入力されるごとにそれをサークル長がLINE通知として受け取る(ちゃんと名簿が作成されてるか時たま確認したいから)

後々改善した機能(2022年12月)

また、3~4ヶ月程稼働させてみて
・活動していない日でもメールが届いて鬱陶しい
・万が一過去のシートが更新された際に通知していない
という問題点がありました。
そのため
・活動者がいなかった時はメールを送信しない
・フォーム入力が当日でなかった場合は「過去データが更新された」という通知を行う
変更を加えました。

使うもの

GAS(Google Apps Script)
IFTTT
Line Notify
Google スプレッドシート
Google フォーム
Google ドライブ
Gmail

システム図

だいたいこんな感じです。

下準備

1. まずフォームを作成する
   以下の情報を集めることを予め部員たちに断っておいてください。
   学籍番号,学生氏名,学生電話番号,クラブ室使用日,クラブ室使用開始～終了時間,体調
   これらの情報が集まるフォームを作成してください。
   例の画像を以下に載せます。設問の順番は必ず以下の通りにしてください。
   

2. フォームとスクリプトエディタを連携
   右上の方にある縦3点ボタンを押し、スクリプトエディタを選択します。
   飛んだ先でコードを作成していきます。
   

3. 学校側から配布された名簿ファイルをスプレッドシートに取り込み
   また、シート名をBaseSheetに変えておいてください。
   スプレッドシート自体のIDも取得しておいてください。(黒塗り部分)
   

4. Google Driveにpdfを格納するフォルダを作成しておく
   作成したらURLからドライブのフォルダIDを取得しておいてください。
   

5. コードを書く
   下のコードブロックを参照してください。

6. 関数別にトリガを作成
   3つ作ります。全て下記のように設定してください。
   ①makeNewSheet関数
   0:00~1:00の間にその日の分のシートが作られます。
   
   ②sendSheetPdfByEmail関数
   8:00~9:00に指定した宛先にメールが送られます。
   朝の登校時間辺りに通知しとけばいいかと思いこうなりました。
   
   ③formSubmitted関数
   

7. appsscript.jsonの作成
   下のコードブロックを参照してください。

8. LINEでのグループ作成
   LINE Notifyを友だち追加して、通知を送信したいグループに招待して下さい。

9. IFTTTの連携設定
   https://qiita.com/yosuke_sasajima/items/0e19f85ec161412e2622
   を参考にしてIFTTTにアクションを作成してください。
   ifはwebhook - receive a web requestを選び、
   Then ThatはLINE - Send messageを選んでください。
   LINEの文言は以下の通り入れてもらえればうまくいくはずです。
   アクションは3つ作ります。
   ①登録の通知
   

②昨日の参加者がいたか、メール送信されたかの通知

③当日以外のシートへの書き込みがあった場合の通知

また、参考資料に載っている通りWebhooksのDocumentationからKeyを取得しておいてください。
コード内のiftttEventKeyに使います。

コード

code.gs

//最終更新 2022.12.17
//既知の不具合 氏名にスペースが入っているとURL間にスペースが入ってしまいIFTTTが叩かれず、LINEにも通知が来ない(学籍番号などでもそのミスは再現すると思われる)
//→解決済　文字列として取ってこられそうな部分にreplace(/\s+/g,'');を付け足す

//フォームに書き込まれた時の処理
function formSubmitted(e) {
 //後で一つにまとめるための配列を定義
 var itemResponses = e.response.getItemResponses();
 var studentDataArray=[];
 //書き込む変数を用意する
 let studentNumber =itemResponses[3].getResponse().replace(/\s+/g,''); //学籍番号
 let studentName = itemResponses[2].getResponse().replace(/\s+/g,'');  //学生氏名
 let studentPhoneNumber = itemResponses[4].getResponse().replace(/\s+/g,''); //学生電話番号
 let studentUsesRoomDate = itemResponses[1].getResponse().replace(/-/g,"/"); //学生部屋使用日時 後々のために-を/にしておく
 let startingTime = itemResponses[5].getResponse(); //活動開始時刻
 let endingTime = itemResponses[6].getResponse(); //活動終了時刻
 let studentCondition = ""; //一旦このように定義しておいて...
 if(itemResponses[0].getResponse()=="良かった"){  //体調が良かったら
   studentCondition = "✔";
 }else if(itemResponses[0].getResponse()=="悪かった"){ //体調が悪かったら
   studentCondition = "✗";
 }
 
 //配列にまとめる
 studentDataArray.push(studentNumber);
 studentDataArray.push(studentName);
 studentDataArray.push(studentPhoneNumber);
 studentDataArray.push(studentCondition);
 studentDataArray.push(startingTime+"~"+endingTime);


 //書き込み対象のスプレッドシートを読み込む
 var targetSpreadSheetId = "3で取得したスプレッドシートのID";
 var targetSpreadSheet = SpreadsheetApp.openById(targetSpreadSheetId);
 var targetSheet = targetSpreadSheet.getSheetByName(studentUsesRoomDate);
 //入力された日付名のシートがなければmakeNewSheetを実行して作成し、再度targetSheetに読み込む
 if(targetSheet==null){
   Logger.log(studentUsesRoomDate+"というシートが見つかりませんでした。作成します");
   makeNewSheet(e,studentUsesRoomDate);
   targetSheet = targetSpreadSheet.getSheetByName(studentUsesRoomDate); 
 }
 //作成もしくは既存のシート名をもう一度別変数に取得
 var targetSheetName = targetSheet.getSheetName();
 //targetRowを初期化
 var targetRow="";
 //まず空白の行を探す 9行目から26行目までをforで回して空白になったらループ抜ける
 for(i=9;i<27;i++){
   if(targetSheet.getRange(`B${i}:F${i}`).isBlank()==true){
     targetRow = `B${i}:F${i}`;
     break;
   }else{
     ;  //空白でなければ何もせず次の行に進む
   }
 }
 //シートの欄を埋める
 Logger.log("シート"+targetSheetName+"に書き込みします");
 Logger.log("書き込まれる配列は以下の通りです:"+studentDataArray);
 Logger.log(targetRow+"に書き込みします");
 //与える配列が一次元で書き込み先が一行なら、setValuesには配列を[]で囲ったものを与えてやる必要がある
 targetSheet.getRange(targetRow).setValues([studentDataArray]);

 //IFTTTを通してLINEへ通知を送る
 //今日の活動か、それとも過去の活動かで通知するパラメタを変える(2022.12.17改善)
 var todayDate = Utilities.formatDate(new Date(),'JST','yyyy/MM/dd');//日付を取得しStringにして
 if(todayDate==studentUsesRoomDate){  //今日の活動なら
   var iftttEventName = "gas_posted"; //IFTTTのイベント名
   var sendValue1 = studentNumber+" "+studentName;  //学籍番号と学生氏名
   var sendValue2 = studentUsesRoomDate+" "+startingTime+"~"+endingTime;  //使用時間
   var sendValue3 = studentCondition; //学生の体調
 }else{ //今日の活動でないなら
   var iftttEventName = "gas_updated";
   var sendValue1 =  studentUsesRoomDate  //使用日。この場合更新されたシートがいつのものなのかを知らせるため最初に指定
   var sendValue2 =  studentNumber+" "+studentName;  //学籍番号と学生氏名
   var sendValue3 =  startingTime+"~"+endingTime+" "+studentCondition;  //活動時間と体調
 }

 var iftttEventKey = "IFTTTのイベントキー";  //IFTTTのイベントキー
 var iftttUrl = "https://maker.ifttt.com/trigger/"+iftttEventName+"/with/key/"+iftttEventKey+"/?value1="+sendValue1+"&value2="+sendValue2+"&value3="+sendValue3;

 UrlFetchApp.fetch(iftttUrl);
 Logger.log("IFTTT経由でLINEに通知を送信しました");

}

//毎日12時~1時にその日の分の新しいシートを作成する関数、トリガで設定すること
//手動で実行してもその日の分のシートが作成される。必要に応じて適宜使うこと
function makeNewSheet(e,date){
  //書き込み対象のスプレッドシートを読み込む
  var targetSpreadSheetId = "3で取得したスプレッドシートのID";
  let targetSpreadSheet = SpreadsheetApp.openById(targetSpreadSheetId);
  //ベースとなる何も書かれていないBaseSheetシートをコピー対象のシートとして読み込み
  let baseSheet = targetSpreadSheet.getSheetByName('BaseSheet');
  //スプレッドシートにコピー
  let copiedSheet = baseSheet.copyTo(targetSpreadSheet);
  //もしmakeNewSheetの引数dateがなかった場合は今日の日付で作成する、あったらその日付で作成する
  if(!date){
    Logger.log("今日の分のシートを作成します")
    todayDate = Utilities.formatDate(new Date(),'JST','yyyy/MM/dd');//日付を取得しStringにして
  }else{
    todayDate = date; //与えられた引数をtodayDateとして
    Logger.log(todayDate+"でシートを作成します");
  }
  //シートの名前をその日付に変更
  copiedSheet.setName(todayDate);
  //シートの欄を埋める
  copiedSheet.getRange("C3:E3").setValue("サークル名");
  Logger.log("サークル名を書き込みました");
  copiedSheet.getRange("C4:E4").setValue(todayDate+" (時間は下部に記載)");
  Logger.log("日時を書き込みました")
  copiedSheet.getRange("C5:E5").setValue("活動場所");
  Logger.log("活動場所を書き込みました")
}

//昨日分のスプレッドシートをpdf化してメールで送信する関数
function sendSheetPdfByMail(e){
  //ターゲットとするスプレッドシートのID
  var spreadSheetId = "3で取得したスプレッドシートのID"
  //バックアップを保存するためのGoogle DriveのフォルダID
  var driveFolderId = "4で取得したGoogle DriveのフォルダID"
  //読み込み対象のスプレッドシートを読み込む
  let targetSpreadSheet = SpreadsheetApp.openById(spreadSheetId);
  //昨日の日付を変数yesterdayに持たせる
  var date = new Date();
  date.setDate(date.getDate() - 1);
  var yesterday = Utilities.formatDate(date,"JST", "yyyy/MM/dd");
  //現在の日付から一日前の日付（昨日）をシート名として読み込み
  var targetSheet = targetSpreadSheet.getSheetByName(yesterday);
  //もし万が一昨日のシートが見つからなかったら(ないはずのケース)
  if(targetSheet==null){
   Logger.log(yesterday+"というシートが見つかりませんでした。作成します");
   makeNewSheet(e,yesterday);
   var targetSheet = targetSpreadSheet.getSheetByName(yesterday); 
 }
  Logger.log("ターゲットとするシートは"+targetSheet);

  //2022.12.17改善①活動した人が居ない日はメールを送らない&IFTTTで活動者がいたかの通知とメール送信の通知を行う
  //9行目が空白かそうでないかで活動した人がいたかどうかを判定する
  if(targetSheet.getRange(`B9:F9`).isBlank()==false){
    //読み込んだシートのIDを取得
    var sheetId = targetSheet.getSheetId();
    Logger.log("シートのIDは"+sheetId);

    //トークンを獲得
    var bToken = ScriptApp.getOAuthToken();
    //pdf作成用URLを用意する
    var url = "https://docs.google.com/spreadsheets/d/" + spreadSheetId + "/export?gid=" + sheetId + "&format=pdf&portrait=true&size=A4&gridlines=false&fitw=true";
    //作成するファイル名を先に用意しておく /記号はファイル名に使えないのでreplaceで置き換えるがそもそもデリミタにも/を使うので\/って感じでエスケープが必要
    var pdfName = yesterday.replace(/\//g,"_");
    //pdfをフェッチする デバッグしてURLにアクセスしたりフェッチしたものをGoogle Driveに保存したりするとちゃんと書き込まれたpdfが出てきたのでバグ？
    var pdf = UrlFetchApp.fetch(url, {headers: {'Authorization': 'Bearer ' +  bToken}}).getBlob().setName(pdfName+".pdf");
    //googleドライブに保存
    let folder = DriveApp.getFolderById(driveFolderId);
    folder.createFile(pdf);

    //メールの送信先、部長と副部長
    var to = "[e-mail],[e-mail]";
    //メールのタイトル
    var subject = yesterday+" 〇〇サークル活動参加者名簿";
    //メールの本文
    var body = "昨日"+yesterday+"の〇〇サークルの活動に参加した人の名簿ファイルです。本日中に印刷して学生センター前のボックスに入れて下さい。"; 
    //メールを送信する
    GmailApp.sendEmail(to,subject,body,{attachments: pdf});
    Logger.log("メールにpdfファイルを送信しました。");
    Logger.log("本日のメール送信クオータは"+MailApp.getRemainingDailyQuota()+"です");
    var sendValue1 = "昨日は参加者がいました。部長・副部長のメールアドレス宛にpdfファイルを送信しました。本日中に印刷して学生センター前ボックスに入れて下さい。";

  }else{
    //メールを送らない(昨日の参加者がいなかったとき)
    var sendValue1 = "昨日は参加者がいませんでした。メールは送信していませんので、印刷の必要はありません。"
  }
  //IFTTTで昨日の活動者の有無とpdf送信の有無を通知
  var iftttEventKey = "IFTTTのイベントキー";  //IFTTTのイベントキー
  var iftttEventName = "gas_yesterdaydata_receieved"; //IFTTTのイベント名
  var iftttUrl = "https://maker.ifttt.com/trigger/"+iftttEventName+"/with/key/"+iftttEventKey+"/?value1="+sendValue1;

  UrlFetchApp.fetch(iftttUrl);
  Logger.log("IFTTT経由でLINEに昨日の活動とpdfメールの通知を送信しました");

}

appsscript.json

appsscript.json

{
  "timeZone": "Asia/Tokyo",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes":["https://www.googleapis.com/auth/forms","https://www.googleapis.com/auth/spreadsheets","https://www.googleapis.com/auth/script.send_mail","https://www.googleapis.com/auth/gmail.send"," https://www.googleapis.com/auth/drive","https://www.googleapis.com/auth/script.external_request"],
  "runtimeVersion": "V8"
}

今後の改良ポイント

・jsの書き方を勉強してガバガバコードをリファクタリングする
・IFTTTのクオータのうち3つを使ってしまっているため、減らせないか検討する
・後継者への引き継ぎ

作成した感想

これは結構な比率をGoogleのサービスに依存しているので鯖落ちやサービス停止なんかがあったら目も当てられないことになります。
今回は手っ取り早くするため&Googleのサービスとの連携を考慮してGASで作りましたが、
沢山の人にもっと使ってもらうんであればGCPなどへのデプロイとかも考えられそうです。

最後に

自動化バンザイ！！！