Backlogの工数（予定時間・実績時間）をGoogleスプレッドシートで自動的に集計する方法

2024/09/11追記：スプレッドシートをアップデートして、よりわかりやすくしました。

クライアント様や社内のチームでタスクを共有するためにBacklogを利用している人は多いのではないでしょうか。
私もBacklogを使用しており大変良いツールだと思いますが、少し惜しいと感じる点があります。

それは工数（予定時間・実績時間）の集計ができない点です。

各課題には

• 予想される工数を記入する「予定時間」欄
• 実際の工数を記入する「実績時間」欄

がありますが、その集計をBacklog上で確認することはできません。
近い機能としてバーンダウンチャートがありますが、この機能は目的が異なるので集計を確認することには適していません。

工数を集計するには、課題一覧をCSVなどで書き出してExcelなどの表計算ソフトで集計するか、課題一覧ページを表示して暗算や手計算するしかありません。

しかし、どちらの方法も頻繁に行うには面倒です。

そこで本記事ではBacklogの工数をGoogleスプレッドシートで自動的に集計する方法をご紹介します。

なお、本記事では文脈によってBacklogの「課題」といったり「タスク」といったりしますが意味は同じです。

Backlogの工数をGoogleスプレッドシートで自動的に集計した結果

Backlogからデータ取得をするApps Scriptと取得したBacklogのデータから工数を集計する関数を組み合わせることで、下記のような表とグラフで工数を集計することができます。

作成したスプレッドシートのポイント

• Backlogからのデータ取得頻度は任意に設定可能（例：1分ごと、1日ごと）
• Backlogからのデータ取得はプロジェクト単位（複数のプロジェクトを横断しての集計は不可）
• 集計表にはピボットテーブルを使用していないため、表を自由に加工しやすい

スプレッドシートの実装方法

初めに、スプレッドシートを実装する上での前提をお話しします。
次にスプレッドシートの実装手順を説明します。
そして最後にApps Scriptについて少し補足します。

【前提】Backlogの使用方法について

私は以下のようにBacklogの各項目を使用しています。

• カテゴリー：工数を負担する会社やチームを設定（例：A社の作業、チームBの作業）
• マイルストーン：工数を割り当てる年月を設定（例：2024年3月分の工数としたい→”202403″と設定）
• 予定時間：タスクを完了するために予想される工数（hours）を設定
• 実績時間：タスクを完了するために実際にかかった工数（hours）を設定

Backlogではカテゴリーとマイルストーン（と発生バージョン）を複数選択することもできますが、私は単一選択のみで使用しています。
そのため今回は課題1つにつき、カテゴリーとマイルストーン（と発生バージョン）はそれぞれ1つのみ設定されていることを前提にスプレッドシートを作成してあります。（2つ以上設定している場合、その内の1つが取得されます）

スプレッドシートの実装手順

1. こちらのスプレッドシートをご自分のGoogleドライブにコピーしてください。
2. 「Setting」シートの黄色セルに値を入力してください。
   
3. メニューバー　＞　拡張機能　＞　Apps Script　を開いてください。
   
4. “dump_backlog_issues.gs”が開かれた状態になるかと思うので、メニューバーにある「実行」ボタンを押してください。
   権限の許可が求められた場合は承認してください。
   「Issues」シートに課題一覧が表示されていればBacklogからのデータ取得は成功です。
   
5. データ取得が成功したら定期的にデータを更新するように設定します。
   左のサイドメニューから「トリガー」を開き、右下の「＋トリガーを追加」ボタンを押してください。
   
6. 設定画面が出てきますので、赤枠部を任意の設定にし、右下の「保存」ボタンを押してください。
   下記は5分おきにデータを更新する場合の設定例です。
   
