この記事は、PERSOL PROCESS & TECHNOLOGY Advent Calendar 2022 の 11 日目の記事です。

はじめに

Backlog には「実績時間」という機能があり、課題ごとに費やした時間を記録できるようになっています。
この実績時間は、あくまで "課題ごと" でしか記録 / 集計することができません。
(メンバーみんなで 1 つのフィールド内に加算していくようなイメージ)

この実績時間はコメントとして "誰が" "いつ" "どんな変更をしたのか" がすべて記録されます。
しかし、これを個人別に集計して CSV ファイルに出力する機能は実装されていません。
そこで、ちょうど UiPath の学習もしていたので、「勉強がてらに UiPath を使ってコメントの情報を取得してみよう！」と考えました。

Backlog API って？

Backlog 上でできる操作の大部分は、この Backlog API を使えば可能です。
今回はこの中の「課題一覧の取得」「課題コメント数の取得」「課題コメントの取得」という 3 種類のリクエストを使っていきます。

実際に作成してみよう

UiPath で実際に開発をします。

作成環境

UiPath Studio 2022.10.3
Backlog プレミアム (スタンダード以上であれば OK)
Excel for Microsoft 365 MSO (バージョン 2210 ビルド 16.0.15726.20188) 64 ビット

おおまかなフロー構成

以下のような流れで処理を行います。

取得する日付範囲を指定する
指定した日付範囲の課題一覧を取得する
取得した課題一覧内の課題 ID をもとに課題コメント数を取得する
課題のコメントを取得する
Excel ファイルに出力し、集計を行う

事前準備

Backlog 側での準備 - API キーを取得する

Backlog ヘルプセンターに掲載されている手順 (APIの設定) に沿って API キーを取得しておきます。

UiPath 側での準備 - パッケージをインストールする

UiPath のプロジェクト内に、WebAPI を利用するためのパッケージをインストールしておきます。

UiPath.WebAPI.Activities

1. 取得する日付範囲を指定する

フロー図 (画面キャプチャー)

抽出開始日と抽出終了日を [入力ダイアログ] を使ってユーザーに指定させます。
※ バリデーション処理を [文字列の一致をチェック] アクティビティを使って組み込んでいます。

正規表現オプション

"\d{4}-\d{2}-\d{2}"

2. 指定した日付範囲の課題一覧を取得する

フロー図 (画面キャプチャー)

ここでいよいよ Backlog の API を使います。

今回、Backlog への API リクエストを行う部分は別モジュール化しました。
(部品化した)

フロー図 (画面キャプチャー)

引数一覧

名前	方向	引数の型	備考
in_Config	入力	Dictionary	Excel で作った Config ファイルが中に格納。API キーやスペース ID (*.backlog.com の * の部分) が入ってる
in_userId	入力	Int32
in_count	入力	Int32
in_order	入力	String
in_projectId	入力	List
in_issueTypeId	入力	List
in_categoryId	入力	List
in_versionId	入力	List
in_milestoneId	入力	List
in_statusId	入力	List
in_priorityId	入力	List
in_assigneeId	入力	List
in_createdUserId	入力	List
in_resolutionId	入力	List
in_parentChild	入力	Int32
in_attachment	入力	String
in_sharedFile	入力	String
in_sort	入力	String
in_createdSince	入力	String
in_createdUntil	入力	String
in_updatedSince	入力	String
in_updatedUntil	入力	String
in_startDateSince	入力	String
in_startDateUntil	入力	String
in_dueDateSince	入力	String
in_dueDateUntil	入力	String
in_id	入力	List
in_parentIssueId	入力	List
in_keyword	入力	String
in_offset	入力	Int32
out_issueData	出力	JArray

今回 API リクエストに使ったパラメーターは [offset] と [count]、[updatedSince] [updatedUntil] です。
この中でも [offset] という数値が肝になってきます。

offset という謎のパラメーター

((引用 : 課題一覧の取得) でも offset の説明がない)

