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

Junichi Sato

2022年1月17日 23:29

最近は業務委託で働いているため、稼働時間を測る必要があるのですが、何時間稼働したか集計するのが大変なので、Googleカレンダーのスケジュールからスプレッドシートに自動的に稼働時間を集計するようにしてみました。

この記事では、カレンダーを使って時間を記録することの利点を説明し、どうやって自動集計を実装するのかについても解説します。

Google Apps Script (GAS)

GoogleカレンダーやGoogleスプレッドシートなどのサービスは、Google Apps Scriptによって、JavaScriptで各サービスを操作することが可能です。

スクリプトの実行はスケジューリングしたり、イベントをトリガーとすることもできます。そのため以下の処理を実現できます。

Googleカレンダーに修正を入れる
トリガーによってカレンダーのイベントの合計時間を集計する
集計スクリプトがGoogleスプレッドシートに書き込む

毎回ローカルマシンでスクリプトを動かしたり、サーバー上でcronを動かしたりしなくても大丈夫です。気軽に自動化できます。

ルール

1ヶ月くらい運用してみているのですが、色々試行錯誤した結果、次のようなルールになりました。

一通り予定を入れておき、ダメなら修正する

一通り予定を入れておいて、スケジュール通りでなければ後で修正する方法を取ってます。過去のカレンダーは実績、未来のカレンダーは計画となります。

タスク管理に、Living In Your Calendarというテクニックがあります。タスクをカレンダーにスケジュールして、予定をブロックしてしまうという方法です。

カレンダーにタスクをスケジュールすると、この時間はこれを取り組むという意識を持ちやすいです。また作業時間の見積もりも行えます。重要なタスクが瑣末なタスクで手をつけられなくなることも防ぎやすいです。

その日の朝に「今日は何時にこの仕事をする」という計画を立て、作業に着手します。ここでの計画は完全なる理想なので、OKRのストレッチゴールのように60%~70%達成できれば良いと考えています。毎日ストレッチな目標を立てている感覚です。

一つのカレンダーを使う

Googleカレンダーでは複数のカレンダーを作れるため、最初は案件ごとにカレンダーを作り、それぞれ集計しようと思っていたのですが、案件やカテゴリの数だけカレンダーの種類が増えてしまう問題がありました。

そのため、カレンダーは一つにします。その方がプログラムも簡単になります。

タイトルにラベルをつける

カレンダーを一つにする代わりに、タイトルにカテゴリを表すラベルをつけて案件やカテゴリ毎に集計できるようにします。ラベルは"["と"]"で囲むルールにしています。

例えば、集計したいカレンダーのイベントは以下のようになります。

実装について

準備

入力用のGoogleカレンダーと出力用のスプレッドシートを準備します。一つのスプレッドシート上に月ごとのタブを作って、まとめて管理したいので、スプレッドシートに「2022年1月」のような名前で新規タブを作成します。この後のコードでタブ名でシート選択する箇所があります。

コード全体

コード全体ですが、こちらのgistに置いてあります。少し長いのでこちらをご参照ください。以下ではコードの中身について解説します。https://gist.github.com/sat0b/74f352239fdb5a6e6f7dea456498458d

main関数

詳細の説明に入る前に、プログラムのエントリーポイントであるmain関数を説明します。

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);
}

ポイントは、最初にカレンダーからイベント情報をまとめてパースして、独自定義のイベント用のデータ構造に変換すること、また、スプレッドシートへの出力前にシートに近い出力用データ構造に変換することです。外部との入力と出力はできるだけ単純な方が見通しが良くなると思います。

一番最初のCALLENDAR_IDとSHEET_IDは自分で用意したIDに変更します。カレンダーのIDはデフォルトのカレンダーの場合は、メールアドレスになります。別に作った場合は、Googleカレンダーの設定から確認することができます。スプレッドシートのIDはスプレッドシートのURLの/d/以降のパスの文字列です。

開始、終了時刻の取得

カレンダーの取得の前に、月の開始時刻と終了時刻を取得します。ここからカレンダーから取得するイベントの範囲を決定します。

// 現在の年と月を返す
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];
}

カレンダーからイベントを取得する

カレンダーからイベントを取得するためには、以下のようにIDを指定してgetCalendarById (doc)でCalendarAppを取得します。

const cal = CalendarApp.getCalendarById('<calendar_id>');

parseCalendarEventsは、Googleカレンダーのイベント情報から、データを抽出し、イベント名、ラベル名、開始時刻、終了時刻、作業時間を計算します。

// カレンダーからイベントを取得
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}`;
}

cal.getEvents (doc)によって、該当した期間のイベントを取得できます。
作業時間は分で集計します。
終日のイベント（1440分）はスキップしています。
タイトルのパースに正規表現のキャプチャグループを使います。

日付の一覧を取得

その月の全ての日付を作って返す関数を作ります。

// その月の全ての日付を返す
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;
}

ラベル一覧を取得

イベント情報からその月のラベルを全て取得します。これを利用して、スプレッドシートのヘッダ行を作ります。Set型を使って重複を除去しており、UNDEFINED_LABELが存在する場合は一番最後に移動します。（未分類は一番後ろの列に配置したいためです）

// ユニークなラベルを取得
function getHeaderLables(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;
}

日毎、ラベル毎の合計時間を求める

Event型のデータから、日付ごとにラベルの合計時間を求めます。日付用、ラベル用で二次元マップとなりますが、初回はオブジェクトの初期値が存在しないため、undefinedとなる場合は加算前に初期値を作ります。

// 日付ごと、ラベルごとの作業合計時間を求める
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;
}

スプレッドシートに書き込み

SpreadSheetApp.openById (doc)を用いて、スプレッドシートを操作します。

const sp = SpreadsheetApp.openById(id);

冪等な処理となるように、データの書き込み前に全てクリアするようにします。クリアした後は、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);
}

トリガーの設定

上記のコードをGoogle Apps Scriptで新規プロジェクトを作成し、コード.gsに貼り付けます。

そのままだとタイムゾーンが日本になっていないため、カレンダーの時刻がずれてしまうと思います。プロジェクトの設定から「appsccript.json」を表示するように変更し、「appsccript.json」のタイムゾーンを日本に変更します。

{
  "timeZone": "Asia/Tokyo",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

プロジェクトのサイドバーの時計マークからトリガーの設定画面を開き、トリガーを設定することで、Googleカレンダーへの書き込みがあるたびに、上で書いた集計スクリプトが実行されるようになります。main関数を実行し、イベントのソースで「カレンダーから」を選択します。

実行結果

上記のコードを実行すると以下のようになりました。(ラベル名のみ変更しました）

必要に応じて過去の実績値のみを書き込むようにする、予定と実績値を色分けするなどの対応を行っても良いかと思います。

おわりに

Google App Scriptを使ってGoogleカレンダーのイベント情報から、ラベル毎に月の合計時間を集計し、Googleスプレッドシートに書き込む方法を解説しました。参考になれば幸いです。

最後まで読んでいただきありがとうございます。他の記事はこちらからどうぞ → https://note.com/sat0b3ee/