Googleスプレッドシートに入力したシフトをGoogleカレンダーに反映する方法

やりたいこと

下記のようなスプレッドシートでシフトを管理しているとします。

日付	ジョバンニ	カムパネラ
2019/07/09	早番	遅番
2019/07/10	早番	遅番
2019/07/11	早番	遅番
2019/07/12	通し	休
2019/07/13	遅番	早番

スプレッドシートでシフトを入力したら、Googleカレンダーに反映されるようにしたい。

完成したコード

Code.gs

function spreadsheetToCalendar() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const s = ss.getSheetByName('Sheet1'); // シート名で指定
  const editedCell = ss.getActiveCell().getA1Notation(); //編集されたセルを取得
  const editedVal = ss.getActiveRange().getValue(); //編集された値を取得
  const calendarId = 'カレンダーID' //内容を反映したいGoogleカレンダーのIDを指定

  //更新セルのA列の値を取得（日付が入っているはず）
  const dateUpdated = s.getRange('A'+ s.getActiveCell().getRowIndex()).getValue();

  //更新セルの1行目の値を取得。(名前が入っているはず)
  //セル位置を取得して、数字（行番号）を1にreplaceする。
  const whomUpdated = s.getRange(s.getActiveCell().getA1Notation().replace(/\d{3}/,'1')).getValue();

  //本当は日付の妥当性チェックをした方がいい。  

  //カレンダーに連携する。
  const eventSubject = '【' + whomUpdated + '】' + editedVal;
  const myCalendar = CalendarApp.getCalendarById(calendarId);       

       // ちゃんと存在したら
        if(editedVal !== ''){
          // 同じ時刻に、同じwhomUpdatedで登録されているイベントを探してみて、
          var existingEvents = myCalendar.getEventsForDay(dateUpdated, {search: '【' + whomUpdated + '】'})
          Logger.log(existingEvents)

          if(existingEvents.length == 0){
            //存在していなかったら何もしない
          }
          else{ //存在していたらイベントを削除
            for (i = 0 ; i < existingEvents.length ; i++){            
              existingEvents[i].deleteEvent()
              Logger.log(existingEvents[i].getTitle() + 'を削除しました。');

            }           
          }
          myCalendar.createAllDayEvent(eventSubject, dateUpdated)
          Logger.log("イベントを作成しました。");

        }else{       
          Logger.log("値がありません。" + editedCell);
        }
}

コードの説明

Code.gs

function spreadsheetToCalendar() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const s = ss.getSheetByName('Sheet1'); // シート名で指定
  const editedCell = ss.getActiveCell().getA1Notation(); //編集されたセルを取得
  const editedVal = ss.getActiveRange().getValue(); //編集された値を取得
  const calendarId = 'カレンダーID' //内容を反映したいGoogleカレンダーのIDを指定

スプレッドシート、カレンダーそれぞれを指定します。

Code.gs

  //更新セルのA列の値を取得（日付が入っているはず）
  const dateUpdated = s.getRange('A'+ s.getActiveCell().getRowIndex()).getValue();

  //更新セルの1行目の値を取得。(名前が入っているはず)
  //セル位置を取得して、数字（行番号）を1にreplaceする。
  const whomUpdated = s.getRange(s.getActiveCell().getA1Notation().replace(/\d{3}/,'1')).getValue();

「何日の」「誰の」シフトが更新されたのかを取得します。
編集されたセルのA列の値を取得することで「何日の」が、1行目の値を取得することで「誰の」がわかります。

• 「何日の」
  • getRowIndex()で更新セルの行数を取得し、頭に文字列Aを付与することで、A列の値を取得します。
• 「誰の」
  • getA1Notation()で更新セルの位置を取得し、数字部分を1に置き換えることで、1行目の値を取得します。

Code.gs

  //本当は日付の妥当性チェックをした方がいい。  

本当はA列の値dateUpdatedが正しい日付であることをチェックしたほうが良いですが、紙面の都合で割愛します。

Code.gs

  //カレンダーに連携する。
  const eventSubject = '【' + whomUpdated + '】' + editedVal;
  const myCalendar = CalendarApp.getCalendarById(calendarId);       

       // ちゃんと存在したら
        if(editedVal !== ''){
          // 同じ時刻に、同じwhomUpdatedで登録されているイベントを探してみて、
          var existingEvents = myCalendar.getEventsForDay(dateUpdated, {search: '【' + whomUpdated + '】'})
          Logger.log(existingEvents)

          if(existingEvents.length == 0){
            //存在していなかったら何もしない
          }
          else{ //存在していたらイベントを削除
            for (i = 0 ; i < existingEvents.length ; i++){            
              existingEvents[i].deleteEvent()
              Logger.log(existingEvents[i].getTitle() + 'を削除しました。');

            }           
          }
          myCalendar.createAllDayEvent(eventSubject, dateUpdated)
          Logger.log("イベントを作成しました。");

        }else{       
          Logger.log("値がありません。" + editedCell);
        }
}

内容をGoogleカレンダーに連携します。
カレンダーに表示するイベントの件名は【ジョバンニ】早番のようなフォーマットにして、終日のイベントとして登録します。

あとはスプレッドシートの更新時にスクリプトが起動するようにトリガーをはってやればOKです。

やり残したこと

• スプレッドシート側を更新しても、カレンダーには反映されない。
  • 例えば、スプレッドシート上で早番を遅番に変更すると、カレンダーには【ジョバンニ】早番と【ジョバンニ】遅番の2つができる。（2019/08/27更新 同日に同じ人のイベントがあったら削除するようコードを修正しました。）
  • スプレッドシート上で早番を消しても、カレンダーには【ジョバンニ】早番は残ったままになる。