序文
- VBAに疎い
- そもそもプログラム書くのしんどい
- APEXを遊んでたら[VBA]というチームにchampionを奪われた1
など、VBAが苦手な諸氏2にむけ、Excel2013以降で使えるようになった超強力なETLツール3でプリザンターのAPIからテーブルデータを読み、それをExcelに反映する方法を伝える。
コードを差し込む場面はAPIリクエストを投げる記述のみで、コピペと数行入換だけでできるように説明する。データ整理や整形などは全てグラフィカルに操作できる為、心理的ハードルはかなり低い（ゴリゴリにコードを書くこともできる）。
なおVBAと大きく違うのは、VBAではExcelからPleasanter側に対し作成・読み取り・更新・削除（CRUD）がすべて出来る一方で、Power Queryは読み取りしかできない（CRUDのうちRのみ可能）。その代わり、読み取った後のデータ整形は、Excelを扱うかの如くカンタンだ。
この記事はプリザンター(Pleasanter) Advent Calendar 2021の参加記事だ。
なお、この内容を書くにあたり、VBA-Pleasanter連携例（特にAPI周りの処理）として、こちらの記事を参考にさせて頂いた。ありがとうございました。
PleasanterとExcelを接続してみた
PleasanterとExcelを接続してみた－応用編
基礎編: ExcelにPleasanterのテーブルを（Power Query経由で）読み込む
Excelのデータタブから、画像通りに空のクエリを選択。
別画面でPower Queryが立ち上がる。ホームの詳細エディターをタップすると、コード入力画面になる。
ここのに以下のコードを差し込み、「URL」と「api」の変数を適宜なおし、完了を押す。
初めて見る記法が多いと思うが、後で説明する。
let
url = "https://プリザンターのドメイン/api/items/読みたいテーブルのID/get",
headers = [#"Content-Type" = "application/json"],
api = "pleasanter_API_keyXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
ct = Json.FromValue([ApiKey = api]),
res = Web.Contents(url,[
Headers = headers,
Content = ct
]),
res_json = Json.Document(res),
Column1 = res_json,
Response = Column1[Response],
Data = Response[Data]
in
Data
黄色い帯警告が出たら、「資格情報の編集」を押す。Webコンテンツへのアクセス、という項目を探し、この画面が出たら、「匿名」を開いて接続を押す。
※ Web APIは開かないこと。これでプリザンターのAPIは開けない。
しばらく待ち、「Record」が大量に表示されれば成功だ。中身を見る場合は各リストをダブルクリックし、戻る場合は数式エディタに= Dataと書けば良い。
APIから来たデータを加工する。リボンメニュー変換から「テーブルへの変換」を押す。
変換後の操作方法については、こちらの記事が詳しい。
Power Query: REST APIでListから取得したRecordをTableに変換するには
変換を終えると、テーブルに各データがExcelのように表示されるようになる（Excelではないので機能は微妙に違う）。ここからデータ整形をしていこう。列に名前をつけたり、不要な列を消したり、列の型を変えたり、他の列と計算したり……
ちなみに以下画像例の「Column1.UpdateTime」は、右クリックからの型の変換で「日付/時刻」にすると良い。T以下を正規表現で削る必要はないのだ。
それらを終えたら、最後にリボンメニューのホーム「閉じて読み込む」を押す。
無事にExcelへデータが反映された！ ここから更に関数やVBAで加工しても良いし、グラフを差し込むなどでも良いだろう。もちろん、ここまでの流れで、Pleasanter側のデータに変更が入ることは一切ない。データ集計機能だけが分離できて安全！
なお、テーブルに更新が入ったらデータタブのクエリと接続から「すべて更新」を押す。更新分もExcelに反映されるはずだ。手順書を書いて他者に渡すときもカンタンに説明できる。
応用編1: クエリを複数読み込んで加工
なお、他のマスタデータの引当（Excelやプリザンターで言うところのLOOKUP、SQLでいうところのJOIN）なども、Power Queryでカンタンに完結できる。
例えば上記例だとユーザIDが数値の為、これもPleasanterのユーザ一覧からしたくなる。これを実現するには、クエリ1とは別に、更に別のクエリを作り、それをマスタデータにして引当をする形がよい。
Power Queryの画面からどこか右クリック、「空のクエリ」で新しいクエリを作成する。
クエリ2が作成できたら先程と同じように詳細エディタを開き、以下コードを貼って適宜修正し、完了を押す。
let
url = "https://プリザンターのドメイン/api/users/get",
headers = [#"Content-Type" = "application/json"],
api = "pleasanter_API_keyXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
ct = Json.FromValue([ApiKey = api]),
res = Web.Contents(url,[
Headers = headers,
Content = ct
]),
res_json = Json.Document(res),
Column1 = res_json,
Response = Column1[Response],
Data = Response[Data]
in
Data
先程の要領で操作していく。クエリ2で必要なデータは画像の通り。
これでクエリ2がユーザマスタになった。クエリ1へ戻る。
リボンメニューのホームから「クエリのマージ」を開く。
マージ下画面をクエリ2に指定し、Owner, UserIdと照合データを選択し、OKを押す。
来た。展開するとクエリ1にユーザマスタの内容が表示されている。
このように、クエリを複数を読み込み、Excelを複数のデータセットにしても良いし、結合しても良い。集計内容の幅が大きく広がることだろう。
もちろんPower Queryは、別システムのAPIやSQLデータベース、Azure、AWS各サービス、HTMLのテーブル構造、さらには上司が汗水流して作成したExcel方眼紙の内容までもデータとして取得できる。ETLのE強すぎ。それらと合わせて一つのExcelファイルに、自動的にまとめ上げられるのも、大きな魅力だ。
応用編2: Power Queryなので Power BI でも応用が効く
そしてPower Queryが使えるということは、Microsoftが誇る多機能データ見える化プラットフォームの「Power BI」にも使えるということ。特にMicrosoft Power BI Desktopは無料4でもかなりの機能を使うことができ、特に週次月次レポートなどで活躍する。
このような感じで、かなり柔軟な表がGUIで書ける。プリザンター標準の集計（プリザンターはクロス集計が非常に使いやすい）から外れて何かをする場合は、特に印刷やPDF化前提で使われるものであれば、自前でd3.js5のコードを書かずに、Power BIを利用すると、作業の効率化＆集計スパンの短縮化に貢献するだろう。
レポートならExcelよりも、むしろこっち使ってくれまである。
おまけ: さっきのプログラム言語なに
Power Query M式言語だ。Power Queryのためだけに生まれたスクリプトで、検索するときはM言語とかで調べた方がヒットする。ブレブレである。
言語の使い方は市井のブログよりも、上のMicrosoft大本営のリファレンスが一番分かりやすい、というか膨大な資料があるわけでもないライトな言語なので、割とサクッと読める。
あとはjsonを扱う関数だけ見とけば良い。
https://docs.microsoft.com/ja-jp/powerquery-m/json-document
https://docs.microsoft.com/ja-jp/powerquery-m/json-fromvalue
なお、基礎編がある程度作り終わった段階で、詳細エディターを開いてみると、それまでの操作が全てM式言語に変換されているのがわかる。
もっとPower Queryの操作を知りたい方への参考リンク
以下の順で読むのがオススメ。
ExcelでPowerQueryを使ってデータ収集分析
Excelワークシート操作に相当するPower Query操作のメモ
ExcelでPowerQueryを使ってデータを結合して出力する