💭

【GAS】スプシで時間割を組むとカレンダー登録が自動的に実行されるコードを書く

2023/02/07に公開

GAS

業務効率化

tech

こんにちは、コインチェックCTO室の大村です。

コインチェックでは、開発者体験向上のために、Ruby on Railsのペアプロの取り組みを始めました。
その内容と成果については別稿に譲るとして、今回は主にペアプロのロジスティクスを担当している私が表題のスクリプト（スプシで時間割を組むとカレンダー登録が自動的に実行される）を組んだので、その内容をシェアしたいと思います。

シナリオ

ペアプロは1人の専任チューターの稼働時間に対して受講者がリクエストを送る形式で実施する
リクエストを送る方法は、受講者がペアプロのコマを、自主的にスプシの空き枠に登録する
- 画像のドロップダウンが表示されているセルに名前を登録する
- なお、受講者の名前は予めスプシの入力規則で登録しておき、受講者以外の名前は入力できない
受講者の名前が入力された場合は、コマに対応する時間がチューターと受講者のGoogle Calendar上でブロックされる
受講者がスプシ上から名前を削除した場合は、既に登録されていた予定がチューターと受講者のGoogle Calendar上で削除される

背景

コインチェックCTO室の取り組みの一環で、若手エンジニア向けのペアプロの取り組みを始めることになったが、ロジをいい塩梅で自動化したかった。
受講者各自が都合の良い時間をスプシ上でブロックしてもらえば、あとはカレンダーのインビテーションから参加してもらえる仕組みにできれば、みんなだいぶ楽かなと思った。
カレンダーを上手に操るすべを身に付けたかった。

実装

3つのgsファイルに分けて作成した

set_calendar.gs：新規のカレンダーの予定をセットする
delete_calendar.gs：登録されたカレンダーの予定を削除する
copy_calendar.gs：すクリプト実行時のスナップショットを取得する

またGASと紐づけるスプシには下記のシートを作成

時間割：受講生がコマを登録するためのシート
参加者マスタ：参加者の名前とメールアドレスを格納したDBのシート
GAS用：スクリプトが作動した時点のSnapshotを残すためのシート

参加者マスタはこんな感じ

参加者名	所属1	所属2	アドレス
Sota Omura	CTO室	----	XXXX@XXX.com

set_calendar.gs

一通り全ての関数で使う変数をグローバル変数で定義します
スプシの呼び出しとシートの呼び出しはもう流れ作業で書いちゃいます

const id = "xxxx@xxxxxxxx";
//カレンダーを呼び出すためのgmailアドレス

let calendar = CalendarApp.getCalendarById(id);
//カレンダーを呼び出す

var spreadsheet = SpreadsheetApp.openByUrl('スプシのURL');
//スプシ呼び出し

var students_master = spreadsheet.getSheetByName('参加者マスタ');
//参加者一覧呼び出し

let lastRow = students_master.getLastRow();
//参加者マスタの行数取得

var gas_master = spreadsheet.getSheetByName('GAS用')
//GASマスタ呼び出し

var timetable_master = spreadsheet.getSheetByName('時間割');
//タイムテーブル呼び出し

//ここまでグローバル関数

つづいて実際にカレンダー登録をするsetEvent()を書きます。
ここでのポイントは
① 'GAS用'には前回スクリプト実行時のSnapshotが残っている
② '時間割'で今回スクリプト実行時に更新されたコマのみ、予定登録か削除をしたい

よって、setEvent()の動作としては
'GAS用'のスナップショットと'時間割’の内容を比較して、
① 'GAS用'で登録されていなかったコマが登録されている→予定登録
② 'GAS用'で登録されていたコマが削除されている→予定削除 (delete_calendarの呼び出し)
としたいです。

