GASでGoogleカレンダーの予定を取得して稼働計画表を生成する - 自動化厨のプログラミングメモブログ │ CODE:LIFE

f:id:maru0014:20201219235550p:plain

この記事は UG Advent Calendar 2020 21日目の記事です。

10月末くらいだったかに来年の稼働計画を立てる時期が来た。今年の実績から概算で入力すればOKだから昨年作った自分用計算シートに値を入れてコピペかなぁと思っていたら、なんと今年は金額ベースの計画入力だけでなく案件ごとの時間数まで入力するフォーマットに変わっていた...。

時間数となると2021のカレンダーに合わせて祝日や曜日のズレも考慮して計算しないといけない。なんてめんどくさいんだ。入力期限明日って言われたしちまちまやってたら間に合わない（余裕持って確認しなかったのが悪いんですけどね）。

Googleカレンダーには全部入力してあるんだしそこから自動生成してくれたらいいのに。

と思ったので作りました。ちまちまデータ入力するより楽しかったです。

概要
解説
あとがき

概要

この記事でできるようになること

GASでGoogleカレンダーに入っている予定を取得してスプレッドシートに出力
複数のカレンダーも同時取得
日付範囲を指定して取得
特定の件名の予定は取得対象から除外する（正規表現）
終日の予定を除外する

目標物確認

こんなかんじのカレンダーがあります。

f:id:maru0014:20201219234430p:plain

ここから移動、MTG、終日の予定を除外してプロジェクトごとの稼働の時間数だけを取得して、以下のような月別・プロジェクト別の時間数を集計します。

f:id:maru0014:20201219234440p:plain

動作イメージ

「設定」シートに取得対象カレンダーIDやその他条件を入力後、上部メニューの GAS ＞カレンダー取得をクリックすると「予定」シートに出力されます。

また、「集計」シートではピボットテーブルを件名と月で時間数を集計しています。

f:id:maru0014:20201219234752g:plain

設定内容

休憩時間付与のしきい値（h）は指定の時間数を超える場合は休憩時間1Hを減算する設定です。

f:id:maru0014:20201219234502p:plain

取得対象カレンダーIDはGoogleカレンダーの設定画面内にあるカレンダーの統合以下に記載されています。

f:id:maru0014:20201219234510p:plain

完成品

細かい事はいいから完成品くれって人用にスプレッドシートごと公開してますのでコピーして使ってください。

Googleカレンダー取得ツール

以下コード全文

// シート取得
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const sheetSchedules = spreadSheet.getSheetByName("予定");
const sheetSettings = spreadSheet.getSheetByName("設定");

// 設定値取得
const calendarIds = sheetSettings.getRange(1, 2).getValue().split(","); // カレンダーID配列
const reExclusion = new RegExp(sheetSettings.getRange(2, 2).getValue()); // 除外ワード
const allDayExclusion = sheetSettings.getRange(3, 2).getValue(); // 終日の予定除外
const startDate = new Date(sheetSettings.getRange(4, 2).getValue()); // 取得開始日
const endDate = new Date(sheetSettings.getRange(5, 2).getValue()); // 取得終了日
const breakTimeThreshold = sheetSettings.getRange(6, 2).getValue(); // 休憩時間付与のしきい値

/**
 * メニュー追加
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi(); // UIクラス取得
  const menu = ui.createMenu("GAS"); // メニュー名セット
  menu.addItem("Googleカレンダー取得", "getGoogleCalendar"); // 関数セット
  menu.addToUi(); // スプレッドシートに反映
}

/**
 * 実行
 */
function getGoogleCalendar() {
  // シート2行名以下をクリア
  let lastRow = sheetSchedules.getLastRow();
  let lastColumn = sheetSchedules.getLastColumn();
  sheetSchedules.getRange(2, 1, lastRow, lastColumn).clearContent();

  // 配列初期化
  let table = new Array();

  // カレンダー数分ループ処理
  for (let i = 0; i < calendarIds.length; i++) {
    // 取得結果の配列を追記
    table = table.concat(
      fetchSchedules(calendarIds[i],reExclusion,allDayExclusion,startDate,endDate)
    );
  }

  if (table.length) {
    sheetSchedules.getRange(2, 1, table.length, table[0].length).setValues(table); // シートに出力
    Logger.log(`${table.length}件の予定を取得しました。`);
    spreadSheet.toast(`${table.length}件の予定を取得しました。`, 'Googleカレンダー取得完了', 5); // 完了メッセージ表示
  } else {
    Logger.log(`${table.length}件の予定を取得しました。`);
    spreadSheet.toast('取得結果が0件です。', 'Googleカレンダー取得完了', 5); // エラーメッセージ表示
  }
}

