GoogleフォームとGoogle Apps Scriptで課題提出期限を過ぎた人に煽りメールが来るスクリプト

開発の背景

G's ACADEMYでは色々な講座を開講していますが、いずれの講座も「課題の期限を越えた時点の未提出者へのリマインダ」は欠かせません。

今までは締め切りのたびに提出ファイルの一覧から欠番を探して、該当する番号の人へfbメッセンジャーなりメールなりでリマインダを送付という流れをとっていました。

これでは工数がかかるので、これらの作業をすべて自動化したい！と考えて、今回の開発に至ります。

(あと、bccのメールをお送りする時に、GASを利用することで、「このメールはbccでお送りしています」という文言を無くして宛先の名前を文面をごとに差し込めるのではないか？ と考えたことも開発のきっかけの一つです。)

(制作時間:半日程度)

前提

課題提出者と確認者がいる
提出者の人数は30人〜50人程度。
すべての提出者が合わせて10回程度の課題を提出する
確認者はそのすべてに対して、課題が出されるまで後追い＆出されたらチェックをつける

求められる仕様

・(1) 提出者はgoogleフォームで課題名と受講番号を指定して課題を送付。１枚のシートにすべての提出者のすべての課題の提出物がまとまるようにする
・(2) 同じブックの別のシートに課題の回数分シートが用意され、それぞれ1番〜最後の人までの名簿を記載
・(3) (1)のフォームの追加がされるたびに、該当者の該当する課題の(2)のシートの「提出済み」「未提出」が自動で更新される
・(4) 更新されたシートから、「未提出」の行のレコードにあるメールアドレスに、google docsで編集した文面のメールが送付される

シート構成のイメージはこんな感じ

開発の流れ

・課題提出フォームを用意
・回答結果の記入されるブックに新規のシートを課題の個数分用意
・スプレッドシート上で、ステータスを自動で更新する関数を作る
・メール送信用の文面となるgoogle docsを準備
・メールを送るスクリプトを書く
・テスト送信

早速スクリプトエディタに記載していきます。
スクリプトエディタは、スプレッドシートのタブのツール→「スクリプトエディタ」で開けます。

google docsに準備したのは以下のサンプル文面。URLのd/**************/editに当たる部分が取得するidです。

自動的に未提出/提出を更新させる関数は、それぞれのシートでダミーの列を1行追加して、
「課題名」と「受講番号」の文字列を結合して、同じものが存在するか確認する方法を採用しています。

以下が関数↓↓↓
=if(iferror(vlookup($E5,'フォームの回答 1'!$I$2:$I$1004,1,false), "未提出") = "未提出", "未提出", "提出済み")

(アイデアをくださったのはLAB3期和田さん。ありがとうございました！)

エディタに書いたのは以下のコード。

function doGet() {
  //シート取得
  var mySheet = SpreadsheetApp.getActiveSheet();
  var rowSheet = mySheet.getDataRange().getLastRow();
  
  //メール本文リストを取得する
  var docTest = DocumentApp.openById("google docsのID");
  //
    Logger.log(docTest);//jsでいうconsole.log
  var strDoc=docTest.getBody().getText();
  
//２行目から、シートのレコードの個数分だけメール送信を繰り返す処理
  for(var i=2;i<=rowSheet;i++){
      
    var strSei = mySheet.getRange(i,1).getValue();  //1列目の値を取得
    var strMei = mySheet.getRange(i,2).getValue();  //2列目の値を取得
    var strEmail = mySheet.getRange(i,3).getValue(); //3列目の値を取得
    var bool = mySheet.getRange(i,6).getValue();//6列目の値を取得
  
    var strBody = strDoc;
    
    var strSubject = "【アラーム】課題の提出をお願い致します。";//メール文面のタイトル
    var strFrom = "hogehoge@fugafuga";//送信元メールアドレス
    var strSender = "児玉浩康";//送信者名
  
    if(bool == "未提出"){ //もし6列目の値が未提出だったら
      GmailApp.sendEmail(//gmailからメール送信
        strEmail,
        strSubject,
        strBody,
        {
          from:strFrom,
          name:strSender
        }
      );
    }else{
      Logger.log(bool);
    }
  }
}

スクリプトを実行すると。。
できました！！！

中身を開いてみると、ちゃんと送られてますね。(実際はもっときちんとした文です笑)

気付き

-シートのメアドの部分にダミーでもメールアドレスが書いてないと送信エラーになる(例外処理が必要)
-無料プランだと1日に100件までしか送付できない
-スクリプトのトリガーを毎週送信に設定しても指定した時間にメールが送信エラーになる→関数名を変えたのが原因。後で解決する

作ってみた結果

Google Apps Scriptはすばらしい
作業がかなり楽になってうれしい(小学生並みの感想)
メールじゃなくてfacebookメッセンジャーで一斉に送信がしたい

教育活動とは直接関係の無い雑務はなるべく減らして、メンバーへの本質的に価値提供につながるよう、今後も様々な雑務を技術で解決していきたいです！

「もっとこうするとスマートに書けるよ！」とか「ここが間違ってるよ！」とかあればご指摘いただけると嬉しいです！！

以上、スタッフ木村がお届けしました〜