自動化厨のプログラミングメモブログ │ CODE:LIFE

Python/VBA/GAS/JavaScript/Raspberry Piなどで色んなことを自動化

Googleカレンダーの予定をスプレッドシートに書き出して「何に何分使ったのか」を集計できるGASツールを公開した

GAS ツール

動作イメージはこんなかんじ。

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

  • 複数のGoogleカレンダーの予定を、期間とキーワードを指定してスプレッドシートに一括出力する
  • 件名に含まれるキーワードで予定を自動カテゴライズする
  • 「何に何分使ったか」を集計するベースにする

コードを書かずに、スプレッドシートをコピーして使える。リポジトリはMITで公開したので、フォークも改変も歓迎。

こんな用途に使える

  • 個人の工数可視化（週次・月次で「何にどれだけ時間を使ったか」を集計）
  • 会議時間の分析（会議カテゴリだけ抽出して、週あたりの会議時間を追う）
  • クライアント別集計（フリーランスや複数案件の時間配分）
  • 調整時間が多い日の特定と次月ブロックへの反映
  • カレンダー予定のバックアップ / アーカイブ
  • Looker Studio やピボットに流す前段のデータソース

いずれもこのスプレッドシートGASツールだけで完結する。

作った動機

「最近何に時間使ったんだろう」をカレンダーでひとつずつ確認するのは手間だった。

選択肢はいくつかあった。

  • ICSエクスポート → CSVじゃないし、カテゴリ集計はやりにくい
  • 市販の工数トラッキングSaaS → 設定とアカウント作成が重い。月額もかかる
  • QiitaやZennの解説記事を参考にGASを書く → 毎回同じことを書いてる

三つ目は繰り返したくなかったので、ツールとしてまとめて配布することにした。

使い方

1. スプレッドシートをコピー

  1. 配布スプレッドシートを開く
  2. 「ファイル」→「コピーを作成」

2. GASメニューから実行 ※初回のみ認証

  1. 「GAS」→「イベントデータクリア」などを実行
  2. 初回実行時に認証画面が出る。要求スコープは Calendarの読み取り と Spreadsheetの書き込み のみ。自分がコピーしたGASであることを確認してから許可する(「詳細」→「安全でないページに移動」)
  3. 次回以降は認証ステップなしで実行できるようになる

3. 設定を埋める

設定シートに以下を入れる。

  • カレンダーID（メインならGmailアドレス、サブなら xxxx@group.calendar.google.com）
  • 取得期間（開始日・終了日）
  • キーワード → カテゴリのマッピング

キーワードは「マッチ文字列 | カテゴリ名」で並べる形式。例えば「定例 | 会議」「実装 | 実装」「調査 | 調査」「問い合わせ | 対応」のように設定する。件名にマッチ文字列が含まれると、対応するカテゴリが割り当てられる。

4. イベント取得

「GAS」メニューから「イベント取得」を押すと、指定期間の予定がシートに書き出される。

4つのメニュー

メニュー やること 使いどころ
イベント取得 期間を指定して取得 通常運用。週次や月次で実行
イベント全件再取得 設定期間を全件取り直し 期間を延ばしたときや、データが壊れたとき
カテゴリー再判定 既存行にカテゴリを当て直し キーワード規則だけ変えたとき。APIコール不要でクォータ節約
イベントデータクリア シートの中身を空に やり直したいとき

「カテゴリー再判定」を分けたのが個人的に便利だった。キーワードのチューニングというのはあとからじりじりやることになるので、毎回APIを叩いて取り直さずに済むのは便利。

既存の選択肢との比較

自分が調べた範囲だと、Googleカレンダー→スプレッドシートの選択肢はだいたい以下に分かれる。

種別 例 コスト コード不要 件名カテゴライズ
解説記事のコード断片 Qiita / Zenn / 個人blog群 無料 ✕（自分で組む） ✕または△
有料テンプレ いろいろ 500〜980円 ○ △
SaaS / ノーコード Yoom / TimeNavi / Jibble / Clockify 月額 ○ ○（外部送信前提）
Google標準機能 ICSエクスポート 無料 ○ ✕
本ツール get-google-calendar-events 無料 (MIT) ○ ○

「コードを書かずに、外部にデータを出さずに、件名単位でカテゴライズする」という組み合わせのテンプレはニッチだったため、そのまま動く状態で配布した。情シス観点ではSaaSにカレンダー情報を渡したくないケースがあるため、スプレッドシート内で完結する点に価値がある。

実装のメモ

中身はGASの定番API。

const calendar = CalendarApp.getCalendarById(calendarId);
const events = calendar.getEvents(startDate, endDate);

ごく普通。実装上ちょっと手を入れたのは以下ぐらい。

  • キーワードマッチングは上から順に見て、最初にヒットしたものを採用（優先度を行順で表現。最長一致ではなく先着一致）
  • まとめてappendRowを叩くと遅いので、2次元配列を準備してsetValuesで一撃
  • イベントのIDをシートに持たせておき、重複チェックに使う

大した技巧はない。不便があれば順次改善する。

ピボットテーブルで集計する

内蔵のカテゴリ列をキーにピボットテーブルを組むと、カテゴリ×週のマトリクスがそのまま出てくる。データがたまってきたらLooker Studioに持っていってダッシュボード化するのもあり。このツールは入り口として使える。

まとめ

  • Googleカレンダー→スプレッドシートのテンプレをMITで配布した
  • 件名キーワードでカテゴライズするのが主な特色
  • まず小さく始めて、足りないところは自分で書き足したい人向け

Starとフィードバック歓迎。