/**
 * GoogleカレンダーからgetEvents
 * @param {Array} calendarIds 取得対象カレンダーIDの配列
 * @return {Array} 取得結果の二次元配列
 */
function fetchSchedules(calendarId) {
  const schedules = new Array(); // 配列初期化
  const calendar = CalendarApp.getCalendarById(calendarId); // カレンダー
  const calendarName = calendar.getName(); // カレンダー名
  const events = calendar.getEvents(startDate, endDate); // 範囲内の予定を取得

  // 各予定のデータを配列に追加
  for (let i = 0; i < events.length; i++) {
    // 除外対象の場合は処理をスキップ
    if (isExclusion(events[i], reExclusion, allDayExclusion)) continue;

    let start = events[i].getStartTime();
    let end = events[i].getEndTime();

    let event = new Array();
    event.push(calendarName); // カレンダー名
    event.push(events[i].getTitle()); // 件名
    event.push(start); // 開始日時
    event.push(end); // 終了日時
    event.push(start.getMonth() + 1); // 月
    event.push(getOperatingTime(start, end)); // 時間数
    event.push(events[i].getDescription()); // 詳細

    schedules.push(event); // 配列に追加
  }

  return schedules;
}

/**
 * 取得対象の切り分け
 * @param {CalendarEvent} schedule 個別のCalendarEventクラス
 * @return {boolean} 真偽値
 */
function isExclusion(event) {
  // 終日イベントはスキップ
  if (allDayExclusion && event.isAllDayEvent()) return true;

  // 除外ワードを含む場合はスキップ
  if (reExclusion.test(event.getTitle())) return true;

  return false;
}

/**
 * 経過時間数の計算
 * @param {Date} start 予定の開始日時
 * @param {Date} end 予定の終了日時
 * @return {number} 経過時間数
 */
function getOperatingTime(start, end) {
  // 時間数算出
  const time = (end - start) / 1000 / 60 / 60;

  // 休憩時間の減算
  const operatingTime = time >= breakTimeThreshold ? time - 1 : time;

  return operatingTime;
}

解説

それぞれの処理ごとにある程度コメントを書いてあるので不要かもしれませんが一応上から順に解説します。

設定値取得

まずはスクリプトのグローバル変数としてシートと設定値を取得。処理対象シートが複数ある場合は可読性も考慮して getSheetByName で取得します。

シートの並びの変更は影響ないですが、シート名を変更すると取得できなくなります。シートの保護で警告を設定しておくといいかも？

forest.watch.impress.co.jp

// シート取得
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const sheetSchedules = spreadSheet.getSheetByName("予定");
const sheetSettings = spreadSheet.getSheetByName("設定");

カレンダーIDは取得結果を split で配列に変換。除外ワードは取得と同時に正規表現オブジェクトを生成。日付は取得と同時にDateオブジェクトを生成。

// 設定値取得
const calendarIds = sheetSettings.getRange(1, 2).getValue().split(","); // カレンダーID配列
const reExclusion = new RegExp(sheetSettings.getRange(2, 2).getValue()); // 除外ワード
const allDayExclusion = sheetSettings.getRange(3, 2).getValue(); // 終日の予定除外
const startDate = new Date(sheetSettings.getRange(4, 2).getValue()); // 取得開始日
const endDate = new Date(sheetSettings.getRange(5, 2).getValue()); // 取得終了日
const breakTimeThreshold = sheetSettings.getRange(6, 2).getValue(); // 休憩時間付与のしきい値

メニュー追加

GASでは関数名 onOpen とすることでスプレッドシートが開かれたと同時に呼び出される関数を利用できます。Excelで言うところの Workbook_Open みたいなかんじ。

createMenu でスプレッドシートに独自メニューを追加し、 addItem で関数を実行するアイテムを追加しています。

function onOpen() {
  const ui = SpreadsheetApp.getUi(); // UIクラス取得
  const menu = ui.createMenu("GAS"); // メニュー名セット
  menu.addItem("Googleカレンダー取得", "getGoogleCalendar"); // 関数セット
  menu.addToUi(); // スプレッドシートに反映
}

シートの初期化と結果のセット

getGoogleCalendar は「予定」シートの初期化、複数カレンダーを取得する設定の場合にカレンダーリストのループ処理を行い、 fetchSchedules 関数にカレンダーIDやその他の条件を渡すことで予定の配列を取得します。

最終的に全てのカレンダーの予定の配列を結合した上でシートに値をセットします。