function setEvent() {
  try {
    var new_timetable = [];
    var old_timetable = [];
    //受講者名, 開始時間, 終了時間　を格納する二次元配列を準備
    //new_timetableは新規に予定を作成する用、old_timetableは予定を削除する用
    
    for (let i = 4; i <= 9; i++) {
      for (let g = 4; g <= 100; g++) {
        var new_student = timetable_master.getRange(i, g).getValue();
        var old_student = gas_master.getRange(i, g).getValue();
	//'時間割'と'gasマスタ'を全探索し（D4:AO100)それぞれnew_student, old_studentに代入
	//

        if (new_student == old_student) {
          ; 
	  //'gasマスタ'と'時間割'の対応するセルの値が同じ場合は受講者のコマに変化がないので何もしない
        } else if (new_student != old_student) {
	　//'gasマスタ'と'時間割'の対応するセルの値が違う場合は処理を実行する
	 
          var classday = new Date();
          classday = timetable_master.getRange(3, g).getValue();
	  //まずは授業実施の日付を３列目の日付の行から取得してclassdayに代入

          var start_time = new Date();
          var start_time = timetable_master.getRange(i, 2).getValue();
	  //次に授業の開始時間をB列から取得
	  
          start_time.setFullYear(classday.getFullYear());
          start_time.setMonth(classday.getMonth(),1);
          start_time.setDate(classday.getDate());
	  //授業の開始時間にclassdayから年月日を上書きする

          var end_time = new Date();
          var end_time = timetable_master.getRange(i, 3).getValue();
	  //次に授業の終了時間取得
	  
          end_time.setFullYear(classday.getFullYear());
          end_time.setMonth(classday.getMonth(),1);
          end_time.setDate(classday.getDate());
	  //授業の終了時間にclassdayから年月日を上書きする  

          if (new_student != "") {
            new_timetable.push([new_student, start_time, end_time]);
          }
	   //'時間割'に新たな登録がある場合、
	   //二次元配列に新たに予定を作る受講者名, 開始時間, 終了時間を格納する
	  
          if (old_student != "") {
            old_timetable.push([old_student, start_time, end_time]);
	    //'時間割'に元々の予定が削除されていた場合、
	    //二次元配列に予定を消す受講者名, 開始時間, 終了時間を格納する
          }
        };
      };
    };

    deleteEvent(old_timetable) 
    //スクリプト更新時に'時間割’上で削除されていた人のカレンダーを削除する

    var title = []
    var set_start_Time = []
    var set_end_Time = []
    var address = []
     //予定のタイトル、開始時刻、終了時刻、参加者アドレスを格納する配列を準備する

    for (let i = 0; i < length; i++) {
      title.push("ペアプロ /" + new_timetable[i][0]);
      //カレンダーのタイトルは”ペアプロ/受講者指名"とする
      
      set_start_Time.push(new_timetable[i][1])
      //カレンダーの開始時間を設定する
      
      set_end_Time.push(new_timetable[i][2]);
      //カレンダーの終了時間を設定する

      for (let g = 3; g <= lastRow; g++) {
        if (students_master.getRange(g, 2).getValue() == new_timetable[i][0]) {
          address.push(students_master.getRange(g, 5).getValue());　
	 　break
	  //参加者アドレスは'参加者マスタ'を探索する(B列に名前、E列にアドレスが格納されている）
        } else {
          ;
        }
      };
    }

　　let length = new_timetable.length
   　//新たに予定登録する配列の要素数を取得しておく
    
    const tutor = 'xxxx@xxxx.com'
    //チューターのアドレスは固定なので設定しておく
    
    for (let i = 0; i < length; i++) {
      let option = {
        description: 'XXX',
	//説明が必要なら加える
	
        location: 'Google Meet',
	//開催場所を記載する
	
        guests: address[i] + ',' + tutor　
	//参加者はカンマでつなげる
      };
      calendar.createEvent(title[i], set_start_Time[i], set_end_Time[i], option);
      //caledar.createEventでカレンダー予定を作成する
    }

    copyEvent()
    //スナップショットを'GAS用'に記録する
  }
  catch (e) {
    console.log(`エラー : ${e}`);
    //エラーメッセージの取得
  }

}

長いですがここまでがsetEvent()となります。

ポイントとしては開始時間を設定するこちらの箇所になります

  start_time.setFullYear(classday.getFullYear());
          start_time.setMonth(classday.getMonth(),1);
          start_time.setDate(classday.getDate());
	  //授業の開始時間にclassdayから年月日を上書きする

これ、setMonthの部分でわざわざ1日を指定しているのですが、実はこれをしないと2月の予定のみ3月に設定されるというエラーが発生してしまいます。

詳細は下記の記事をみていただくとわかるのですが、スプシにHH:mmの形で時間を打ち込むと、デフォルトで年月日がSat Dec 30 1899と解釈されます。
これにgetMonthメソッドを利用すると2月30日->3月2日と変換されてしまうのです。
それを防ぐために、getMonthで2月を指定するときは1日も一緒に指定してやる必要があると言うわけです。

delete_calendar.gs

こちらは'時間割'で削除されているコマをカレンダー上からも削除するスクリプトになります

function deleteEvent(timetable) {
  try {
    let length = timetable.length
    var address = []
    for (let i = 0; i < length; i++) {
      for (let g = 3; g <= lastRow; g++) {
        if (students_master.getRange(g, 2).getValue() == timetable[i][0]) {
          address.push(students_master.getRange(g, 5).getValue());
          break
	  //参加者アドレスは'参加者マスタ'を探索する(B列に名前、E列にアドレスが格納されている）
	  //setEvent()の時と同じ処理
	  
        } else {
          ;
        }
      };
    }

      for (i = 0; i < length; i++) {
        var deleteTarget = calendar.getEvents(timetable[i][1], timetable[i][2]);
	//参加者カレンダーの対応する時間に入っている予定をgetEventで取得する
	
        for (let target of deleteTarget){
          if (target.getTitle().includes('ペアプロ') == true){
            target.deleteEvent()
	    //取得した予定に'ペアプロ'という単語が入っていればその予定を削除する
          }
        }

      }
    }

  
  catch (e) {
    console.log(`エラー : ${e}`);//エラーメッセージの取得
  }

}

特に難しいところはないと思います。

copy_calendar.gs

こちらは'受講用'のすクリプト実行時のスナップショットを'GAS用'に転記するスクリプトです

function copyEvent() {
    range = 'A1:AO10'
    timetable_master.getRange('A1:AO10').copyTo(gas_master.getRange(range),{contentsOnly:true})
    //'GAS用'に'受講用'の値をそのままcopyToを利用してコピペする。
}

実行頻度

デプロイしたらsetEvent()を1日に1回は自動で回すようにしています。

これで、受講者はスプシの'受講用'シートに名前を入力するだけでよく、カレンダー登録をいちいち行う煩わしさから解放されました。めでたしめでたし。