【PowerQuery】Backlogの課題一覧情報をAPIから取得してExcelに自動出力!

皆さんは業務にBacklogを利用されてますか?

Backlogで課題を作成し
社内のタスクなどを管理されている方も多いと思います!

ではその課題情報を
どうやって手元に落としてますか?

管理画面に入って、条件を入力して、課題を検索して、CSVで落として…

こんな手間を一瞬で解決してくれるのがAPIです!

むかしVB.NETでAPI取得した記事を上げましたが

【実績】Backlogの情報をAPIで取得しExcelに出力

今回はExcelの標準機能!
PowerQueryを使って実装してみました!

取得したい課題条件をパラメータ化する

自分のAPIキーを発行する

APIに接続するための自分用のカギを発行します!

①「個人設定」→「API」
②「新しいAPIキーを発行」の「メモ」に適当な名前を入力
③「登録されたAPIキー」表の「APIキー」列に発行されます

APIキー発行方法の公式ガイドはコチラ

汎用性と安全性を持たせるために
APIキーは本体外部から読込む作りがいいと思います!

プロジェクトと状態をパラメータ化してみる

APIによる課題一覧を取得する
公式リファレンスはコチラ

上記に従ってパラメータを作っていく!

今回はBacklogの「プロジェクト」と課題の「状態」に
条件を与えて取得してみる!

1. BacklogAPIにリクエストするパスの固定化

①自身のスペースIDを含むURL
 例:https:// [スペースID] .backlog.jp、https:// [スペースID] .backlog.com

②課題一覧の取得URLのパス
 /api/v2/issues

①&②をPowerQueryエディター「パラメータ管理」から固定値として登録!

例えばこんな感じで「CONST_URL_PATH」と命名しておこう!

2. プロジェクトIDをパラメータ化

ID部分はExcelシート上で入力し
PowerQueryがIDを読込む作りにしておく!
※ID読込めればなんでもOK!

例えばプロジェクトIDを「123456789」と入力した場合
?projectId[]=123456789が生成されるクエリを作る!

そしてこのクエリ名を「projectId」としておきます!

3. 状態IDをパラメータ化

プロジェクトIDと同様に
状態IDはExcelシート上で入力する作りにしておく!

状態に関してIDと内容の紐づけは以下の通り!
1:未対応
2:処理中
3:処理済み
4:完了

例えば状態IDを「2」(処理中)と入力した場合
&statusId[]=2が生成されるクエリを作る!

そしてこのクエリ名を「statusId」としておきます!

課題情報を取得させるコードを実装する

それではこれらのパラメータを指定して
APIから課題情報の一覧を取得してみよう!

メイン処理するクエリを実装

1.メイン処理のコード

2.実装のポイント

「1」ではAPIにリクエストするURLを生成してます!
これまで準備したAPIキーやパラメータはここに差し込みます!
あとデフォルトで1回20件の取得になってるので
最大数100件をcount=100として与えてます!

「2」では課題情報を追加していく為のテーブルを作ってます!

「3」ではカスタム関数 get_issues を呼び出しました!
get_issuesは課題情報を取得する関数として作りました!
中身は後述します!

「4」では「2」の段階で1行できる空白行を除外してます!

課題情報を取得するカスタム関数を実装

1.カスタム関数とは

その名の通り自分で作るオリジナルの関数です!

今回は
課題一覧を取得して、それをテーブル形式で返す
そんな関数を作ってみます!

作る場所は
PowerQueryエディターの左側にあるナビゲーションウィンドウ上で
右クリックから空のクエリを作成!
そこにM言語に従って好きなようにコーディングします!

2.カスタム関数 get_issues のコード

3.実装のポイント

このカスタム関数の「引数」は「tbl」「i」「url」(1行目)
tbl はメイン処理で作った空のテーブルを与えます!
i は課題一覧取得のリクエスト回数!初回は0を与えます!
url はメイン処理で作ったリクエストURLを与えます!

「1」では課題一覧情報をAPIから取得してます!
最初の100件だけ取得しています。
改めて課題一覧を取得する公式リファレンスはコチラ。

「2」では「1」で取得した件数を格納してます!

「3」では取得した課題情報を空テーブルに結合していってます!

「4」では100件以降の課題情報を取得する処理です!
「2」の件数が100であれば
101件目が存在する可能性があるので
100件目以降を順次取得していきます。
要はこの部分は繰り返し処理となります。

4.PowerQueryの繰り返し処理

カスタム関数の「4」をもう少し解説する!

PowerQueryの繰り返し処理として2つ発見!
①ループ処理

②再帰処理

今回私は②の再帰処理を取り入れました!

要は @自身の関数名 と書く事で
自分自身を呼び出せるという事です!

リクエストの間隔を1秒空けて
引数 i をカウントアップさせて再帰処理を行ってます!

ちなみに if に i < 5 と入れているのは
有料プランの読み込み制限が 600回/分以内 だったので
一度に取得する数が600回を超えない為です!

600件以上ある場合は
1分間のインターバル入れてもいいかもですね!

この辺りも自分にあった規約を読んでおきましょう!

どこか参考になる部分あれば幸いです!

かじむー

皆さんも無駄な時間はカットして業務効率化していきましょう!