concat メソッドは初めて使いましたがイミュータブル（元のオブジェクトを変更しない）なので table = table.concat(<fetchSchedulesの結果>) とすることで「配列に配列を追加」というのを実現しています。

function getGoogleCalendar() {
  // シート2行名以下をクリア
  let lastRow = sheetSchedules.getLastRow();
  let lastColumn = sheetSchedules.getLastColumn();
  sheetSchedules.getRange(2, 1, lastRow, lastColumn).clearContent();

  // 配列初期化
  let table = new Array();

  // カレンダー数分ループ処理
  for (let i = 0; i < calendarIds.length; i++) {
    // 取得結果の配列を追記
    table = table.concat(
      fetchSchedules(calendarIds[i],reExclusion,allDayExclusion,startDate,endDate)
    );
  }

  if (table.length) {
    sheetSchedules.getRange(2, 1, table.length, table[0].length).setValues(table); // シートに出力
    Logger.log(`${table.length}件の予定を取得しました。`);
    spreadSheet.toast(`${table.length}件の予定を取得しました。`, 'Googleカレンダー取得完了', 5); // 完了メッセージ表示
  } else {
    Logger.log(`${table.length}件の予定を取得しました。`);
    spreadSheet.toast('取得結果が0件です。', 'Googleカレンダー取得完了', 5); // エラーメッセージ表示
  }
}

カレンダーから予定を取得して結果を配列に代入

fetchSchedules では第一引数で受け取った calendarId のカレンダーから指定日付範囲内の予定を一括で取得、取得結果をループ処理しながら除外条件にマッチする予定を除いて schedules 配列に格納して返します。

/**
 * GoogleカレンダーからgetEvents
 * @param {Array} calendarIds 取得対象カレンダーIDの配列
 * @return {Array} 取得結果の二次元配列
 */
function fetchSchedules(calendarId) {
  const schedules = new Array(); // 配列初期化
  const calendar = CalendarApp.getCalendarById(calendarId); // カレンダー
  const calendarName = calendar.getName(); // カレンダー名
  const events = calendar.getEvents(startDate, endDate); // 範囲内の予定を取得

  // 各予定のデータを配列に追加
  for (let i = 0; i < events.length; i++) {
    // 除外対象の場合は処理をスキップ
    if (isExclusion(events[i], reExclusion, allDayExclusion)) continue;

    let start = events[i].getStartTime();
    let end = events[i].getEndTime();

    let event = new Array();
    event.push(calendarName); // カレンダー名
    event.push(events[i].getTitle()); // 件名
    event.push(start); // 開始日時
    event.push(end); // 終了日時
    event.push(start.getMonth() + 1); // 月
    event.push(getOperatingTime(start, end)); // 時間数
    event.push(events[i].getDescription()); // 詳細

    schedules.push(event); // 配列に追加
  }

  return schedules;
}

この schedules 配列はシートにそのままセット出来るよう、以下のような二次元配列になっています。

[
  ['稼働スケジュール','プロジェクトA','2021/01/04 09:00','2021/01/04 12:00',1,3,'プロジェクトAの詳細'],
  ['稼働スケジュール','プロジェクトB','2021/01/04 14:00','2021/01/04 18:00',1,4,'プロジェクトBの詳細']
]

除外対象チェック

isExclusion では終日の予定を除外の設定にチェックが入っているかつ予定が終日だった場合

または除外ワードの正規表現にマッチする場合に true を返し、fetchSchedules関数での配列生成のループ処理で continue することでスキップさせています。

getTitle をfetchSchedulesとisExclusion で2回実行しちゃってるので呼び出し元で変数に入れておいても良かったかも。

/**
 * 取得対象の切り分け
 * @param {CalendarEvent} schedule 個別のCalendarEventクラス
 * @return {boolean} 真偽値
 */
function isExclusion(event) {
  // 終日イベントはスキップ
  if (allDayExclusion && event.isAllDayEvent()) return true;

  // 除外ワードを含む場合はスキップ
  if (reExclusion.test(event.getTitle())) return true;

  return false;
}

経過時間数の計算

getOperatingTime では開始日時と終了日時を受け取って範囲内の時間数を算出。

休憩時間の閾値を超える場合は1時間を減算して返します。

/**
 * 経過時間数の計算
 * @param {Date} start 予定の開始日時
 * @param {Date} end 予定の終了日時
 * @return {number} 経過時間数
 */
function getOperatingTime(start, end) {
  // 時間数算出
  const time = (end - start) / 1000 / 60 / 60;

  // 休憩時間の減算
  const operatingTime = time >= breakTimeThreshold ? time - 1 : time;

  return operatingTime;
}