スポンサーリンク

【pleasanter/PowerQuery】プリザンターの情報をパワークエリで取得する④ユーザーテーブルとマージ

プリザンター
スポンサーリンク
※当サイトは広告を含みます

前回、前々回とパワークエリでプリザンターの情報を取得する、をやりました。
【pleasanter/PowerQuery】プリザンターの情報をパワークエリで取得する①
【pleasanter/PowerQuery】プリザンターの情報をパワークエリで取得する②取得条件の指定方法
【pleasanter/PowerQuery】プリザンターの情報をパワークエリで取得する③上限値以上のレコードを連続で取得する

今回はプリザンターからテーブルを取得すると同時に、ユーザー情報も取得して、ユーザーIDではなくユーザー名を表示する、というのをやってみたいと思います。
※ユーザーテーブルと組織テーブルにアクセス権がない等、取得できない可能性もあります。

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

１．使用するテーブル

サイトID151　

ユーザーテーブル

２．パワークエリでサイトの情報を取得し、ユーザーテーブルをマージしてユーザー名を表示する

（１）サイトの情報をパワークエリで取得する

まず、サイトの情報をパワークエリで取得します。

コード
ID151のサイトから情報を取得し、テーブルとして展開し、ID、タイトル、担当者を表示しています。
IDの昇順で取得しています。特に意味はありません。
※サーバー名、サイトID、apiキーは実際のものに書き換えてください