この [課題一覧の取得] は 100 件単位でしか取得を行うことができず、ここで offset が効いてきます。
offset は〇件目の項目から取得を開始するという設定値であり、"100" という数値をパラメーターとして設定すれば、"101" 件目から抽出が行われるというものです。

今回は、抽出したデータが 0 件となるまで繰り返し 100 件ずつ取得を継続するという仕組みにしています。

抽出したデータの扱い

データは JSON 形式で吐き出されるようなのですが、この JSON を扱う方法がイマイチわからず…。
※ JArray だとか、JObject だとか…
いろいろと模索をして、私は次のように理解しました。

受け取るレスポンス例に合わせて

[ // JArray の内容
    {
        // JObject の内容
    }
]

JArray が無事取り出せたら、JArray 内の JObject ごとに繰り返し処理を組み、リスト内に課題の ID を格納しました。

3. 取得した課題一覧内の課題 ID をもとに課題コメント数を取得する

後述する [4. 課題のコメントを取得する] [5. Excel ファイルに出力し、集計を行う] も 1 つの xaml ファイルに組み込んでしまったのでまずはフロー図から。
※ かなり繰り返し処理をネストさせることになってしまったのは少し反省点…。

フロー図 (画面キャプチャー)

ここでコメント数を取得するのは、実は意図があってのことなのです。
後続の処理である [4. 課題のコメントを取得する] には先ほどの [offset] というパラメーターが存在しないのです。
(100 件ずつしか取得できないのに)

ではどうしたのか、については後述の [4. 課題のコメントを取得する] で説明しますね。

ひとまず Backlog からコメントの件数を取得する処理の xaml ファイルから。

フロー図 (画面キャプチャー)

引数一覧

名前	方向	引数の型	備考
in_Config	入力	Dictionary	Excel で作った Config ファイルが中に格納。API キーやスペース ID (*.backlog.com の * の部分) が入ってる
in_issueIdOrKey	入力	String
out_issueData	出力	Jobject

今回 API リクエストに使ったパラメーターは [issueIdOrKey] です。

4. 課題のコメントを取得する

さて、ここから怒涛のネスト地獄。
JArray の中の JObject の中の中の中の・・・・・的な具合。

まずは [課題コメントの取得] に使った xaml ファイルから。

フロー図 (画面キャプチャー)

引数一覧

名前	方向	引数の型	備考
in_Config	入力	Dictionary	Excel で作った Config ファイルが中に格納。API キーやスペース ID (*.backlog.com の * の部分) が入ってる
in_minId	入力	Int32
in_maxId	入力	Int32
in_count	入力	Int32
in_order	入力	String
in_issueIdOrKey	入力	String
out_issueData	出力	JArray

今回 API リクエストに使ったパラメーターは [maxId] [count] [issueIdOrKey] です。

課題コメント件数の取得 → 課題コメントの取得の大まかな流れ

格納するデータテーブルの初期化・列の追加
課題 ID ごとの処理
1. 課題 ID 内のコメントの件数を取得しておく
2. (項番 0. 処理終了後) の件数が 1 件以上残っていれば最後に処理したコメント ID を [maxId] のパラメーターに設定する
3. 課題コメントを取得 (100 件ごと)
4. コメントごとの処理
  1. データ行に課題 ID を代入
  2. jsonProperty ごとの処理
    1. jsonProperty の名前が "changeLog" であればその JSON 配列をデシリアライズ
      1. jsonObject ごとの処理
        
        jsonObject の field の値が "actualHours" であれば
        
        データ行に実績時間の更新前の値、更新後の値、その差を代入
    2. jsonProperty の名前が "createdUser" であればその JSON をデシリアライズし、データ行にコメントの登録者 (createdUser) を代入
  3. データ行にコメントの登録日時を代入
  4. あらかじめ設定した日付範囲内であれば、データテーブルに格納
  5. [項番 2.1.] で取得したコメントの件数から 1 件引き算をする
5. 残りのコメントが 1 件以上あれば、項番 2.2. に戻る

・・・という具合なのである。
(もしもっといいやり方があれば教えてほしいです)

maxId って？

