5
8

More than 1 year has passed since last update.

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

Last updated at Posted at 2023-06-14

やりたいこと

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

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

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

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

イメージ

1118A5C9-E3FE-4913-8676-372A153A9ADF_4_5005_c.jpeg
上の添付の様なスプレッドシートを、次のようにボタンひとつで、Googleカレンダーに登録します。
07D2B487-8E29-43C4-9FD6-1BF562A08C86.png

0.事前にやること

①スプレッドシート作成

15048BAD-09A9-4591-A6EE-611A8925A5ED.png
色とカレンダー種別はプルダウンで選択できるようにしておきます。

ちなみに色リストはこんな感じ
745A5938-C0FC-4620-88DE-54B63EB189A6.png

②スクリプトを設定

「拡張機能」 → Apps Script
AAE9F008-5A01-4347-B558-B027D0152A48_4_5005_c.jpeg

Apps Scriptの画面になるのでコードを記載していきます。
49097B09-D6EB-440E-B2DB-649B70055C8C.png
ファイル名、プロジェクト名は何でもいいです。

1.スプレッドシートの内容をカレンダーへ登録するための記述

以下の関数を記述します。

①CreateSchedule()関数

・スプレッドシートから予定の情報を読み取り、Googleカレンダーに予定を追加します。
・予定の一覧をスプレッドシートから取得し、順番に予定を作成します。
・カレンダー名やイベントの開始日時・終了日時などの情報を使用して、予定を作成します。
・予定の作成が成功した場合、スプレッドシートの対応する行に予定のIDを記録し、ステータスを「済」に変更します。

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)関数

引数として受け取った色の文字列に基づいて、対応する色の数値を返します。

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を使用して、対応するイベントを特定し削除します。

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()関数を呼び出して新たな予定を追加します。
つまり、イベントを更新するために、まず既存のイベントを削除し、その後新しい予定を追加します。

code.gs
//イベントの更新
function UpdateEvent() {
  DeleteEvent()  //イベントの削除
  CreateSchedule() //イベントの追加
}

2.スプリプトをワンボタンで発火させる

今回はボタン一つでシートに記載してある予定をカレンダーに登録したいので、シート上に図形描画でボタンを作成します。
91787DEE-8C5E-47FC-AE04-7B0C83ED456F_4_5005_c.jpeg

「拡張機能」 → 「マクロ」 → 「マクロをインポート」
からUpdateEventDeleteEventを追加します。
D8BC5576-D43B-49A0-AD6B-E547B41689A0_4_5005_c.jpeg

48370DE1-A98F-4050-8BE1-6D280DBF303A_4_5005_c.jpeg

先ほど作成したボタン画像の右クリックすると、「・・・」マークが表示されるので、そこから「スクリプトを割り当て」をクリック
63FF1ACE-D512-44BD-91CC-C5998F6451E7_4_5005_c.jpeg

割り当てたいスクリプトを入力します。
69478AD0-B803-441B-85B0-366E352052AA_4_5005_c.jpeg

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

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

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

3.使い方

「追加・変更」ボタン

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

「削除のみ」ボタン

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

5
8
3

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
5
8