7. 「工数集計」シートの以下の項目をご自分の状況により調整してください。
   
   • 規定工数：セルB1
     ここに毎月割り当てる規定工数を入力してください。
     例えば、毎月100時間作業することになっていれば「100」と入力してください。
   • カテゴリーの名称：セルE2:F3, H2:I3
     Backlogで使用しているカテゴリー名を設定してください。
     2行目は単なる列名なのでデフォルトのままでも問題ありませんが、わかりやすい列名にすることをおすすめします。
     3行目は関数の中にある「カテゴリー1」「カテゴリー2」という文字列を実際のカテゴリー名に変更してください。セルE3を例にすると、変更するのは以下のオレンジ色の部分です。
     =ARRAYFORMULA(IF($A$3:$A<>"",SUMIF(Issues!$I$2:$I&Issues!$F$2:$F&Issues!$R$2:$R, $A$3:$A&"カテゴリー1"&"no", Issues!$N$2:$N),""))
     例えば、カテゴリーとして「A社」と「B社」があれば関数の中にある「カテゴリー1」を「A社」、「カテゴリー2」を「B社」に書き換えてください。
     カテゴリーが3つ以上ある場合は列を適宜追加してください。ここで設定したカテゴリー名で課題がフィルタリングされ、当該の列で集計されます。

【補足】Apps Scriptについて

Backlogからデータ取得をするApps Scriptはこちらの記事を参考に、少し改造させていただきました。
Backlogから取得する項目は以下です。

Backlogの項目名	「Issues」シートの項目名
種別	issueType.name
キー	issueKey
件名	summary
担当者	assignee.name
状態	status.name
カテゴリー	category.0.name
優先度	priority.name
発生バージョン	version.0.name
マイルストーン	milestone.0.name
登録日	created
更新日	updated
開始日	startDate
期限日	dueDate
予定時間	estimatedHours
実績時間	actualHours
登録者	createdUser.name
（課題ページのURL）	URL
（状態が完了か否かの判定）	is_status_done

最後の2つはBacklogの課題一覧にはない項目ですが、あると便利なのでApps Scriptで取得するようにしています。

各シートの説明

ここでは各シートで何をしているのか簡単に説明します。

「工数集計」シート

このスプレッドシートのメインとなるシートです。「Issues」シートを参照して、マイルストーン × カテゴリー × 状態ごとに予定工数と実績工数を集計します。

規定工数は、毎月当該のプロジェクトに割り当てる工数です。

上限工数は、当月の規定工数＋前月の余剰工数で計算され、当月に稼働できる工数の上限を表しています。私のプロジェクトの場合、当月に使い切れなかった工数orオーバーしてしまった工数は、翌月に繰り越す運用をしているため、この列を設けてあります。

余剰工数は、上限工数 ー（未完了の予定工数合計＋完了の実績工数合計）で計算され、当月にあとどれくらい工数が余っているかを表しています。負の値になる場合、当月に稼働できる工数をオーバーしていることになります。

「工数集計グラフ」シート

「工数集計」シートを可視化したグラフです。「工数集計」シートに列を追加した場合は、参照するデータ範囲を変更する必要があるかもしれません。

「今月のタスク一覧」「先月のタスク一覧」シート

読んで字のごとくです。セルC1で今月・先月の年月を計算し、その年月とマイルストーンが一致するタスクを「Issues」シートから抽出します。マイルストーンの命名規則が「YYYYMM」ではない場合、セルC1の関数を適宜変更してください。

工数集計上は特に必要のないシートですが、工数集計シートを見た際に「あれ？今月・先月はなんで工数こんなになってるの？」を確認するのにあったら便利なので設けてあります。

「Issues」シート

Backlogから取得した生データです。独自の集計シートを作る場合は、このシートを参照してください。

「Issues」シートはデータを更新するたびに初期化されます。
また「工数集計」「今月のタスク一覧」「先月のタスク一覧」シートで「Issues」シートを参照しているため、「Issues」シートはユーザー側で編集しないことを推奨します。

「Setting」シート

Backlogからデータを取得するために、初期設定する必要があるパラメータをまとめたシートです。

最後に

「工数集計」シートと似たようなことはピボットテーブルを使うことでも実現できます。
手軽に集計したい場合はピボットテーブルのほうが簡単です。

しかし、ピボットテーブルでは表内に任意の列を追加するなどの2次加工がしにくいです。
そのような場合は、今回のように自分で疑似ピボットテーブルを作成すると良いと思います。