【GAS】スプレッドシートに記載した予定をGoogleカレンダーにまとめて反映させたい

## やりたいこと
始業前に今日やるタスクをGoogleカレンダーにまとめたいが、一つずつ入力するのはめんどくさい。。。
スプレッドシートに記載した予定をGoogleカレンダーにまとめて反映させれば便利かも。

そこで以下のことをGASで実現したいと思います。

`スプレッドシートに記載した予定をGoogleカレンダーにまとめて反映させたい。`

Googleカレンダーに予定を一つずつ入力することもなくなり、スプレッドシートからサクッと予定を流し込み出来ます。

## イメージ
![1118A5C9-E3FE-4913-8676-372A153A9ADF_4_5005_c.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2658262/fe916a18-b034-b6d7-c045-6ab3269dda45.jpeg)
上の添付の様なスプレッドシートを、次のようにボタンひとつで、Googleカレンダーに登録します。
![07D2B487-8E29-43C4-9FD6-1BF562A08C86.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2658262/5a1ba06d-c26a-a500-82f2-4a09f4f2f619.png)

## 0.事前にやること
### ①スプレッドシート作成
![15048BAD-09A9-4591-A6EE-611A8925A5ED.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2658262/c749b0a3-8a25-f2de-3d4e-7f833554de18.png)
色とカレンダー種別はプルダウンで選択できるようにしておきます。

ちなみに色リストはこんな感じ
![745A5938-C0FC-4620-88DE-54B63EB189A6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2658262/27e8f707-6cf2-93bb-d08e-70674915eb1b.png)


### ②スクリプトを設定
「拡張機能」 → Apps Script
![AAE9F008-5A01-4347-B558-B027D0152A48_4_5005_c.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2658262/7690c8c0-cd01-eb30-e9b1-bd0f830ab2a6.jpeg)

Apps Scriptの画面になるのでコードを記載していきます。
![49097B09-D6EB-440E-B2DB-649B70055C8C.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2658262/4feabf13-bc3c-ceb3-5ca2-1e3981fc3dd7.png)
ファイル名、プロジェクト名は何でもいいです。

## 1.スプレッドシートの内容をカレンダーへ登録するための記述
以下の関数を記述します。
### ①CreateSchedule()関数
・スプレッドシートから予定の情報を読み取り、Googleカレンダーに予定を追加します。
・予定の一覧をスプレッドシートから取得し、順番に予定を作成します。
・カレンダー名やイベントの開始日時・終了日時などの情報を使用して、予定を作成します。
・予定の作成が成功した場合、スプレッドシートの対応する行に予定のIDを記録し、ステータスを「済」に変更します。
```javascript:code.gs
/**
 * Googleカレンダーに予定を追加する
 */
function CreateSchedule() {

  // 読み取り範囲（表の始まり行と終わり列）
  const topRow = 2;
  const lastCol = 11;
  const statusCellCol = 1;
  const EventIDCol = 11; //K列

  // 予定の一覧バッファ内の列(0始まり)
  const statusNum = 0;
  const startdayNum = 1;
  const startNum = 2;
  const enddayNum = 3;
  const endNum = 4;
  const titleNum = 5;
  const locationNum = 6;
  const descriptionNum = 7;
  const colorNum = 8;  //色の列
  const calnameNum = 9;    //カレンダー名の列

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

  // カレンダーの設定
  let calendar = CalendarApp.getDefaultCalendar();

  // 予定の最終行を取得
  let lastRow = sheet.getLastRow();

  // 予定の一覧をバッファに取得
  const contents = sheet.getRange(topRow, 1, lastRow - topRow + 1, lastCol).getValues();

  // 順に予定を作成
  for (let i = 0; i < contents.length; i++) {
    // 「済」と「ー」の場合は無視する
    if (contents[i][statusNum] === "済" || contents[i][statusNum] === "ー") {
      continue;
    }

    // 値をセット 日時はフォーマットして保持
    let startday = contents[i][startdayNum];
    let startTime = contents[i][startNum];
    let endday = contents[i][enddayNum];
    let endTime = contents[i][endNum];
    let title = contents[i][titleNum];
    let calendarName = contents[i][calnameNum];    //設定するカレンダー名

    // 場所と詳細をセット
    let options = { location: contents[i][locationNum], description: contents[i][descriptionNum] };

    console.log(startday + " " + contents[i][titleNum]);

    try {
      let event;

      if (calendarName !== "") {
        let calendars = CalendarApp.getCalendarsByName(calendarName);
        if (calendars.length > 0) {
          calendar = calendars[0];
          console.log(calendars[0].getName());
        }
      }

      // 終了日時を作成
      if (endday === '') {
        endday = startday;
      }
      let startDate = new Date(startday);
      let endDate = new Date(endday);

      // 開始終了時刻が無ければ終日で設定
      if (startTime === '' || endTime === '') {
        //予定を作成
        endDate.setDate(endDate.getDate() + 1);  //★日付を1日プラスする必要あり★
        event = calendar.createAllDayEvent(
          title,
          startday,
          endDate,
          options
        );
      } else {
        // 開始日時を作成
        startDate.setHours(startTime.getHours());
        startDate.setMinutes(startTime.getMinutes());

        endDate.setHours(endTime.getHours());
        endDate.setMinutes(endTime.getMinutes());

        // 予定を作成
        event = calendar.createEvent(
          title,
          startDate,
          endDate,
          options
        );
      }

      if (contents[i][colorNum] !== "") {
        let color = contents[i][colorNum];
        event.setColor(getColorNum(color));
      }

      console.log(event.getId());
      sheet.getRange(topRow + i, EventIDCol).setValue(event.getId());

      // 予定が作成されたら「済」にする
      sheet.getRange(topRow + i, statusCellCol).setValue("済");

    } catch (e) {
      Logger.log(e);
    }
  }
}
```


