【GAS】カレンダーの予定⇄スプレッドシート 相互に反映

やりたいこと

スプレッドシート⇄カレンダーの相互のやりとりをGASで実現したい。
①スプレッドシートの内容をカレンダーで予定登録。
②カレンダーの内容をスプレッドシートに出力。

上図のようなスプレッドシートのデータを下図のようなカレンダーに反映。
またその逆(カレンダーからスプレッドシート)をGASで操作する。

準備したファイル

• createEvents.gs //①に必要
• getEvents.gs //②に必要
• global_variable.gs //①と②に必要
• create_menu_bar.gs //(メニューバーから関数を実行する場合必要)

共通の変数

★global_variable.gs
GoogleカレンダーのIDだけ共通変数として抜き出し

// GoogleカレンダーID
const MY_CALENDAR_ID = "{読み書きしたいカレンダーのID}";

①スプレッドシートの内容をカレンダーへ登録

★createEvents.gs

function createEvents() {
  // スプレッドシートオブジェクト
  const ss = SpreadsheetApp.getActive()
  // シートオブジェクト
  const sheet = ss.getSheetByName("create");
  // 行・列の定義
  const row = 2;
  const titleCol = 1;
  const startTimeCol = 2;
  const endTimeCol = 3;

  // 予定の最終行・列を取得
  const lastRow = ss.getLastRow();
  // 範囲を指定して予定を一括取得
  const events = sheet.getRange(row,1, lastRow, endTimeCol).getValues();

  try {
    // スプレッドシートの予定を変数に詰めてカレンダーへ登録
    for (i = 0; i <= lastRow - row; i++) {
      let title = events[i][titleCol -1];
      let startTime = events[i][startTimeCol -1];
      let endTime = events[i][endTimeCol -1];

      addCalendarEvent(title, startTime, endTime);
    }
  } catch(e) {
      Logger.log(e);
  }
}

// カレンダーへ予定を登録
function addCalendarEvent(title, startTime, endTime) {
 const calender = CalendarApp.getCalendarById(MY_CALENDAR_ID);
 calender.createEvent(title, startTime, endTime);
}

②カレンダーの内容をスプレッドシートに出力

下記のようなスプレッドシートを用意しておく。(タイトル行もコードに書いてしまっても良かったですね。。)

★getEvents.gs

// スプレッドシートオブジェクト
const ss = SpreadsheetApp.getActive()
// シートオブジェクト
const sheet = ss.getSheetByName("get");

function getEvents() {
  try {
    // カレンダーのデータを取得する範囲を指定
    const startTime = new Date(sheet.getRange('D1').getValue());
    const endTime   = new Date(sheet.getRange('D2').getValue());

    const calendar = CalendarApp.getCalendarById(MY_CALENDAR_ID);
    const events = calendar.getEvents(startTime, endTime);
    outputEvents(events);
  } catch(e) {
    Logger.log(e);
  }
}
// イベントを受け取り、スプレッドシートへの出力
function outputEvents(events) {
 for(let i = 0; i < events.length; i++) {
  // スプレッドシートへ行を追加
  sheet.appendRow(
   [
    events[i].getTitle(),        // タイトル
    getId(events[i].getTitle()), // 予定ID
    events[i].getStartTime(),    // 開始時刻
    events[i].getEndTime(),      // 終了時刻
   ]
  );
 }	
}

// 予定ID用にタイトルを整形
function getId(title) {
 const regex = /[^0-9]/g;
 const id = title.replace(regex, '');
 return id;
}

↓getEvents()を実行してデータの取得ができた。

メニューバーから関数を実行できるようにする

★create_menu_bar.gs

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('GAS');
  menu.addItem('シート → カレンダー', 'createEvents');
  menu.addItem('カレンダー → シート', 'getEvents');
  menu.addToUi();
}

↓赤枠部分で関数を実行することができるようになる。