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

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


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

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

![Clipboard01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228205/057e1acb-387e-c19d-caf3-6924c9634e1d.png)

#完成したコード
```javascript: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);
        }
}
```
#コードの説明

```javascript: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を指定
```
スプレッドシート、カレンダーそれぞれを指定します。

```javascript: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行目の値を取得します。

```javascript:Code.gs
  //本当は日付の妥当性チェックをした方がいい。  
```
本当はA列の値`dateUpdated`が正しい日付であることをチェックしたほうが良いですが、紙面の都合で割愛します。

```javascript: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更新 同日に同じ人のイベントがあったら削除するようコードを修正しました。）
 - スプレッドシート上で`早番`を消しても、カレンダーには`【ジョバンニ】早番`は残ったままになる。