### ②getColorNum(color)関数
引数として受け取った色の文字列に基づいて、対応する色の数値を返します。
```javascript:code.gs
/**
 * スプレッドシートから引数の色を取得
 */
function getColorNum(color) {
  let cnum;
  switch (color) {
    case "PALE_BLUE": cnum = 1; break;
    case "PALE_GREEN": cnum = 2; break;
    case "MAUVE": cnum = 3; break;
    case "PALE_RED": cnum = 4; break;
    case "YELLOW": cnum = 5; break;
    case "ORANGE": cnum = 6; break;
    case "CYAN": cnum = 7; break;
    case "GRAY": cnum = 8; break;
    case "BLUE": cnum = 9; break;
    case "GREEN": cnum = 10; break;
    case "RED": cnum = 11; break;
    default: cnum = 0; break;
  }
  console.log(cnum);
  return cnum;
}
```

### ③DeleteEvent()関数
・スプレッドシート内の空白の行に対応するイベントをGoogleカレンダーから削除します。
・予定の一覧をスプレッドシートから取得し、順番に予定を削除します。
・カレンダー名とイベントIDを使用して、対応するイベントを特定し削除します。
```javascript:code.gs
/**
 * Googleカレンダーからイベントを削除する
 * 先頭カラムが空白の行のイベントを削除する
 */
function DeleteEvent() {

  // 読み取り範囲（表の始まり行と終わり列）
  const topRow = 2;
  const lastCol = 11;

  // 予定の一覧バッファ内の列(0始まり)
  const eventStatusColumnIndex = 0;
  const calendarNameColumnIndex = 9;    //カレンダー名の列
  const eventIdColumnIndex = 10;   //イベントID

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

  // カレンダーの設定
  let calendar = CalendarApp.getDefaultCalendar();

  // 予定の最終行を取得
  let lastRow = sheet.getLastRow();

  // 予定の一覧をバッファに取得
  const contents = sheet.getRange(topRow, 1, lastRow - topRow + 1, lastCol).getValues();

  // 順に予定を削除
  for (let i = 0; i < contents.length; i++) {

    // 「済」の場合は無視する
    if (contents[i][eventStatusColumnIndex] === "済") {
      continue;
    }

    let calendarName = contents[i][calendarNameColumnIndex];    //設定するカレンダー名
    let eventId = contents[i][eventIdColumnIndex];

    Logger.log('Deleting event ID ' + eventId + ' on row ' + (i + topRow + 1));

    try {
      // カレンダーの切り替え
      if (calendarName !== "") {
        let calendars = CalendarApp.getCalendarsByName(calendarName);
        for (let j in calendars) {
          calendar = calendars[j];
          Logger.log('Switching to calendar: ' + calendars[j].getName());
        }
      }

      // 保持していたイベントIDからイベントを削除する
      let event = calendar.getEventById(eventId);

      // イベントの削除
      event.deleteEvent();

    } catch (e) {
      Logger.log('Error on row ' + (i + topRow + 1) + ': ' + e.toString());
    }
  }
}
```

### ④UpdateEvent()関数
`DeleteEvent()`関数を呼び出してイベントを削除し、`CreateSchedule()`関数を呼び出して新たな予定を追加します。
つまり、イベントを更新するために、まず既存のイベントを削除し、その後新しい予定を追加します。
```javascript:code.gs
//イベントの更新
function UpdateEvent() {
  DeleteEvent()  //イベントの削除
  CreateSchedule() //イベントの追加
}
```

## 2.スプリプトをワンボタンで発火させる
今回はボタン一つでシートに記載してある予定をカレンダーに登録したいので、シート上に図形描画でボタンを作成します。
![91787DEE-8C5E-47FC-AE04-7B0C83ED456F_4_5005_c.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2658262/013b56ed-b9c2-4a53-1305-ef21743c2dfa.jpeg)

「拡張機能」 → 「マクロ」 → 「マクロをインポート」
から`UpdateEvent`と`DeleteEvent`を追加します。
![D8BC5576-D43B-49A0-AD6B-E547B41689A0_4_5005_c.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2658262/cc2afc91-1d83-539d-507d-755e8680dd7b.jpeg)

![48370DE1-A98F-4050-8BE1-6D280DBF303A_4_5005_c.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2658262/aab04874-1657-538c-8f93-5acb3e27d420.jpeg)

先ほど作成したボタン画像の右クリックすると、「・・・」マークが表示されるので、そこから「スクリプトを割り当て」をクリック
![63FF1ACE-D512-44BD-91CC-C5998F6451E7_4_5005_c.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2658262/024b742f-7325-895e-75f9-442c15eb570e.jpeg)

割り当てたいスクリプトを入力します。
![69478AD0-B803-441B-85B0-366E352052AA_4_5005_c.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2658262/455e3575-7d26-85f3-3d6d-4b3a9e97cdb3.jpeg)

割り当てるスクリプトはそれぞれ以下の通り（先ほど追加した関数名）
・「追加・変更」ボタン・・・`UpdateEvent`
・「削除のみ」ボタン・・・`DeleteEvent`

これで作成したスクリプトをボタンクリックでショートカットで使用できるようになります。

:::note info
初めてカレンダー連携を実行する時は認証画面が表示されます。
:::


## 3.使い方
### 「追加・変更」ボタン
・項目を入力し、ボタンを押すとGoogleカレンダーに追加されます。
・追加出来たらA列に「済」と自動入力されます。
・変更したい場合
　①項目を変更
　②A列の「済」を消す

### 「削除のみ」ボタン
・A列の「済」を消してから、ボタンを押すとGoogleカレンダーから削除されます。