let
    url = "http://localhost/api/items/151/get",
    api = "abc12345",
    
    requestJson = Json.FromValue([
        ApiKey = api,
        View = [
            ColumnSorterHash = [ResultId = "asc"]
        ]
    ]),
    res = Web.Contents(url,[
        Headers = [#"Content-Type" = "application/json"],
        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", {"ResultId", "Title", "Owner"}, {"ResultId", "Title", "Owner"})
in
    #"展開された Column1"

「閉じて読み込む」として、ワークシートに出力したところです。担当者は取得していますが、IDの表示のためだれやらわかりません。

（２）ユーザーテーブルを取得する

空のクエリなどで（１）のテーブル情報取得とは別のクエリを立ち上げてください。

コード（※サーバー名、apiキーは実際のものに書き換えてください）
サイトの情報を取得するときとほとんど同じです。urlを items/サイトID から users に変えています。

let
    url = "http://localhost/api/users/get",
    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],
    テーブルに変換済み = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"展開された Column1" = Table.ExpandRecordColumn(テーブルに変換済み, "Column1", {"UserId", "Name", "UserCode", "DeptCode", "DeptId"}, {"UserId", "Name", "UserCode", "DeptCode", "DeptId"})
in
    #"展開された Column1"

情報を取得し、ユーザーID、名前、ユーザーコード、組織コード、組織IDを展開しました。
ワークシートには出力せず、接続専用とします。
クエリを右クリックして「名前の変更」でクエリ名を「userTB」としました

（３）二つのテーブルをマージする

①クエリ１を選択し、リボン「ホーム」＞「クエリのマージ」＞「クエリのマージ」をクリックします

②くっつけるテーブルに「userTB」を指定します。
結合するキー項目は、クエリ1は「Owner」をクリックして指定。
userTB側は「UserId」をクリックして指定。
結合の種類は初期値の「左外部」のままにします。

③userTBが結合されました。
「展開」ボタンをクリックして「Name」を表示します。

④名前が表示されました。

⑤Ownerの列を削除します。

⑥「閉じて読み込む」をクリックし、ワークシートに出力します。コードの代わりに名前が表示されました。

本筋とは関係ないのですが、せっかくResultId順に読み込んだのに、マージ後並び順が変わってしまいました。パワークエリってマージすると順番変わっちゃうんですかね。

ご参考まで、詳細エディタのコードは以下のようになりました。

let
    url = "http://localhost/api/items/151/get",
    api = ""abc12345",
    
    requestJson = Json.FromValue([
        ApiKey = api,
        View = [
            ColumnSorterHash = [ResultId = "asc"]
        ]
    ]),
    res = Web.Contents(url,[
        Headers = [#"Content-Type" = "application/json"],
        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", {"ResultId", "Title", "Owner"}, {"ResultId", "Title", "Owner"}),
    マージされたクエリ数 = Table.NestedJoin(#"展開された Column1", {"Owner"}, userTB, {"UserId"}, "userTB", JoinKind.LeftOuter),
    #"展開された userTB" = Table.ExpandTableColumn(マージされたクエリ数, "userTB", {"Name"}, {"Name"}),
    削除された列 = Table.RemoveColumns(#"展開された userTB",{"Owner"})
in
    削除された列

３．ユーザーテーブルを取得するときに、絞り込み条件を指定する場合の書き方

（１）でユーザーテーブルを取得していますが、全部は要らない、総務部（組織ID２）、と経理部（組織ID５）だけでいいんだけど、という場合は以下の指定になります。

let
    url = "http://localhost/api/users/get",
    api = "abc12345",

    requestJson = Json.FromValue([
        ApiKey = api,
        View = [
            ColumnFilterHash = [DeptId = "[2,5]"]
        ]
    ]),
    res = Web.Contents(url,[
        Headers = [#"Content-Type" = "application/json"],
        Content = requestJson
    ]),
//以下略

５．ユーザー情報を取得する関数を作ってみた。

（１）作成するもの

社員番号（userテーブル内でuserCodeに社員番号が充当されている想定）を入力すると、該当のユーザーの情報が表示される関数、社員名の一部を入力すると該当のユーザーの情報が表示される関数をパワークエリで作ってみたいと思います。

（２）準備

userテーブルとdeptテーブルの情報を取得するクエリを作成し、双方をマージして情報元DBとなるテーブルを作成しておきます。

①前項でやりましたが、userテーブルから情報を取得して、展開しておきます。読み込みは「接続専用」で大丈夫です。クエリの名前は「userTB」とします。

②deptテーブルからuserテーブルと同様に情報を取得して展開しておきます。読み込みは「接続専用」で大丈夫です。
userテーブルの取得方法とだいたい同じでurlの部分を users から depts に変更するくらいです。
url = “http://localhost/api/depts/get”
クエリの名前は「deptTB」とします。

③ ①のuserテーブルに②のdeptテーブルを組織IDでマージします。
①userTBをもとにして、「新規のクエリとしてマージ」としてdeptTBを結合しましたが、普通にマージでも大丈夫です。
クエリの名前は「マージuserTBdeptTB」とします。

（３）社員番号（ユーザーコード）からユーザー情報を取得する関数

空のクエリを作成し、以下のコードを入力します。
クエリ名は「fn社員番号からユーザー情報を取得」とします。

(社員番号 as text) =>
let 
    ソース = マージuserTBdeptTB,
    フィルターされた行 = Table.SelectRows(ソース, each ([UserCode] = 社員番号))
in
    フィルターされた行

「完了」をクリックすると以下のような画面が出ます。

関数を実行してみましょう。社員番号「p013」を入力し、「呼び出し」をクリックします。
社員番号p013の木村美恵さんが呼び出しされました。
木村さんのプリザンター内でのUserIdが14であることや、部署が営業部であることがわかりました。

（４）社員名（の一部）からユーザー情報を取得する関数

空のクエリを作成し、以下のコードを入力し、完了ボタンをクリックします。
クエリ名は「fnユーザー名からユーザー情報を取得」とします。

(社員名の一部を入力 as text) =>
let 
    ソース = マージuserTBdeptTB,
    フィルターされた行 =  Table.SelectRows(ソース, each Text.Contains([Name], 社員名の一部を入力))
in
    フィルターされた行

関数を実行してみましょう。
「鈴木」と入力して「呼び出し」をクリックします。
鈴木太郎さんと鈴木花子さんが表示されました。

（５）どうやって作ったか

素人の私にも作れた！作り方を大公開。

①まず、マージしたテーブルで、適当な社員番号でフィルターをかける。
②「詳細エディター」をクリックしてコードを表示。フィルターした部分のコードをコピーする。
③コードをコピーしたら、フィルターしたステップは削除します。

④空のクエリを作成します。
⑤「詳細エディター」をクリックして開いて、ソースの=の右辺にマージuserTBdeptTBと基にするデータのクエリ名を入力します。最後のカンマも忘れずに

⑥　②でコピーしておいたフィルターのコードを改行して張り付けします。
2か所、書き換えます。

⑦一番上の行に引数を受け取る部分を入力します。この部分があるとパラメーター入力の画面になるんですね。
(引数名) =>
と入力します。引数名は何でもOK。as text は引数はテキスト型に限定しますよ！という意味を持ちますが、あってもなくてもいいです。

⑧フィルターの “p002” 部分を引数名に置き換えます。

これで完了をクリックすると出来上がりです。

６．最後に

今回は、パワークエリでプリザンターのユーザー情報を取得しておいて、テーブルから取得した情報のユーザーコードが表示される部分に氏名などを表示できるようにする、というのをやりました。

ユーザーがめっちゃいるんだけど。。。という環境では試していないので、そのような場合どうなるかはわかりません。。。

ユーザー情報を取得する関数なんかも作ってみたりしましたが、それも実際使う場面があるのか。。。パワークエリ勉強中なので、やってみたかった、というのがほんとのところです。

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

７.参考文献、記事

プリザンター公式より

開発者向け機能：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】エクセルからプリザンターのデータを取得する
【pleasanter/VBA】エクセルからプリザンターのデータを取得する②絞り込み条件の指定
【pleasanter/VBA】エクセルからプリザンターのデータを取得する③変数で絞り込み条件を指定する
【pleasanter/VBA】エクスポート④絞り込み条件で〇〇を含むを指定する
【pleasanter/VBA】エクスポート⑤並べ替え
【pleasanter/VBA】エクスポート⑥表示名を出したい
【pleasanter/VBA】エクスポート⑦データがちょん切れる時の対応 Offset
【pleasanter/VBA】エクスポート⑧出力される日付を文字列ではなく日付時刻型で出したい
【pleasanter/VBA】エクスポート⑨汎用ツールを作ってみた
【pleasanter/VBA】エクスポート⑩汎用ツール完全版

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

スポンサーリンク

コメント

タイトルとURLをコピーしました