【pleasanter/PowerQuery】プリザンターの情報をパワークエリで取得する②取得条件の指定方法

今回は、クエリの段階で条件を指定して、絞り込みしたデータを取得する、というのをやってみました。json文字列をどうやって書くのかなあというのをしこしこやってみた記録です。

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

１．はじめに
２．使用するプリザンター
３．コード
４．やってみる
５．いろいろな指定の書き方
６．最後に
７.参考文献、記事

１．はじめに

こちらの記事をとても参考とさせていただいています。この記事を見なかったらパワークエリでプリザンターと連携するなんて思いつきもしなかった。書き方も全然わからなかったのですが、コードをコピーさせていただいたら私にもできました！ありがとうございます。

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

さて、今回はエクセルVBAでプリザンターからデータを取得したときにやったように、日付などの条件を指定して、パワークエリでプリザンターから絞り込みした状態のデータを取得する、というのをやりたいと思います。パワークエリでフィルタすればいいじゃんという話かもしれませんが、データ量が多いサイトの場合、取得の段階で必要なデータだけにしたほうがいいような気がして。

jsonリクエストをM言語だとどうやって書くのかな？というのがわからなかったのでいろいろ試みてなんとかこうかなというのをつかみました。

２．使用するプリザンター

前回と同じ、下図のプリザンターから情報取得を試みます。サイトIDは151です。

３．コード

※サーバ名、サイトID、APIキーは実際の環境に合わせたものにしてください。

まず、条件を指定しないコード。（コードの説明は前回の記事をご参照ください）

