こんにちは、米本です。

さて、2023年10月から開始したワーキンググループに関するストーリーですが、

今回で5回目、一旦の最終回となります。

過去の投稿はこちらからご覧ください。

第1回：はじめに編はこちら

第2回：webスクレイピング編はこちら

第3回：chatwork連携編はこちら

第4回：書籍管理編はこちら

最終回ですので、これまでの集大成的なものにしたく、

ステータスに応じてChatworkへ通知を飛ばす、スプレッドシートテンプレートを作成してみます。

◯準備

まずは要件出しから。

今回は健康診断の受診状況一覧と仮定して、以下の要件としました。

・予定日が入力済かつsysdateより前の場合、STATUSを「予約済」として、1週間前に通知を飛ばす
・予定日が入力済かつsysdateより後の場合、受診確認の通知を飛ばし、STATUSを「完了」としてグレーアウトする

以下の一覧をベースとします。

通知先は以前利用した通知用のボードになります。悲しげなタスクが残存していますが、なんのこっちゃわかりません。気にせず進めましょう。

◯コーディング

早速コーディング。

function test_template() {

  const token = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';          //Chatwork API Tokenを定義

  const room_id ='XXXXXXXXX';                                //メッセージ送信対象のルームIDを定義

  const client = ChatWorkClient.factory({token: token});     //ChatworkAPIクライアント作成

  const event_name = '健康診断';                              //イベント名を取得

  const event_url = 'https://docs.google.com/spreadsheets/X';//イベントURLを取得

  var sheet = SpreadsheetApp.getActiveSheet();               //シートを取得

  var myRange = sheet.getDataRange().getValues();            //使用しているセルの範囲を取得

  var date = new Date();                                     //現在日時を取得

  Logger.log('//イベント名を取得');

  Logger.log(event_name);

  Logger.log('//イベントURLを取得');

  Logger.log(event_url);

  Logger.log('//使用しているセルの範囲を取得');

  Logger.log(myRange);

  Logger.log('//現在日時を取得');

  Logger.log(date.getTime());



  //ループを3行目から開始するためにi=2

  for(var i = 2; i < myRange.length; i++) {

    var name = myRange[i][1];                                 //個人名取得

    var charworkID = parseInt(myRange[i][2]);                 //個人ID取得

    var schedule_date = myRange[i][3];                        //予定日取得

    var schedule_type = typeof(schedule_date);                //データ型取得

    var status = myRange[i][4];                               //ステータス取得

    var range = sheet.getRange(i+1, 1, 1, myRange[0].length); //1行を取得する

    Logger.log('//個人名取得');

    Logger.log(name);

    Logger.log('//個人ID取得');

    Logger.log(charworkID);

    Logger.log('//予定日取得');

    Logger.log(schedule_date);

    Logger.log('//データ型取得');

    Logger.log(schedule_type);

    Logger.log('//ステータス取得');

    Logger.log(status);

    Logger.log('//1行を取得する');

    Logger.log(range);

    

    //「ステータスが未済かつ予定日以降」であれば、「背景グレーアウトを解除(透明に戻す)」

    //「ステータスが未済かつ予定日の7日前以内」であれば、「ステータスを予約済に変更して通知」

    //「ステータスが予約済かつ予定日以降」であれば、「ステータスを済に変更して通知後、背景をグレーアウト」

    if(schedule_date != '' && schedule_type == 'object'){

      var notify_date = new Date(schedule_date);                                //参照渡しで「schedule_date」の変更を抑止するため新しいDateオブジェクトを作成する

      notify_date.setDate(notify_date.getDate() - 7);                           //予定日の1週間前を取得

      var row = i + 1;                                                          //ステータス変更対象の行を取得

      Logger.log('//予定日を再取得');

      Logger.log(schedule_date);

      Logger.log('//予定日の1週間前を取得');

      Logger.log(notify_date);

      Logger.log('//ステータス変更対象の行を取得');

      Logger.log(row);

      if(status == '未済' && schedule_date <= date){

        range.setBackground(null);  

      }else if(status == '未済' && notify_date <= date && schedule_date > date){

        var new_status = '予約済';                                               //変更後のステータスを設定

        var body = '[To:' + charworkID + ']' + name + 'さん\r\n';                //通知文を設定

        body += '～～～【通知】「' + event_name + '」予定日の7日前になりました～～～\r\n';

        body += '予定日を変更する場合は、こちらのスプレッドシートにて、\r\n';

        body += '・予定日の変更\r\n';

        body += '・ステータスを「未済」に変更\r\n';

        body += 'お願いします。\r\n';

        body += event_url;

        Logger.log('//変更後のステータスを設定');

        Logger.log(new_status);

        Logger.log('//通知文を設定');

        Logger.log(body);

        sheet.getRange('E' + row).setValue(new_status);

        client.sendMessage({

            room_id: room_id,

            body: body

        });

      }else if(status == '予約済' && schedule_date <= date){

        var new_status2 = '済';                                                   //変更後のステータスを設定

        var body = '[To:' + charworkID + ']' + name + 'さん\r\n';                 //通知文を設定

        body += '～～～【通知】「' + event_name + '」予定日が経過しました～～～\r\n';

        body += '完了していない場合は、こちらのスプレッドシートにて、\r\n';

        body += '・予定日の変更\r\n';

        body += '・ステータスを「未済」に変更\r\n';

        body += 'お願いします。\r\n';

        body += event_url;

        Logger.log('//変更後のステータスを設定');

        Logger.log(new_status2);

        Logger.log('//通知文を設定');

        Logger.log(body);

        sheet.getRange('E' + row).setValue(new_status2);

        client.sendMessage({

            room_id: room_id,

            body: body

        });

        range.setBackground("#C0C0C0");                                          //背景をグレーアウトする

      }

    }

  }

}

これまで同様、APItokenやルームIDはマスクしています。

◯実行

上記を実行すると・・・

このように通知が飛び、スプレッドシート側が
・予定日が一週間以内だった人（一田一郎）のSTATUSを予約済に更新
・予定日を過ぎた人（三田三郎）のSTATUSを済に更新
されます。

今回は健康診断の受診予定、と仮定してみましたが、
条件を変更することで、イベントの出欠確認や、様々なことに転用できるのではないかと思います！

如何でしたでしょうか。これにてGoogleAppScript検証のストーリーは一旦終了となります。

自分は普段インフラ関連の業務に携わっていますが、
今回の検証でアプリ開発側目線での考え方に触れることができ、
何よりGoogleAppScriptの知見が得られたのが大きな収穫でした。

基本的な部分であれば無料で試すことができますので、興味を持たれた方は是非お試しください。

これまでご覧いただきありがとうございました！