【業務効率化】GASでスプレッドシートの予定をカレンダーに登録しよう

はじめに

単純作業の繰り返しってシンプルに辛いですよね？
友人がそんな状況だったのでお手伝いしました。

前提として、スプシでスケジュールが送られて来てそれをカレンダーにて入力するという業務が発生していました。
一日ならまだしも、毎月20日分を15分刻みのスケジュールを入れるとなるとしんどいですよね。

そこで今回は3分でできるポチポチと少しの入力で1時間かかっていたものが終わるスクリプトを作成しました。

開発手順

相談を受けてまずはゴールを明確にしました。

ゴール

名前とGmailを入力したらその人のGoogleカレンダーに週の予定が色分けされて挿入される
週の予定としているのはスケジュールが週毎にシートがわかれているためです

少し触ってみる

GASは業務でSlackとスプシを連携させたりと触った経験はあったのですがブランクがあったので少し調査をしました。
カレンダーの予定の作成は初めてだったのでどの様にすると良いかも調査をして以下の工程で進める様にしました。

var1.0

メールアドレス、対象名（なくてもOK）、挿入したい行（複数）、基準日の4つをシートに入力し、GASを起動すると指定のメールアドレスのカレンダーに予定が作成される

var2.0

メールアドレス、対象名（なくてもOK）、挿入したい行（複数）、基準日の4つをシートに入力し、GASを起動すると指定のメールアドレスのカレンダーに色分けされた予定が作成される

var3.0 未実装

メールアドレス、対象名（なくてもOK）、の2つをシートに入力し、GASを起動すると指定のメールアドレスのカレンダーに色分けされた予定が作成される

開発

手順と方針が決まったのであとは開発をしていきます。
以下がvar2.0まで実装した状態のコードです。

gas.js

/**
 * スプレッドシート表示の際に呼出し
 */
function onOpen() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();

  // スプレッドシートのメニューにカスタムメニュー「カレンダー連携 > 実行」を作成
  let subMenus = [];
  subMenus.push({
    name: "実行",
    functionName: "createSchedule", //実行で呼び出す関数を指定
  });
  ss.addMenu("カレンダー連携", subMenus);

  // デバック用
  // createSchedule();
}

/**
 * 予定を作成する
 */
function createSchedule() {
  // シートを取得
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // 連携するアカウント
  const gAccount = sheet.getRange(8, 31).getValues()[0][0];
  console.log(gAccount);

  const targetName = sheet.getRange(11, 31).getValues()[0][0];
  console.log(targetName);

  const targetRows = sheet.getRange(14, 31).getValues()[0][0];
  const targetRowsArr = targetRows.split(" ");
  console.log(targetRowsArr);

  const startDay = sheet.getRange(17, 31).getValues()[0][0];
  console.log(startDay);

  const options = {
    // description: "作成テスト",
  };

  // googleカレンダーの取得
  const calender = CalendarApp.getCalendarById(gAccount);

  // カレンダー登録日
  let scheduleDay = startDay;

  for (const [i, row] of targetRowsArr.entries()) {
    // 登録したいスケジュール
    let schedule = sheet.getRange(row, 6, 1, 56).getValues()[0];
    console.log(schedule);


    // 日付の変更
    if (i > 0) {
      scheduleDay = new Date(scheduleDay);
      scheduleDay = scheduleDay.setDate(scheduleDay.getDate() + 1);
    }

    for (const [index, title] of schedule.entries()) {
      let date = new Date(scheduleDay);
      let times = getTimesByDate(date);
      let startTime = times[index];
      let endTime;

      // forEachを連続数分スキップ
      if (title == schedule[index - 1]) {
        continue;
      }

      // スケジュールが連続してるかで分岐
      if (title == schedule[index + 1]) {
        // 連続している場合
        let consecutiveCount = countConsecutive(schedule, index, title);
        endTime = times[index + consecutiveCount];
        createEvent(calender, title, date, startTime, endTime, options);
      } else {
        // 一コマの場合
        endTime = times[index + 1];
        createEvent(calender, title, date, startTime, endTime, options);
      }
    }
  }

  // ブラウザへ完了通知
  Browser.msgBox("完了");
}

// 日次を算出
function getTimesByDate(date) {
  const startTime = new Date(date.getFullYear(), date.getMonth(), date.getDate(), 8, 15, 0);
  const times = {};

  for (let i = 1; i <= 96; i++) {
    const time = new Date(startTime.getTime() + (i - 1) * 15 * 60 * 1000); // 15分単位で加算
    times[i] = time;
  }

  return times;
}

// スケジュールが連続している値を算出
function countConsecutive(arr, startIndex, value) {
  let count = 0;
  let index = startIndex;

  while (arr[index] === value) {
    count++;
    index++;
  }

  return count;
}

function getColorIdByString(inputString) {
  let colorId;

  switch (inputString) {
    case "BRK":
      colorId = "2"; //  ライトグリーン
      break;
    case "T":
      colorId = "3"; // ライトパープル
      break;
    case "F+P":
      colorId = "4"; // パープルレッド
      break;
    case "N":
      colorId = "5"; // イエロー
      break;
    case "L":
      colorId = "6"; // オレンジ
      break;
    case "1x1":
      colorId = "7"; // シアン
      break;
    case "P":
      colorId = "8"; // グレー
      break;
    case "MTG":
      colorId = "9"; // ブルー
      break;
    default:
      colorId = "1"; // デフォルト（無色）
      break;
  }

  return colorId;
}

// カレンダー作成
function createEvent(calender, title, date, startTime, endTime, options) {
  try {
    if (title !== "") {
      // 開始日時をフォーマット
      let startDate = new Date(date);
      startDate.setHours(startTime.getHours());
      startDate.setMinutes(startTime.getMinutes());
      // 終了日時をフォーマット
      let endDate = new Date(date);
      endDate.setHours(endTime.getHours());
      endDate.setMinutes(endTime.getMinutes());
      // 予定を作成
      let event = calender.createEvent(title, startDate, endDate, options);
      // 色を追加
      let colorId = getColorIdByString(title);
      event.setColor(colorId);
    }
  } catch (e) {
    Logger.log(e);
  }
}

実行してみる

1. 実行ボタンを押す

2. 完了

3. カレンダーを見てみる

おわりに

今回はGASを使った業務効率化をしてきました。
作成したものが誰かの役にたって活躍してくれると嬉しいです😚

参考文献