先ほど 100 件以上のデータを取得する際には offset を使った、が。
今回の [課題コメントの取得] にはそんなパラメーターはない。

そこで今回 [maxId] というパラメーターを使った。
コメントの ID は数値になっており、既定ではコメント ID の降順で出力が行われる。
そこで 100 件目のコメント ID を [maxId] に格納して、もう 1 度リクエストを行えば、101 件目以降の 100 件が取得されるってわけ。

なるほど！！(自分で)

5. Excel ファイルに出力し、集計を行う

ごめんなさい。
ここまで UiPath で頑張りましたが、ピボットテーブル絡みの機能は UiPath StudioX にしか実装されていないようで。

VBA で実装しちゃいました。Excel ファイル内に。
一応コードをば。

pivotTableOperations()

Sub pivotTableOperations()

    Sheets("日次_個人別").Select
    ActiveWorkbook.PivotCaches.Create(xlDatabase, "元データ").CreatePivotTable "日次_個人別!R1C1"
        
    With ActiveSheet.PivotTables(1)
        .PivotFields("name").Orientation = xlRowField
        .PivotFields("updated").Orientation = xlColumnField
        .PivotFields("workload").Orientation = xlDataField
    End With
    
    Range("B2").Select
    Selection.Group Start:=True, End:=True, By:=1, Periods:=Array(False, False, False, True, False, False, False)

    Sheets("プロジェクト_個人別").Select
    ActiveWorkbook.Worksheets("日次_個人別").PivotTables(1).PivotCache.CreatePivotTable "プロジェクト_個人別!R1C1"
    
    With ActiveSheet.PivotTables(1)
        .PivotFields("name").Orientation = xlRowField
        .PivotFields("projectId").Orientation = xlColumnField
        .PivotFields("workload").Orientation = xlDataField
    End With
    
    Sheets("日次_プロジェクト別").Select
    ActiveWorkbook.Worksheets("日次_個人別").PivotTables(1).PivotCache.CreatePivotTable "日次_プロジェクト別!R1C1"
    
    With ActiveSheet.PivotTables(1)
        .PivotFields("projectId").Orientation = xlRowField
        .PivotFields("updated").Orientation = xlColumnField
        .PivotFields("workload").Orientation = xlDataField
    End With
    
    Range("B2").Select
    Selection.Group Start:=True, End:=True, By:=1, Periods:=Array(False, False, False, True, False, False, False)

End Sub

出力結果

個人別で日付ごとの工数を出すことができました。

Backlog API の制限

APIの種類	有料プラン	フリープラン
読み込み	600 回/分以内	60 回/分以内
更新	150 回/分以内	15 回/分以内
検索	150 回/分以内	15 回/分以内
アイコン取得	60 回/分以内	6 回/分以内

Backlog API には一定のレート制限があり、これに伴うエラーハンドリングを行わなければいけません。

今回使ったリクエストでいえば (有料プランの場合)、「課題一覧の取得」は「検索」にあたり、150 回/分
「課題コメント数の取得」「課題コメントの取得」は「読み込み」にあたり、600 回/分という制限になります。

具体的には、レート制限に引っかかった際には "429" というステータスコードが返却されるため、"429" の際には 60 秒待つ挙動を追加しています。

繰り返し (後判定) で "429" が返却されたときは再度取得を試みるようにして、

繰り返した頭には、"429" の時は 60 秒待機させるような処理を組み込めば出来上がり。

まとめ

これで個人別での工数管理が Backlog と UiPath で完結できます。
もちろん、メンバーの入力の精度に依存してしまう部分はあるので、精緻なデータにならないときもありますが、簡単に取り出せるのでこれはこれでアリだな、といった具合です。

なお、あくまで Backlog の標準機能ではないため、ご利用の際は自己責任でお願いします。

あと、はじめて Qiita に投稿してみましたが、これを機にもっと自分の知識整理のためにも投稿してみようかな、などと思ったり。
ここまでお読みいただきありがとうございました。

明日の PERSOL PROCESS & TECHNOLOGY Advent Calendar 2022 もお楽しみに！