1. はじめに
この記事では、私が個人的に構築・運用している「Google Calendarとスプレッドシートで作業時間を集計する仕組み」をご紹介します。
🫀 動機は「面倒臭い」と「やってみたい」の気持ちから
エンジニアになってから、業務のタスク管理はBacklogを使うことが多かったのですが、作業が完了するときには、開発に掛かった時間をBacklogのチケットへ「実績時間」として入力しないといけませんでした。
この実績時間の登録のために、いちいち開始時間と終了時間をメモって計算したりするのは面倒だし、いろんなツールを試そうとも思ったが、1分1秒細かく集計したいわけでも無く、なかなかマッチするツールが見つけられなかったんです。
そこで、会議をするときに「Googleカレンダーから予定を入れて招待する」という入力の手軽さと、当時の私の「Googleの機能だけでも連携すれば色々できるじゃん！」というマインドから、「今使えるものGoogleWorkspaceのものだけでやってみよう！」と思い立って作ったのがきっかけです。（GASの勉強にもなるし）
✨ 完成品のイメージ
最終的に、スプレッドシートには以下のようなデータが出力されます。
そしてこのデータを以下のように、スプレッドシートのピボットテーブルで月別、日別に表示しています。
※表示されているデータはサンプル用です
2. 仕組みの概要と設計思想
私が作成した作業ログシステムの全体像と、キーとなる設計思想を紹介します。
🗺️ システム全体図
データは以下のシンプルなフローで流れます。
Google Calendar $\rightarrow$ GAS (Google Apps Script) $\rightarrow$ Google スプレッドシート
- Google Calendar: 作業内容を予定として登録
- GAS: Calendar APIをで情報を取得・整形を行ってスプレッドシートへ書き出し
- Google スプレッドシート: 結果を好きなように集計・可視化する
🎨 設計のポイント：カレンダーの「色」を作業分類に
この仕組みでこだわった点は、カレンダーの「色」を作業の分類キーとして使うことです。予定の色を変えるだけで、自動的に「開発」や「MTG」といった分類に振り分けられます。
私の設定している分類ルールは、GAS内のコードに以下のように定義されています。
| 色 (Color ID) | 分類 | 備考 |
|---|---|---|
| 1 (ラベンダー) | 開発 | メインのコーディング作業など |
| 3 (ブドウ) | private | 私的な用事など |
| 4 (フラミンゴ) | その他業務 | 資料作成、事務作業など |
| 5 (バナナ) | 休み | 有給、半休など |
| "" (デフォルト) | MTG | 色を付けていない予定はMTGとして扱う |
この対応付けは、GASのgetWorkType()関数で行っています。
デフォルトの色は、MTG関連としておくと、他の方からの打ち合わせの招待が来た際に、参加/不参加を決めるだけで、色を変更する必要がなくなるのでオススメです。
🗓️ 予定の入れ方
予定のタイトルには、私の場合はBacklogのキー（PJ_NAME-1）のような、タスクごとのキーとなる文字列を最初に入れています。
開発業務の「設計」をしたのか、「テスト」したのかなどの細かいところまでは集計する必要がなかったので、予定には具体的な内容は書かず、色とキー名だけで登録して、どのタスクにどのくらい時間を使ったのかがわかればOKとしました。
この予定のタイトルの入れ方次第で、後々のスプレッドシートでの集計がしやすくなります。
また、工夫次第では私よりも効果的に集計ができるかもです！
⚙️ 除外ロジック
また、以下の予定は作業ログとして不要なため、除外するようにしています。
-
終日のイベント (
event.isAllDayEvent()) -
不参加のMTG (
event.getGuestByEmail()?.getStatus() == "no")
3. 実装の心臓部：Google Apps Script (GAS)
いよいよ実装の核となるGASコードの紹介です。
🔗 GitHubリポジトリ
コード全文は、こちらのGitHubリポジトリで公開しています。
https://github.com/tomosu20/myAppleScripts/blob/main/importCalendarToSheet.gs
✍️ GASの主要関数解説
重要な関数をいくつか紹介します。
A. メインの処理を実行する
メニューバーに項目を追加し、カレンダーからのデータ抽出を実行できるようにしています。
/**
* メニューバーに項目を追加
*/
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu("スクリプト実行");
menu.addItem("カレンダーの予定を出力する", "exportCalendarSchedule");
menu.addToUi();
}
B. 分類を行う getWorkType()
前述の通り、カレンダーのcolorNum（色番号）を取得し、switch文で分類名に変換します。
function getWorkType(event) {
const colorNum = event.getColor();
// ... (省略)
switch (colorNum) {
case "1": //ラベンダー
return "開発";
case "3": //ブドウ
return "private";
// ... (省略)
default:
return colorNum;
}
}
C. スプレッドシートにデータを出力する exportCalendarSchedule()
年初（date_start）から今日までの予定を取得し、各イベントの開始時刻、終了時刻、分類、そして以下の方法で計算した作業時間を出力配列に格納します。
💡 作業時間計算のロジック
(end - begin) / (1000 * 60 * 60)
これは、イベントの終了時刻と開始時刻の差分（ミリ秒）を、1時間あたりのミリ秒（$1000 \times 60 \times 60$）で割ることで、時間（小数）として算出しています。
function exportCalendarSchedule() {
// ... (省略)
// イベントを取得
const date_start = new Date(now.getFullYear(), 0, 1); // 年始
const events = getCalendarEvents(date_start, now);
events.forEach((event) => {
// ... (除外処理、データ取得の処理)
result.push(
[
// ... (年月, 日付, 分類, タイトル, 開始, 終了)
(end - begin) / (1000 * 60 * 60), //作業時間 (時間:小数)
]
);
});
// シートに書き込む
const range = sheet.getRange(1, 1, result.length, result[0].length);
range.setValues(result);
}
🚨 注意点
コード内の const MAIL_ADDRESS = "##your email address##"; の部分は、ご自身のGoogle Calendarに紐づくメールアドレスに書き換える必要があります。
4. スプレッドシートでの活用（可視化）
データが出力されたら、あとはスプレッドシートの得意分野です。
📊 集計と分析
出力されたデータ列（分類、作業時間など）をもとに、ピボットテーブルの機能を使って、各月毎や日別に作業時間を集計することが出来ます。
また、QUERY関数やSUMIF関数を使って、以下のような集計レポートを作成することもできます。
- 分類別合計工数: どの作業にどれだけの時間を割いたか
- 月別工数推移: 月ごとに作業量がどのように変化しているか
5. まとめと今後の展望
🎉 この仕組みを導入してよかったこと
この仕組みを導入したことで、自身の作業実績の記録と分析が手軽にできるようになったかなと思います。
また、これを作ったことでGoogleカレンダーは常に画面に開きっぱにしているので、打ち合わせの招待の通知とかは爆速で気づけますね。
ただ、めちゃくちゃ几帳面な方だったら1分単位でも記録したいと思うところかもしれないですが、そうなると予定の登録時に時間まで入力しないといけなくて、かなり面倒になってしまいます。
Googleカレンダーは予定を入れるとき、15分単位なら入力が楽なので、私の場合は、15分単位で作業記録を付けています。余ってしまう時間はサボるのではなく、例えば10時10分に作業の目処がついてしまったら、「あと5分、もう一度漏れが無いか確認しよう！」みたいに少しでも業務の精度を上げる活動をしています。
そのおかげか、ミスや確認漏れが減って、作業のメリハリが付いたようにも感じます！
💡 今後の拡張性
ただ、ここはもう少しこうしたい、という点もいくつかあります。
-
getWorkType()の分類ロジックを、コードではなくスプレッドシートの別シートを参照するようにし、柔軟な分類変更を可能にしたい - Looker Studioとかと連携して、グラフ化したい
ただ、今の状態でも特に困ったことはないので、そこまで汎用的にするのは少し腰が重く、なかなか手を付けられてないです笑