21
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

プリザンター(Pleasanter)Advent Calendar 2021

Day 18

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

Last updated at Posted at 2021-12-18

序文

  • 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のデータタブから、画像通りに空のクエリを選択。
Clipboard00.jpg

別画面でPower Queryが立ち上がる。ホームの詳細エディターをタップすると、コード入力画面になる。
Clipboard02.jpg

ここのに以下のコードを差し込み、「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

Clipboard03.jpg

黄色い帯警告が出たら、「資格情報の編集」を押す。Webコンテンツへのアクセス、という項目を探し、この画面が出たら、「匿名」を開いて接続を押す。
※ Web APIは開かないこと。これでプリザンターのAPIは開けない。
Clipboard04.jpg

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

APIから来たデータを加工する。リボンメニュー変換から「テーブルへの変換」を押す。
Clipboard041.jpg

変換後の操作方法については、こちらの記事が詳しい。
Power Query: REST APIでListから取得したRecordをTableに変換するには

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

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

それらを終えたら、最後にリボンメニューのホーム「閉じて読み込む」を押す。
Clipboard05.jpg

無事にExcelへデータが反映された! ここから更に関数やVBAで加工しても良いし、グラフを差し込むなどでも良いだろう。もちろん、ここまでの流れで、Pleasanter側のデータに変更が入ることは一切ない。データ集計機能だけが分離できて安全!
Clipboard06.jpg

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

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

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

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

Power Queryの画面からどこか右クリック、「空のクエリ」で新しいクエリを作成する。
Clipboard01.jpg

クエリ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で必要なデータは画像の通り。
Clipboard12.jpg

これでクエリ2がユーザマスタになった。クエリ1へ戻る。
Clipboard13.jpg

リボンメニューのホームから「クエリのマージ」を開く。
Clipboard14.jpg

マージ下画面をクエリ2に指定し、Owner, UserIdと照合データを選択し、OKを押す。
Clipboard16.jpg

来た。展開するとクエリ1にユーザマスタの内容が表示されている。
Clipboard18.jpg
Clipboard19.jpg

このように、クエリを複数を読み込み、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

このような感じで、かなり柔軟な表が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を使ってデータを結合して出力する

  1. しかも勝利確定画面で屈伸された

  2. Extract/Transform/Loadの略。ここでは複数のデータを集約して使いやすく加工するための、データ可視化ツールと定義する

  3. マイクロソフトストアからDLできる。Ubuntuインストールする場所と同じやつ。

  4. プリザンターに導入されているjavascriptのグラフ描画ツールセット。たぶんバーンダウンとかもこれで書かれてる。実際に使う際はそのプラグインであるc3.jsを$.getScriptで読ませると楽。

21
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
21
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?