ExcelだけでBacklogのAPIから課題一覧を引っこ抜く
ExcelだけでAPIからデータを引き抜くことができるんですって！？PowerQueryを使うらしい！
ということで早速やってみた。
この手順の通りやっていけばBacklogのAPIからデータ抜けるはず。
他のAPIでも応用が利きそうなので、試していきたい。
PowerQueryとは
PowerQueryって何？という方もいるだろうが簡単に説明すると、
Excelで外部（別のシート含）からデータを取り込み、好きな形式でデータを出力できる機能である。
Excelでデータの取り込み・吐き出しというとVlookupやCountifs、ピボットテーブルを利用している人も多いだろうが、これらよりもかなり便利だ。
例えばこんなことができる。
手元にあるデータ①（カラムA・B・C）と、データ②（カラムA・D・E）が存在するとする。
この条件でA・B・C・Dというカラムのデータを作りたいとき、AをキーとしてVlookupを使う人も多いだろう。
ただ、これを定期的に更新する必要がある場合に面倒だ。毎度①②を起動して、更新をかけないといけない。データを並び替えようものなら数式が狂うこともある。
PowerQueryを使うと、一度設定をすれば「更新」をクリックするだけで自動でデータが取り込まれるし、吐き出すときのデータの並び順もある程度自由に並び替えられる。
また、Excelの便利な機能というとVBAを想像する人も多いが、PowerQueryではプログラムを手で書くことなく実装できるのもポイントだ。
このあたりで詳細に解説されているので、興味のある方は参考に見ていただきたい。
https://jsdg.org/blog/view031/
※ただし、本記事で取り上げる方法はある程度クエリを手でいじる必要があるのでアレルギーの方は覚悟したうえで臨んでほしい。
BacklogのAPIキーを発行する
「個人設定」→①「API」を選択し②「登録」をクリック
するとAPIキーが発行される。このAPIキーは後で使うので控えておいてほしい。
クエリを作成する
これ以降は、「課題」のページを開いたときのURLが以下であると仮定して書き進めます。
ご自身で使っているbacklogのURLと照らし合わせながら進めてほしい。
https://spaceid.backlog.com/find/PRJKEY?projectId=12345678
クエリエディターを起動する
Excelを起動し、データタブ→データの取得→Power Query エディターの起動 を選択する
クエリを書く前の準備
パラメーターの管理→新しいパラメーター を選択
以下の通り入力／選択
名前：CONST_URL_PATH
種類：テキスト
現在の値：https://spaceid.backlog.com/api/v2/issues
データの入力をクリックするとテーブルの作成のダイアログが開くので、
列名：parameter
1セル目の値：?projectId[]=12345678
名前：projectId
課題情報を取得するコードを書いていく
一旦ここまでで準備は完了。続いてコードを書いていく。以下の2ステップ。
①課題情報取得用のコード記述
②カスタム関数の作成
読者の皆さま向けにはこの記事のコードをコピペ・一部編集すれば取得できるようにしてある。
新しいソース→その他のソース→空のクエリ を開く
「詳細エディター」をクリックするとコードの入力画面が表示される
詳細エディターの入力画面に入っているものは削除し、以下のコードを張り付ける。
APIキーの欄には冒頭で控えておいたAPIキーをダブルクォーテーション内にまるっと張り付ければOK。
張り付け後、「完了」をクリックしてこの作業はいったん終了。
let
// プロジェクトID
prm_projectId = projectId{0}[parameter],
//APIキー
my_api_key = "【バックログのAPIキーをここにコピペする】"
,
// １．リクエストURL生成(プロジェクト内の課題一覧)
url = CONST_URL_PATH
& prm_projectId
& "&sort=UPDATED"
& "&count=100"
& "&apiKey=" & my_api_key
,
// ２．空テーブル
tbl_blank = Table.FromList({null}, null, {"result_issue"}),
// ３．課題情報を取得
response = get_issues(tbl_blank, 0, url),
// ４．空白行の削除
result = Table.SelectRows(response, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
// ５．データの展開・並べ替え
// ここから先は後で記述される
in
result参考：https://kajimublog.com/powerquery-backlog-api-issues/
ただし、このコードだけでは情報取得はできない。
３で使っているget_issuesというカスタム関数を別で定義する必要があるので、この後作業する。
カスタム関数get_issuesを定義する
先ほどと同様に空のクエリを作成し、詳細エディターを開き、以下のコードをまるっと張り付けて、完了をクリック。
(tbl as table, i as number, url as text) =>
let
// offset
offset = i * 100,
// １．プロジェクト内の課題をリクエスト
response = Json.Document(Web.Contents(url & "&offset=" & Text.From(offset))),
// ２．レスポンス数の確認
response_number = List.Count(response),
// リストからテーブルに変換
list_to_table = Table.FromList(response, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// 列名変更
rename_clm = Table.RenameColumns(list_to_table,{{"Column1", "result_issue"}}),
// ３．縦結合
tbl_comb = Table.Combine({tbl, rename_clm}),
// ４．レスポンス数が100 かつ リクエスト数が6以下 の場合 再帰処理
result = if response_number = 100 and i < 5 then
// 再帰処理(リクエスト間隔1秒)
Function.InvokeAfter(
()=> @get_issues(tbl_comb, i + 1, url),
#duration(0,0,0,1)
)
else
tbl_comb
in
result参考：https://kajimublog.com/powerquery-backlog-api-issues/
このままではエラーが出ているはず。データ取得用のクエリを開き「資格情報の編集」をクリックする。
匿名の画面のまま「接続」をクリック。
こんな感じになったら成功。
あとは普段のPowerQueryと同様にテーブルを展開し、必要でないカラムを削除したり、並び替えたり、フィルタをかけたりすればOKだ。
引き出したデータを成型する
カスタム属性の並び替え
カスタム属性（データ内のカラム名だとcustomFields）は、1課題につき複数存在することがある。
そのため、カスタム属性が3個設定されていれば、1つの課題が3列分出力されてしまう。（以下画像の通り）
このままだと参照データとしては扱いにくい。そこで使うのがPowerQueryの「列のピボット」の機能である。
列のピボットの使い方
以下の手順で操作すればOK。
①見出しにしたい列を選択する（画像ではresult_issue.customFields.name）
②「変換」タブを開く
③「列のピボット」をクリック
④値にしたい列を選択する（画像ではresult_issue.customFields.value）
⑤「集計しない」を選択する
OKをクリックすると見出しと値が割り振られる（画像2枚目）
これで扱いやすいデータ形式に変わった。
あとは「閉じて読み込む」をクリックすれば、Excelのシートにデータが書き出される。
あとは煮るなり焼くなり何なりとご自由に！
同じ悩みを抱いたみなさんの業務が少しでも効率化しますように！
参考文献：
コードを大いにパクらせていただきました！ほかにも役立つExcelのHowToやPython等の記事も充実していますので是非参考に！（参考として掲載する旨を快諾いただき、ありがとうございました！）
ググって列のピボットの機能の存在を初めて知った時にとても参考になったブログです！
この記事が気に入ったらサポートをしてみませんか？