【pleasanter/PowerQuery】プリザンターの情報をパワークエリで取得する①

エクセルからVBAでプリザンターの情報取得はやっていましたが、パワークエリでプリザンターと連携する、という記事を拝読し、こんなこともできるんだ（パワークエリ初心者）とやってみたらできたので、その研究記録をここに記します。

プリザンターの情報を出力するだけならプリザンターのエクスポート機能を使えばよいのですが、出力後にいつも同じ成型作業をしている、同じ集計をしているなどという場合はパワークエリでプリザンターへ連携するファイルを作っておけば、更新ボタンクリックで最新の情報に更新されるので、便利だなと思いました。エクセルVBAでプリザンター情報を取得してもよいのですが、ゴリゴリ書く手間が軽減されるところはパワークエリのほうに軍配が上がるかなあと思います。使用目的によってはVBAのほうがあんなことやこんなことやいろいろできるので、良い場合もあると思います。

今回は、基本のパワークエリ連携と、いったい中身はどうなってるの？というところをネチネチやってます。次回、200件を超えるデータの取得方法などをやる予定です。

※おことわり
2024年2月時点の情報です。プリザンターのバージョンは 1.3.50.2 です。Google Chrome でやっています。
javascript,html,cssともに初心者です。調べながら、やってみながら、きっとこうすればいいんだ！という感じで書いていますので、間違っている場合、効率的な書き方ではない可能性が大いにあります。間違ってるよ！とか、こうしたほうがいいよ！ということがありましたら、コメント等で教えていただけると大変ありがたいです。

１．エクセルのパワークエリでプリザンターの情報を取得する
- （１）連携するプリザンター
- （２）パワークエリでやってみる
２．理解しようと試みてグダグダやってみる
- （１）VBAと見比べてみる
- （２）パワークエリを一つずつ実行してみる。
３．最後に
４.参考文献、記事

１．エクセルのパワークエリでプリザンターの情報を取得する

こちらのサイトを見よう見まねでやってみました。ほぼほぼコピペです。ありがとうございます。解説などもわかりやすく大変勉強になりました。
VBAは嫌なのでPleasanter APIからPower QueryでExcelに割振りたいと思います。（＋Power BI連携も）

（１）連携するプリザンター

下図のテストテーブルでやってみます。サイトIDは151です。

（２）パワークエリでやってみる

①情報を取得したいプリザンターのサイトのIDを控えておきます。
また、APIキー、サーバー名も必要になりますので控えておきます。
APIキーの取得の仕方は以下のサイトを確認してください。
開発者向け機能：API：APIキーの作成

②エクセルを開きます。

③リボン「データ」＞データの取得＞その他のデータソースから＞空のクエリ
（パワークエリエディタを開いて空のクエリが出せれば何でもよい）

③作成された空のクエリを選択し「詳細エディター」をクリック

④プレ入力されている let以下すべてクリアし、以下のコードを入力します。
※赤字部分のサーバー名、サイトIDは実際のものに変更してください。
url = “http(s)://サーバー名/api/items/サイトID/get”
httpの部分は環境設定によりhttpsまたはhttpになります。プリザンターのサイトのurlをコピーしてメモ帳に張り付けるとどっちだかわかります。
たとえばうちのプリザンターはこうでした。
http://localhost/items/151/index
これをペタッと張り付ければよいかと思いきや、サーバー名とitemsの間に/api/をはさまないとダメでした。
※apiキーはご自身のapiキーを入れてください。
api = “apiキー“

