はじめに
次のようなExcelやCSVをみたことがないだろうか。
何らかのシステムに転記したいという要望で、OrderID,CustomerNameのデータをA画面で入力後、B画面に遷移しProductsCode,Quantityのデータを入力するというものだ。矢印で示した動作とA,B画面の切替およびデータ登録の動作が必要となる。
Power Automate for desktopの基本アクションだけでもこの一連のフローを作ることができる。しかし実際作ってみればわかるのだが単純に一行毎処理するフローと違い、アクション数がかなり多くなる。ここにさまざまな実務ベースの処理が加わり、複雑さが増すことになる。ドキュメント作成やコメントの記述をおこなっていたとしても複雑になるほど、変更や問題が起きた場合の修正に時間がかかる。
ex1. ループ回数のリストを作成しリストに基づいて動作させるフロー
結論から言うとPower Automate for desktopでこのようなデータテーブルを扱う場合、データテーブル型のまま頑張るよりもカスタムオブジェクト型に変換することでアクション数を大幅に減らし、フローを単純化することができる。それが私自身最初に知りたかったデータテーブルの扱い方である。
カスタムオブジェクト型に変換するとよいの？
ただしカスタムオブジェクト型に変換すると言ってもデータテーブルを一度、階層構造をもったリスト型JSONに変換する。その後、Power Automate for desktopにカスタムオブジェクトとして読み込むとループがForEachだけで済むようになる。
ex2. ex1とまったく同じ動作を カスタムオブジェクト型に変換するフローで実装した
アクション数が半分以下になり、使っている変数も少なくなっているのがわかると思う。
「PowerShellスクリプトの実行」アクションにたくさんスクリプト書いているんだろうと思うかもしれない。しかし今回のスクリプトは1行だけだ。
フローの解説
1. PowerShellスクリプトの実行
Import-CSV "ファイルパス\datasample.csv" -Encoding UTF8 | Group-Object -Property "OrderID","CustomerName" |ConvertTo-Json 3
コマンドレットをパイプで接続したワンライナーになっている。このままでは見にくいのでパイプで改行する。
Import-CSV [[-Path] <string[]>] [-Encoding <string>]] |
Group-Object [[-Property] <Object[]>] |
ConvertTo-Json [[-Depth <int>]]
コマンドレットをひとつづつ動かしなら内容を見ていく
-
Import-CSV
CSVファイルのパスとファイルのエンコード形式を記述し読込をおこなう。エンコード形式を合わせないと文字化けするので注意が必要だ。
-
Group-Object
グルーピングしたい列を -Propertyに指定する。例のように複数列を指定可能。AsHashTableパラメーターを使わずにGroupInfoオブジェクトを返すようにするのがポイント。これによりリスト型のJSONとして出力できる。なおグループが1つしかない場合はリスト型にならないので後続のフローで工夫が必要になる。
-
ConvertTo-Json
JSON形式で出力する。値はすべてテキスト型になる。 Depthパラメーターが2のときは3階層目がハッシュテーブルで出力されている。なお指定しない場合デフォルトでは2となる。
Depthパラメーターを3にすることで3階層目までJSON形式に展開できる。
%PowerShellOutput%を「JSONをカスタムオブジェクトに変換」アクションに読み込ませて確認してみる。 階層構造をもったリスト型カスタムオブジェクト（一覧カスタムオブジェクト）になっているのがわかると思う。ValuesとGroupに必要な値がグルーピングされている。
2.If
グループが1つしか構成できない場合%PowershellOutput%はリスト型のJSONにならない。つまり後続でFor eachを回そうとするとエラーになる。そこで%PowershellOutput%の先頭文字を判定し「[」でない場合を判定する。
3.変数の設定
先頭文字が「[」でない場合、値が1つだけのリストにする。
%PowershellOutput%を[%PowershellOutput%]に上書きする。
4.End
5.JSONをカスタムオブジェクトに変換
6.コメント
############################
入力処理の開始
############################
7.For Each
%JsonAsCustomObject%はリストになっているのでForEachでループ
8.コメント
############################
A画面での処理
############################
9.メッセージの表示
確認用
10.For Each
%CurrentItem[‘Group’]%はリストになっているのでForEachでループ
11.コメント
############################
B画面での処理
############################
12.メッセージの表示
確認用
13.End
14.End
上記フローで「メッセージを表示する」アクションを「Excelワークシートに書き込み」アクションに変更して転記した。
まとめ
データテーブルをグループ化したJSONに変換、カスタムオブジェクトとして読み込むことで大幅にリファクタリングすることができた。ただしデータの順序については留意したほうがよい。
PowerShellはSort-Object,Select-Object,Where-Objectを使用できる。今回の方法と組み合わせるとさらに強力になるので是非試して頂きたい。
あとがき
残念ながら私のまわりでは、さまざまなデータがAPIでつながる世界線は遠く、CSVやExcelを介することが多い。実務では、今回取り上げたようなデータを何度も扱ってきた。しかし独学者にはつらいことに、Power Automate for desktopに限らず他の自動化ツールにおいても、こうしたデータの扱いについて、まともな情報を見つけることができなかった。たしかに基本アクションだけでも作れなくはない。SQLを使って多少アクション数を減らすこともできるが、どうしても複雑なフローになる。実務では動けば何でもいいというわけではない。複雑であるほど可読性が悪いため、メンテナンスが難しくなる。その結果持続性が失われる。実際。そのようなフローを作ってしまったこともある。なので個人的にはDocsかLearnに書いておいてほしかったレベルで、最初に知りたかったTipsだ。とはいえ、最初に知りたかったと書きながら、はじめたてだと少し難しいかもしれないが同じような境遇の方に、役立てれば幸いだ。
おまけ サンプルデータとデータテーブルからCSVへの変換
今回の記事と同じサンプルデータを試すことができる。 TempFileにCSVを生成し、PowerShellスクリプトにImportしている。元データがExcelでも応用が利くので参考になれば幸い。
1. 変数の設定
%{ ^['OrderID', 'CustomerName', 'ProductsCode', 'Quantitiy'], ['778aab84', 'aaa', 112233, 10], ['778aab84', 'aaa', 332244, 5], ['778aab84', 'aaa', 554433, 7], ['888m4jee', 'bbb', 332244, 2], ['55aarr34', 'ccc', 112233, 8], ['55aarr34', 'ccc', 778899, 12], ['55aarr34', 'ccc', 224455, 3], ['h7u8833', 'ddd', 778899, 24], ['h7u8833', 'ddd', 332244, 5], ['7har54ss', 'eee', 554433, 8] }%
2. 一時ファイルの取得
3. CSVファイルに書き込む
4. PowerShellスクリプトの実行
Import-CSV %TempFile% -Encoding UTF8 |
Group-Object -Property OrderID,CustomerName |
ConvertTo-Json -Depth 3
結果
注意事項
- Power Automate for desktop 2.17.169.22042
- Windows 10 pro 21H2
- PowerShell 5.1.19041.1320
- お決まりですが自己責任で。