Google Apps ScriptでGoogleカレンダーからGoogleスプレッドシートに作業時間を自動集計する

main.gs

const CALLENDAR_ID = '<calendar_id>'
const SHEET_ID = '<sheet_id>'

function main() {
  // 現在の年と月を取得
  const [year, month] = getCurrentYearMonth();

  // 月の開始時刻と終了時刻を取得
  const [startTime, endTime] = getCurrentMonthTimes(year, month);

  // カレンダー接続
  const cal = CalendarApp.getCalendarById(CALLENDAR_ID);

  // イベント一覧を取得
  const events = parseCalendarEvents(cal, startTime, endTime)

  // その月の全ての日付を取得 (1列目に利用)
  const dates = getCurrentMonthDates();

  // その月に含まれるラベルのリストを取得 （1行目に利用）
  const labels = getHeaderLabels(events);

  // 日付ごとラベル毎の時間を集計
  const summaries = getTotalTimeByDate(events);

  // 出力用のデータに変換
  const output = makeOutput(dates, labels, summaries);

  // スプレッドシートに接続
  const sp = SpreadsheetApp.openById(SHEET_ID);

  // 2022年1月のような名前のシートを選択
  const sheet = sp.getSheetByName(`${year}年${month}月`)

  // スプレッドシートに書き込み
  writeSpreadSheet(sheet, output);
}

// 現在の年と月を返す
function getCurrentYearMonth() {
  const date = new Date();
  const month = date.getMonth() + 1;
  const year = date.getFullYear();
  return [year, month];
}

// 月の開始時刻と終了時刻を取得
function getCurrentMonthTimes(year, month) {
  const startTime = new Date(`${year}/${month}/01 00:00:00`);
  let endTime;
  if (month == 12) {
    endTime = new Date(`${year+1}/01/01 00:00:00`);
  } else {
    endTime = new Date(`${year}/${month+1}/01 00:00:00`);
  }
  return [startTime, endTime];
}

// カレンダーのイベント情報をパースする
function parseCalendarEvents(cal, startTime, endTime) {
  const calEvents = cal.getEvents(startTime, endTime);
  let events = [];
  for (const calEvent of calEvents) {
    const eventTitle = calEvent.getTitle();
    const [eventName, eventLabel] = parseEventTitle(eventTitle);
    const startTime = calEvent.getStartTime();
    const endTime = calEvent.getEndTime();
    const workTime = getWorkTime(startTime, endTime);
    // 終日のイベントはスキップ
    if (workTime >= 1440) {
      continue;
    }
    const event = {
      eventName: eventName,
      label: eventLabel,
      date: dateToString(startTime),
      startTime: startTime,
      endTime: endTime,
      workTime: getWorkTime(startTime, endTime),
    };
    events.push(event);
  }
  return events;
}

const UNDEFINED_LABEL = "未分類";

// イベントタイトルをパースする
// "[label] eventName" の形式のイベントをパースし、イベント名とラベルを返す
// labelがない場合は、タイトル全体をイベント名とし、ラベルは”未分類”とする
function parseEventTitle(title) {
  let eventName = title.trim();
  const group = eventName.match(/\[(.*)\](.*)/);
  let eventLabel;
  if (group && group.length > 0) {
    eventLabel = group[1].trim();
    eventName = group[2].trim();
  } else {
    eventLabel = UNDEFINED_LABEL;
  }
  return [eventName, eventLabel];
}

// 作業時間の計算（分）
function getWorkTime(startTime, endTime) {
  return (endTime.getTime() - startTime.getTime()) / 1000 / 60
}

// dateを文字列2022/1/1のような文字列に変換
function dateToString(date) {
  const _year = date.getFullYear();
  const _month = date.getMonth() + 1;
  const _date = date.getDate();
  return `${_year}/${_month}/${_date}`;
}

// その月の全ての日付を返す
function getCurrentMonthDates() {
  const date = new Date();
  date.setDate(1);
  const month = date.getMonth();

  const dates = [];
  while (date.getMonth() == month) {
    dates.push(new Date(date));
    date.setDate(date.getDate() + 1);
  }
  return dates;
}

// ユニークなラベルを取得
function getHeaderLabels(events) {
  let labels = new Set();
  for (event of events) {
    labels.add(event.label);
  }

  // 未分類がある場合は最後に移動
  if (labels.has(UNDEFINED_LABEL)) {
    labels.delete(UNDEFINED_LABEL);
    labels = Array.from(labels);
    labels.sort();
    labels.push(UNDEFINED_LABEL);
    return labels;
  }
  labels = Array.from(labels);
  labels.sort();
  return labels;
}

// 日付ごと、ラベルごとの作業合計時間を求める
function getTotalTimeByDate(events) {
  summaries = {};
  for (const event of events) {
    if (summaries[event.date] === undefined) {
      summaries[event.date] = {};
    }
    if (summaries[event.date][event.label] === undefined) {
      summaries[event.date][event.label] = 0;
    }
    summaries[event.date][event.label] += event.workTime;
  }
  return summaries;
}

// 日付とラベルと合計値から、以下のような2x2の二次元配列を作成する
//          lable1 label2 label3
// 2022/1/1 val    val    val
// 2022/1/2 val    val    val
function makeOutput(dates, labels, summaries) {
  const output = [[]];

  // 左上は飛ばす
  output[0].push(null);

  // ヘッダ行の追加
  for (const label of labels) {
    output[0].push(label);
  }

  for (date of dates) {
    const row = [];
    const dateKey = dateToString(date);
    row.push(dateKey);
    for (label of labels) {
      const val = summaries[dateKey][label] || null;
      row.push(val);
    }
    output.push(row);
  }
  return output;
}

// スプレッドシートに書き込み
function writeSpreadSheet(sheet, output) {
  sheet.clear();
  for (let i = 0; i < output.length; i++) {
    for (let j = 0; j < output[i].length; j++) {
      const row = i + 1;
      const column = numToAlpha(j);
      sheet.getRange(`${column}${row}`).setValue(output[i][j]);
    }
  }
}

// 数値をアルファベットに変換 (0 → A, 1 → B, 2 → C)
function numToAlpha(num) {
  return String.fromCharCode(parseInt(num) + 65);
}

https://note.com/sat0b3ee/n/nd419ae36e8e7