はじめに

こちらのツイートでも軽く紹介したのですが、普段業務をしている中で大ざっぱにでも何にどれくらい時間を使えていたのか振り返りたいなという思いがありました。
そのために、しばらく Google カレンダーの予定をカテゴリごとに何色かに分けて登録するようにしていたのですが、いかんせん登録しただけで振り返りがおざなりになっていました。

そこで、登録したカレンダーのカテゴリ毎の所要時間を集計し、かつ日次や週次で Slack に投稿してくれる Bot を Google Apps Script（以下 GAS）で作りました。

この Bot でできること

大きく 2 つです。

1. 日次の投稿：その日のカテゴリ別所要時間と、内訳を Slack に投稿する

2. 週次の投稿：1 週間分のカテゴリ別所要時間をグラフにしたものを Slack に投稿する

インストール方法・使い方

以下のリポジトリの README に記載しています。

基本的にはテンプレートとなるスプレッドシートをコピーしてもらえれば、コードのデプロイなどは行わずに誰でも利用できます。

技術的なこと

この Bot を作るにあたって技術的に考えたことや難しかったことを紹介します。

Google カレンダーの予定をカテゴリごとに集計する

この Bot のメイン機能とも呼べる部分です。
まず、Google カレンダーの予定を取得する処理については特にハードルはありませんでした。GAS なので Google の他サービスとの連携は簡単に行えます。
今回で言えば、 CalendarApp.getEventsForDay(targetDate) を呼ぶだけで targetDate の予定を取得できます。
あとは辞退した予定や終日予定などを除外してやれば OK です。

function fetchGoogleEvents(
  targetDate: Date
): GoogleAppsScript.Calendar.CalendarEvent[] {
  const events = CalendarApp.getEventsForDay(targetDate);

  // filter events so that:
  // - include only accepted events
  // - exclude allday events
  return events.filter(
    (event) =>
      !event.isAllDayEvent() &&
      (event.getMyStatus() === CalendarApp.GuestStatus.OWNER ||
        event.getMyStatus() === CalendarApp.GuestStatus.YES)
  );
}

そして、取得した予定の所要時間をカテゴリごとに集計する処理についてですが、
当初はその処理をスクリプトで実装していたものの、途中から
「あ、これスプレッドシートのピボットテーブルでやれば実装しなくて済むのでは？」
ということに気づき、スクリプトでは取得した予定にカテゴリ情報をくっつけてシートに保存するだけに留めました。

こういう形式で予定のカテゴリ・タイトル・開始終了時刻を保存する

ピボットテーブルではこのように集計される

またその場合、他の人に使ってもらうことを考えるとピボットテーブルの作成などは自動化できると初期セットアップの手間としては望ましいです。
が、そこについても「テンプレートとなるスプレッドシートを用意しておいて、コピーしてもらえばいいか」という発想になり、特にスクリプトでの実装は不要としました。

グラフを Slack に投稿する

Slack にテキストメッセージを投稿するだけであれば、 Incoming Webhooks を使えば実現できます。
Sending messages using Incoming Webhooks | Slack

が、これには画像を投稿する機能はありません。画像を投稿するには files.upload API を使います。
files.upload method | Slack

Incoming Webhooks は Webhook URL があれば投稿できますが、 files.upload は token が必要です。結果として両方とも入力してもらう形になってしまった。。。

グラフのデータ範囲を GAS で更新する

作っていて一番面倒だったのが、グラフのデータ範囲の更新処理です。
週次で投稿するグラフに含めるのはその週の月〜金までの 5 日分のデータだけとしていたため、翌週には次の 5 日分がデータ範囲となるよう定期的に更新する必要がありました。
かつ、テーブルのヘッダー行は見出しとして常にデータ範囲に含める必要があります。

GAS でこのようなグラフ操作をするためには、 EmbeddedChartBuilder クラスの addRange(range) および clearRanges() を使用します。
サンプルコードは以下です。

const chart = sheet.getCharts()[0];
sheet.updateChart(
  chart
    .modify()
    .clearRanges()
    .addRange(sheet.getRange(2, 1, 1, numColumns)) // Always include header row
    .addRange(
      sheet.getRange(sheet.getLastRow() + 1, 1, CHART_RANGE_DAYS, numColumns)
    )
    .build()
);

addRange(range) は文字通りデータ範囲を「追加」するため、先に clearRanges() で現在のデータ範囲をクリアします。
また addRange(range) は複数回適用できるので、これによってヘッダー行と実際のデータを含む行を順番に追加しています。

もう 1 点、めちゃくちゃ細かいところですが、ピボットテーブルには「総計」列を表示するオプションがあります。

この列が表示されていたとしても、グラフには含めたくありません。
そのため、 PivotGroup クラスの totalsAreShown メソッドを使って総計列を表示しているかどうか判定し、データ範囲の列方向を調整しています。

const lastColumn = sheet.getLastColumn();
console.log("lastColumn", lastColumn);

const pivotTables = sheet.getPivotTables();
const pivotTable = pivotTables[0];
const pivotGroups = pivotTable.getColumnGroups();
const totalsAreShown = pivotGroups[0].totalsAreShown();

const numColumns = totalsAreShown ? lastColumn - 1 : lastColumn;

参考: スプレッドシートのピボットテーブルで「総計を表示」しているかどうかを GAS で判定する - dackdive's blog

入力用のフォームダイアログ

Slack の Webhook URL や Bot Token などをコードに直接埋め込みたくなかったため、フォームを用意してスクリプトプロパティに保存する仕組みにしています。

こちらは別途記事にしています。

モジュール分割

GAS の開発およびデプロイには clasp という Google 公式 CLI を使用しました。
clasp は標準で TypeScript をサポートしているなど便利な反面、 ES Modules をサポートしていないという問題があります。

https://github.com/google/clasp/blob/master/docs/typescript.md#modules-exports-and-imports

Currently, Google Apps Script does not support ES modules. Hence the typical export/import pattern cannot be used and the example below will fail:

また、この問題に対しては以下 3 つのワークアラウンドが紹介されています。

declare const exports: を使う（未検証。よくわからん）
namespace を使う
Rollup などのモジュールバンドラを使う

いずれかの方法でファイルを分割してコードを整理したいなと思ったのですが、やりたいことに対して複雑すぎる点や、Web 上のスクリプトエディタを使ったデバッグがやりにくくなる点を鑑み、結局 main.ts に全部ごちゃっと書いたままになっています。イケてない。。。

おわりに

というわけでまだ少しやりたいことはあるのですが、一旦形になったのでソースコード含め公開しました。

使ってみていただければ幸いです。

Discussion