let
    //※サーバー名とサイトIDは実際のものに変更する
    url = "http://localhost/api/items/151/get",
    header = [#"Content-Type" = "application/json"],

    //※apiキーは自身のapiキーに変更する
    api = "abc12345",
    requestJson = Json.FromValue([ApiKey = api]),
    
    res = Web.Contents(url,[
        Headers = header,
        Content = requestJson
    ]),
    res_json = Json.Document(res),
    Response = res_json[Response],
    Data = Response[Data]
in
    Data

⑤「完了」ボタンを押します。すると以下のような感じでデータ取得できました。
これをパワークエリの機能で成型していきます。

※この画面になる前に「接続情報を指定してください」とメッセージが出る場合があります。「資格情報の編集」をクリックして「匿名」で接続します。

⑥テーブルに変換。
リストになっているのでテーブルに変換します。
「リストツール」タブの「テーブルに変換」ボタンをクリックします。

④テーブルに変換されました。これを展開します。
展開ボタン（っていうのかな？あっちこっち矢印のボタン）をクリック。
不要な項目はチェックを外します。
「元の列名をプレフィックスとして使用する」のチェックはどちらでもよいですが、外したほうが列名が短くなり見やすくなります。

⑤分類項目などが展開されていない場合はそちらも「展開ボタン」をクリックして必要な項目を展開します。

⑥ここまでで、基本の情報取得は完了です。「閉じて読み込む」でエクセルに展開してみましょう。

ちなみにここまでのM言語を見てみると、以下のようになっています。

let
    url = "http://localhost/api/items/151/get",
    header = [#"Content-Type" = "application/json"],
    api = "abc12345",
    requestJson = Json.FromValue([ApiKey = api]),
    res = Web.Contents(url,[
        Headers = header,
        Content = requestJson
    ]),
    res_json = Json.Document(res),
    Response = res_json[Response],
    Data = Response[Data],
    テーブルに変換済み = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"展開された Column1" = Table.ExpandRecordColumn(テーブルに変換済み, "Column1", {"SiteId", "UpdatedTime", "ResultId", "Title", "Body", "Status", "Manager", "Owner", "ItemTitle", "ClassHash", "NumHash", "DateHash", "DescriptionHash", "CheckHash"}, {"SiteId", "UpdatedTime", "ResultId", "Title", "Body", "Status", "Manager", "Owner", "ItemTitle", "ClassHash", "NumHash", "DateHash", "DescriptionHash", "CheckHash"}),
    #"展開された NumHash" = Table.ExpandRecordColumn(#"展開された Column1", "NumHash", {"NumA"}, {"NumA"}),
    #"展開された ClassHash" = Table.ExpandRecordColumn(#"展開された NumHash", "ClassHash", {"ClassA"}, {"ClassA"}),
    #"展開された DateHash" = Table.ExpandRecordColumn(#"展開された ClassHash", "DateHash", {"DateA"}, {"DateA"}),
    #"展開された DescriptionHash" = Table.ExpandRecordColumn(#"展開された DateHash", "DescriptionHash", {"DescriptionA"}, {"DescriptionA"}),
    #"展開された CheckHash" = Table.ExpandRecordColumn(#"展開された DescriptionHash", "CheckHash", {"CheckA"}, {"CheckA"})
in
    #"展開された CheckHash"

⑦パワークエリで成型する。
すてきに展開されました。が、パワークエリの機能であれこれ成型してから展開する、というところがパワークエリの醍醐味でしょうか。

もう一度 Power Query エディターに戻って、以下の条件で成型してみます。
・状況900、910を除外する
・日時の書式を修正する（データ型を日付時刻型に変更する）
・NumAの大きい順に並べ替えする

ここではパワークエリの手順は省略しますが、成型したり、必要なデータだけに絞り込みしたり、不要な列を外したり任意の順番にしておいたり、定型の集計などもあればそれも作成しておけば、今後はクエリを更新するだけで最新のデータが入手できますね。

番外編：エラーが出た場合

実はまず下図の「DataSource.Error:リモートサーバーに接続できません」というエラーが出て「一生できないかもしれない」とくじけそうになりました。ですが、この時はurlの打ち間違い（http なのに https としていた）でした。以下のようなエラーが出たら、url間違いを疑うとよいかもです。

２．理解しようと試みてグダグダやってみる

さて、これでできたっと。。。しかーし、何が起きているのかさっぱりわからない。

なので、中身をネチネチ見ていきたいと思います。ここから先は、興味のある方だけお付き合いいただけたらと思います。

（１）VBAと見比べてみる

これまでさんざんやってきたエクセルVBAでプリザンターから情報取得、のコードと比較してみました。

だいぶ端折って書いていますが、パワークエリもVBAも似たようなことをやっているように思える。なので、なんか一安心。

（２）パワークエリを一つずつ実行してみる。

パワークエリのステップを順を追ってみてみたいと思います。

１手目
url = “http://localhost/api/items/151/get”

2手目
header = [#”Content-Type” = “application/json”]

３手目
api = “abc12345”

こんな感じですね。変数に代入を行っています。

なお、変数に入れずにそのままコードに記載した場合のコードは以下のようになります。短くはなりますが、どうなんでしょうね。好みですね。

let 
    res = Web.Contents("http://localhost/api/items/151/get",[
        Headers = [#"Content-Type" = "application/json"],
        Content =  Json.FromValue([ApiKey = "abc12345"])
    ]),
    res_json = Json.Document(res),
    Response = res_json[Response],
    Data = Response[Data]
in
    Data

４手目
requestJson = Json.FromValue([ApiKey = api])

Json.FromValue関数はJson文字列を生成する関数だそうです。
Json.FromValue

なんかファイルっぽいのができてますね。そのファイルっぽいのを右クリックして「テキスト」を選択、または詳細エディターでrequestjsonの後に Text.FromBinary(requestJson) と入れると、jsonの中身っぽいのが見れます。

jsonっぽいですね。
[ApiKey = “abc12345”] が　{“ApiKey” : “abc12345”} になっている。
ということは [] が {} になり、 = が : になる、ということか。
キーワードっぽいやつはパワークエリ側では “” で囲わなくていいようですね。

ということは。。。エクスポートの時にやったみたいな複数のjson文字列リクエストもできるのか？

{ “ApiKey” : “abc12345”,
“Offset” : 200,
“View” : {
“ColumnFilterHash” : {
“Status” : “200”
},
“ColumnsSorterHash” : {
“ResutlId” : “asc”
}
}
}

requestJsonのステップを以下のコードに書き換えます。

requestJson = Json.FromValue([
        ApiKey = "abc12345",
        Offset = 0,
        View = [
            ColumnFilterHash = [ Status = 200 ],
            ColumnSoterHash = [ ResultId = "asc" ]
        ]
    ]),

実行してみると下図の結果となりました。状況が200にフィルタリングされて、ID昇順に並んでいます。jsonが通用しているようですね。

requestJson を Text.FromBinary で出力してみたところ、下図のようになっていました。
見にくいけれど、改行して形を整えると、上に書いたJsonと一致するはずです。

で、ここまでで遊んできたものはすべて消して次のステップへ行きます。。。

5手目
res = Web.Contents(url,[
Headers = header,
Content = requestJson
])
Web.Contents関数はWEBからデータを持ってこい！とやるやつですね。
構文は Web.Contents（URL, [オプション])
URLにはプリザンターのテーブルのurl、オプションにはヘッダー情報と,requestJsonで作成したJson文字列を入れています。
ダウンロードしたコンテンツをresで受け取っています。
Web.Contents

6手目
res_json = Json.Document(res)

Microsoft Learn より　Json.Document

構文
Json.Document(jsonText as any, optional encoding as nullable number) as any
バージョン情報
JSON ドキュメントの内容を返します。
引用元：https://learn.microsoft.com/ja-jp/powerquery-m/json-document

あっさりした説明が書いていあるのみ。。。

そしてあっさりした結果が表示されている。

StatusCode が 200。通信に成功したということですね。
Respose が Record ・・・これはあやしい。なにかが詰まっていそうな予感。クリックしてドリルダウンしてみましょう。

Responseの中身が見れました。ここでまた Data の List に何かが詰まってそう。Data の List をクリックして、さらにその中のRecordをクリックしてドリルダウンしてみると、わおー、プリザンターのデータが詰まってる！

と、データが取れたっぽいなということが分かったところで、ドリルダウンしたステップを削除して次へ進みます。

７手目
Response = res_json[Response]

res_json の Response の列のデータを突っ込んでる感じですね。

８行目
Data = Response[Data]

で、こんどはその中から ResponseテーブルのData 列にListとして詰まっているデータをDataテーブルに突っ込んだ。

いい感じになりましたね！あと一息感。

リスト形式なので、次に「テーブルに変換」ボタンでこれをテーブル化するステップを追加します。

テーブルに変換済み = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

で、あとは展開すればいいだけで。

３．最後に

そもそもパワークエリのほうも初心者なんで、わかったようなわからないような。でも便利そうだなあということはわかりました。

最初にも書きましたが、プリザンターの情報を出力するだけならプリザンターのエクスポート機能を使えばよいのですが、出力後にいつも同じ成型作業をしている、同じ集計をしているなどという場合はパワークエリでプリザンターへ連携するファイルを作っておけば、更新ボタンクリックで最新の情報に更新されるので、便利だなと思いました。

エクセルVBAの場合データの取得、成型や集計、さらにそのほかいろんなこともできます。ですが、書くのが面倒っちゃ面倒。プリザンターの情報をエクセルに持ってくる場合、パワークエリで済みそうな場合はパワークエリで、ごちゃごちゃ複雑なことをしたい場合などはVBAで、とベストな方法を選んでいくとよいですね。

お読みいただきありがとうございました。