VBAは嫌なのでPleasanter APIからPower QueryでExcelに割振りたいと思います。（＋Power BI連携も）

## 序文

- 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](https://qiita.com/advent-calendar/2021/pleasanter)の参加記事だ。

なお、この内容を書くにあたり、VBA-Pleasanter連携例（特にAPI周りの処理）として、こちらの記事を参考にさせて頂いた。ありがとうございました。
[PleasanterとExcelを接続してみた](https://qiita.com/m-isik/items/7afb0907a1dc60225453)
[PleasanterとExcelを接続してみた－応用編](https://qiita.com/m-isik/items/0abfd51a22587bed2e47)

### 基礎編: ExcelにPleasanterのテーブルを（Power Query経由で）読み込む

Excelのデータタブから、画像通りに空のクエリを選択。
![Clipboard00.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/957d0b56-27cc-519b-64c0-8cfa6b5d3143.jpeg)

別画面でPower Queryが立ち上がる。ホームの詳細エディターをタップすると、コード入力画面になる。
![Clipboard02.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/bb3c59ef-4bce-d649-a949-30d41d7b463c.jpeg)

ここのに以下のコードを差し込み、「URL」と「api」の変数を適宜なおし、完了を押す。
初めて見る記法が多いと思うが、後で説明する。

```M
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
```

![Clipboard03.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/37e309a1-75d6-e3ca-6d91-28ed0de8f350.jpeg)

黄色い帯警告が出たら、「資格情報の編集」を押す。Webコンテンツへのアクセス、という項目を探し、この画面が出たら、「匿名」を開いて接続を押す。
※ Web APIは開かないこと。これでプリザンターのAPIは開けない。
![Clipboard04.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/6eb4f05f-761f-8f27-2812-7d7cd4ddce65.jpeg)

しばらく待ち、「Record」が大量に表示されれば成功だ。中身を見る場合は各リストをダブルクリックし、戻る場合は数式エディタに`= Data`と書けば良い。

APIから来たデータを加工する。リボンメニュー変換から「テーブルへの変換」を押す。
![Clipboard041.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/de20e77d-ecf6-5862-9130-57587687e771.jpeg)
 
変換後の操作方法については、こちらの記事が詳しい。
[Power Query: REST APIでListから取得したRecordをTableに変換するには](https://qiita.com/RPAbot/items/9ca6561827f51c7c8063)

変換を終えると、テーブルに各データがExcelのように表示されるようになる（Excelではないので機能は微妙に違う）。ここからデータ整形をしていこう。列に名前をつけたり、不要な列を消したり、列の型を変えたり、他の列と計算したり……

ちなみに以下画像例の「Column1.UpdateTime」は、右クリックからの型の変換で「日付/時刻」にすると良い。T以下を正規表現で削る必要はないのだ。

それらを終えたら、最後にリボンメニューのホーム「閉じて読み込む」を押す。
![Clipboard05.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/f1be6e1b-fc08-cdce-4952-027c49507694.jpeg)

無事にExcelへデータが反映された！ ここから更に関数やVBAで加工しても良いし、グラフを差し込むなどでも良いだろう。もちろん、ここまでの流れで、Pleasanter側のデータに変更が入ることは一切ない。データ集計機能だけが分離できて安全！
![Clipboard06.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/310971d9-35b4-bce3-d902-05a442770fff.jpeg)

なお、テーブルに更新が入ったらデータタブのクエリと接続から「すべて更新」を押す。更新分もExcelに反映されるはずだ。手順書を書いて他者に渡すときもカンタンに説明できる。

### 応用編1: クエリを複数読み込んで加工

なお、他のマスタデータの引当（Excelやプリザンターで言うところのLOOKUP、SQLでいうところのJOIN）なども、Power Queryでカンタンに完結できる。

例えば上記例だとユーザIDが数値の為、これもPleasanterのユーザ一覧からしたくなる。これを実現するには、クエリ1とは別に、更に別のクエリを作り、それをマスタデータにして引当をする形がよい。

Power Queryの画面からどこか右クリック、「空のクエリ」で新しいクエリを作成する。
![Clipboard01.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/a0215600-21da-bb60-46c6-78429f0077c0.jpeg)

クエリ2が作成できたら先程と同じように詳細エディタを開き、以下コードを貼って適宜修正し、完了を押す。

```M
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で必要なデータは画像の通り。
![Clipboard12.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/57609e10-d7b6-0647-893c-357d89569216.jpeg)

これでクエリ2がユーザマスタになった。クエリ1へ戻る。
![Clipboard13.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/44c97212-4145-406a-b2aa-39d6bb26e32a.jpeg)

リボンメニューのホームから「クエリのマージ」を開く。
![Clipboard14.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/21a3bb88-49de-b5da-04fc-2017046060ce.jpeg)

マージ下画面をクエリ2に指定し、Owner, UserIdと照合データを選択し、OKを押す。
![Clipboard16.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/dc3be9b0-e7c7-8594-919e-1e7d9776be4e.jpeg)

来た。展開するとクエリ1にユーザマスタの内容が表示されている。
![Clipboard18.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/d0088c55-823e-10c0-fdae-af5b0922fe86.jpeg)
![Clipboard19.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/de7e080d-53d3-1449-ff43-d02157297087.jpeg)

このように、クエリを複数を読み込み、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]でもかなりの機能を使うことができ、特に週次月次レポートなどで活躍する。
![Clipboard20.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/23318/34a09554-61e5-8470-8719-7b38df47e072.jpeg)

このような感じで、かなり柔軟な表がGUIで書ける。プリザンター標準の集計（プリザンターはクロス集計が非常に使いやすい）から外れて何かをする場合は、特に印刷やPDF化前提で使われるものであれば、自前でd3.js[^5]のコードを書かずに、Power BIを利用すると、作業の効率化＆集計スパンの短縮化に貢献するだろう。

レポートならExcelよりも、むしろこっち使ってくれまである。


### おまけ: さっきのプログラム言語なに
[Power Query M式言語](https://docs.microsoft.com/ja-jp/powerquery-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を使ってデータ収集分析](https://qiita.com/mosugi/items/71df310b35db81722d1e)
[Excelワークシート操作に相当するPower Query操作のメモ](https://qiita.com/tanuki_phoenix/items/e4ee13e9132180483dd8)
[ExcelでPowerQueryを使ってデータを結合して出力する](https://qiita.com/mosugi/items/b252aa9c9188e2ab2bde)

[^1]: しかも勝利確定画面で屈伸された
[^2]: 私
[^3]: Extract/Transform/Loadの略。ここでは複数のデータを集約して使いやすく加工するための、データ可視化ツールと定義する
[^4]: マイクロソフトストアからDLできる。Ubuntuインストールする場所と同じやつ。
[^5]: プリザンターに導入されているjavascriptのグラフ描画ツールセット。たぶんバーンダウンとかもこれで書かれてる。実際に使う際はそのプラグインであるc3.jsを$.getScriptで読ませると楽。