let
    //※サーバー名とサイトIDは実際のものに変更する
    url = "http://localhost/api/items/151/get",
    //※apiキーは自身のapiキーに変更する
    api = "abc12345",
    requestJson = Json.FromValue([ApiKey = api]),
    
    res = Web.Contents(url,[
        Headers = [#"Content-Type" = "application/json"],
        Content = requestJson
    ]),
    res_json = Json.Document(res),
    Response = res_json[Response],
    Data = Response[Data]
in
    Data

このコードで、以下の条件を指定していきます。

・状況が100(未着手)、または200（実施中）
・分類Aが「良い」　（分類Aは選択肢を設定していない文字列の項目にしています）
・日付Aが 2024/1/1～2024/1/31

以下のコードが条件を指定したコードです。

let
    //※サーバー名とサイトIDは実際のものに変更する
    url = "http://localhost/api/items/151/get",
    //※apiキーは自身のapiキーに変更する
    api = "abc12345",
    
    requestJson = Json.FromValue([
        ApiKey = api,
        View = [
            ColumnFilterHash = [ 
                Status = "[100,200]",
                ClassA = "良い",
                DateA = "[""2024/01/01 00:00:00,2024/01/31 23:59:59""]" 
            ]
        ]
    ]),
    res = Web.Contents(url,[
        Headers = [#"Content-Type" = "application/json"],
        Content = requestJson
    ]),
    res_json = Json.Document(res),
    Response = res_json[Response],
    Data = Response[Data]
in
    Data

４．やってみる

①Excelのリボン「データ」タブ＞空のクエリを作成。詳細エディターを開きます。

②３のコードを貼り付けし、完了ボタンをクリックします。
※apiキー、サーバー、サイトIDなどは実際のものに変更してください。

③取得できたっぽい。リストの状態なので「リストツール」タブの「テーブルへの変換」ボタンをクリックします。

④展開ボタンをクリックして内容を展開します。
今回はレコードID,タイトル,状況,分類A,日付Aのみ展開します。
「元の列名をプレフィックスとして使用する」のチェックは外しています。

⑤ClassHash、DateHashをさらに展開します。

⑥できたっぽい。さらにDataAを日付時刻に変更します。

すてき！閉じて読み込むでエクセルシートに出力します。

プリザンターでフィルタをかけて確認してみたところ、同じ７件のデータが出ました。あってますね！

５．いろいろな指定の書き方

（１）正解の探し方

どうやってjson文字列作成の正解を探したかというと、詳細エディターでこちょこちょ書いて、ダメな場合下にエラーが出るので、ダブルコーテーションを増やしたり消したりして怒られなくなるように調整していきました。

それと、以下の公式サイトのJSONデータレイアウトを見ながらこうかな？とやりました。
開発者向け機能：JSONデータレイアウト：View

（２）説明項目や選択肢設定なしの分類項目

説明Aに「検討」を含む

requestJson = Json.FromValue([
    ApiKey = api,
    View = [
        ColumnFilterHash = [ 
            DescriptionA = "検討"
        ]
    ]
]),

説明Aが空欄

requestJson = Json.FromValue([
    ApiKey = api,
    View = [
        ColumnFilterHash = [ 
            DescriptionA = " "
        ]
    ]
]),

（３）分類項目（文字列の選択肢あり）

分類Bに選択肢　晴れ、曇り、雨　を設定
「晴れ」のデータを絞り込み

requestJson = Json.FromValue([
    ApiKey = api,
    View = [
        ColumnFilterHash = [ 
            ClassB = "[""晴れ""]"
        ]
    ]
]),

「曇り」または「雨」のデータを絞り込み

requestJson = Json.FromValue([
    ApiKey = api,
    View = [
        ColumnFilterHash = [ 
            ClassB = "[""曇り"",""雨""]"
        ]
    ]
]),

ダブルコーテーション多めでわかりにくいですよね。
ClassB = “[““晴れ““]”
赤字のダブルコーテーションはこれは文字列の始めと終わりを示すダブルコーテーションではなく、後ろの文字はただの普通のダブルコーテーションですよ、というのをパソコンさんに伝えたい、エスケープ文字というやつです。javaScriptだとエスケープ文字は\ですが、エクセルだとダブルコーテーションなんですね。だからよけいにややこしくなる。
エスケープ文字のダブルコーテーションを\に置き換えると以下のようになります。
ClassB = “[\“晴れ\“]”
ClassB = “[\“曇り\“,\“雨\“]”

（４）分類項目（数字の値と表示値のある選択肢あり）

分類Bを値と表示値のある選択肢に変更しました。
選択肢　100:晴れ、200:曇り、300:雨

（省略して ColumnFilterHash 内のコードだけにしています）

分類B　100（晴れ）を絞り込みしたい場合、以下のどちらでも行けました。

ClassB = "[""100""]"
ClassB = "[100]"

分類B　200（曇り）、300（雨）を絞り込みしたい場合、以下のどちらでも行けました。

ClassB = "[""200"",""300""]"
ClassB = "[200,300]"

（５）数値項目の指定

数値Ａが 10 or 20 or 30

NumA = "[10,20,30]"

1000～2000 または3000

NumA = "[""1000,2000"",3000]"

2000以下

NumA = "["",2000""]"

単純にズバッと数値を指定するときはダブルコーテーション不要で、以上とか以下とかを指定したいときはダブルコーテーションが必要、そして、エスケープ文字のダブルコーテーションも必要になりますね。

（６）日付項目の指定

日付Aが2023/10/18終日

DateA = "[""2023/10/18 00:00:00,2023/10/18 23:59:59""]"

日付Aが2023年10月1日から2023年10月31日

DateA = "[""2023/10/01 00:00:00,2023/10/31 23:59:59""]"

更新日が2023/11/1以降

UpdatedTime = "[""2023/11/01 00:00:00,""]"

作成日が2023/12/31以前

CreatedTime = "["",2023/12/31 23:59:59""]"

６．最後に

パワークエリでのjson文字列の指定方法をいろいろやってみました。

どんなjsonができているのかしら、というのはjsonRequesutのステップで下に出てきたcsvっていうやつを右クリックしてテキストを選択すると、なんか出てくるのでこれで確認しました。もっといい方法があるのかもしれないけど

７.参考文献、記事

プリザンター公式より

開発者向け機能：API：APIキーの作成

★開発者向け機能：JSONデータレイアウト：View

項目名とデータベース上のカラム名の対応

参考とさせていただいた記事。ありがとうございました。

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

[Power Query] for ループのような処理を Power Query で行う方法

[Power BI] Power Query の Web.Contents 関数でREST APIを使う（仮）

内部リンク

プリザンターPowerQuery連携シリーズ
【pleasanter/PowerQuery】プリザンターの情報をパワークエリで取得する①
【pleasanter/PowerQuery】プリザンターの情報をパワークエリで取得する②取得条件の指定方法
 【pleasanter/PowerQuery】プリザンターの情報をパワークエリで取得する③上限値以上のレコードを連続で取得する
 【pleasanter/PowerQuery】プリザンターの情報をパワークエリで取得する④ユーザーテーブルとマージ

VBAでインポート
【pleasanter/VBA】エクセルからプリザンターへデータを追加、更新する～import～