概要
PowerAutomateのクラウドフローを利用してExcelファイルをJSONファイルに変換します。
対象
PowerAutomateクラウドフロー初心者および初級レベルの方
伝えたいこと
PoewrAutomateはシステム開発経験のない現場の作業者でも作成できるローコード開発ツールと言われており、Excelの関数が分かれば開発できると言われています。机上では簡単に作成できることになっていますが、実際はいろいろと落とし穴があり、それらをクリアしないと思うように作成できません。
ここでは、私がハマった落とし穴とその対策方法を紹介します。（落とし穴はあると分かっていれば怖くありませんよね？）
クラウドフローの概要
フローの概要は以下の通りです。
フローでよく使っているアクションの説明
- データ操作 > 作成
-
アクションの名前は 作成 ですが、どちらかというと関数を使ってデータを編集するという感覚に近いです。
フロー中でこのアイコンを見たら関数を使ってデータを編集していると思ってください。
- コントロール > スコープ
-
上の図のように作成1～作成3のアクションをまとめてフローを見やすくするために使用しています。
スコープについてはこちらの Qiita記事 を参考にしてください。
フローの概要説明
- 1. ファイルが作成または変更されたとき
- 今回は Book1.xlsx が変更されたタイミングをトリガーとしています。
- 2. 出力ファイル名設定
- 入力ファイル名 Book1.xlsx の拡張子を変更し、Book1.json を出力ファイル名として設定します。
- 3. 入力テーブル名設定
- Book1.xlsx 内の読み込むテーブルを設定します。
- 4. データ取得
- 入力テーブルからデータを取得します。
- 5. データ整形
- 取得したデータをJSON形式に整形します。
- 6. ファイル出力
- JSON形式に整形したデータをファイル（Book1.json）に出力します。
- Excel 2007 以降の xlsx 形式のファイルであること。
- テーブルが１つだけ設定されていること。
（テーブルが複数設定されていた場合は最初の一つ目のみ対象となります） - データ取得の際、余計な情報が取得され、簡単には削除できない
4. データ取得 の際、テーブルデータとともに Excel の内部情報が取得される。
これを削除したいが、一筋縄ではいかず工夫が必要となる。 - ①検索
- コネクタとアクションが多いため、条件にデータ操作を指定して検索します。
- ②アクション選択
- データ操作 > 作成 を選択します。
- ③コンテキストメニューの表示
- 三点リーダー（･･･）をクリックします。
- ④メニューの選択
- コンテキストメニューから名前の変更を選択します。
- ⑤名前の入力
- 名前を入力します。作成 を 出力ファイル名 に変更しています。
- ⑥入力
- 入力エリアを選択します。
- ⑦式
- 式を選択し、fx エリアを選択します。
- ⑧関数選択
- replace関数を選択します。
- ⑨動的なコンテンツ
- 動的なコンテンツを選択します。
- ⑩挿入位置確認
- カーソル位置が replace() の括弧の内側にあることを確認します。
- ⑪コンテンツ選択
- 拡張子付きのファイル名を選択します。
- ⑫置き換え文字列の入力
- 今回は 拡張子.xlsx を 拡張子.json に置き換えたいので ,'.xlsx','.json' を入力します。
replace関数のリファレンス - ⑬入力完了
- OKボタンをクリックします。
（図では更新ボタンになっています、あしからず） - ①行ループ
- 4. データ取得 で取得した配列形式のデータをもとにループします。
- ②現在行
- 実際のフローでは不要なアクションです。
ここではループした際にどのような値が入っているか確認する目的で入れています。 - ③odata.etag削除
- @odata.etag を削除します。➡ここが落とし穴です。
@odata.etag をキーとしてデータを削除する際 removeProperty 関数を使用しますが、キーにピリオド(.)が含まれているとエラーになってしまいます。このため、④～⑦の手順を踏む必要があります。 - ④JSONをString化
- JSONとして扱えないので、いったん文字列に変換します。
- ⑤odata.etagをodata-etagに置換
- 文字列に変換後、禁則文字のピリオド(.)をハイフン(-)に置換します。
今回はハイフン(-)に置換していますが、禁則文字でなければ他の文字でも問題ありません。 - ⑥StringをJSON化
- JSONとして扱うため、文字列からJSONに復元します。
- ⑦odata-etag削除
- @odata.etagを削除します。
- ⑧ItemInternalId削除
- ItemInternalId を削除します。
- ⑨BOM付与
- BOM（バイトオーダーマーク）を付与します。
PowerAutomate のフローで扱う文字コードは UTF-8 です。作成された JSON ファイルをテキストエディタ等で開くと稀に UTF-8 と認識できずに文字化けする場合があります。（この場合でも文字コードを指定して開けば文字化けせずに開けます。）テキストエディタで文字コード UTF-8 の JSON ファイルを文字化けすることなく確実に開くにはファイルの先頭に 0xEF 0xBB 0xBF の３バイトを付与しておくとよいため、これを行います。 - ②現在行
- 実際のフローでは不要なアクションです。
ここではループした際にどのような値が入っているか確認する目的で入れています。 - ④JSONをString化
- JSONとして扱えないので、いったん文字列に変換します。
- ⑤odata.etagをodata-etagに置換
- 文字列に変換後、禁則文字のピリオド(.)をハイフン(-)に置換します。
今回はハイフン(-)に置換していますが、禁則文字でなければ他の文字でも問題ありません。 - ⑥StringをJSON化
- JSONとして扱うため、文字列からJSONに復元します。
- ⑦odata-etag削除
- @odata.etagを削除します。
- ⑧ItemInternalId削除
- ItemInternalId を削除します。
- ⑨BOM付与
- BOM（バイトオーダーマーク）を付与します。
PowerAutomate のフローで扱う文字コードは UTF-8 です。作成された JSON ファイルをテキストエディタ等で開くと稀に UTF-8 と認識できずに文字化けする場合があります。（この場合でも文字コードを指定して開けば文字化けせずに開けます。）テキストエディタで文字コード UTF-8 の JSON ファイルを文字化けすることなく確実に開くにはファイルの先頭に 0xEF 0xBB 0xBF の３バイトを付与しておくとよいため、これを行います。
Excelファイルの前提条件
Excelのテーブルって何？という方は下記URLを参考にしてください。
https://curio-shiki.com/blog/excel/excel-table
落とし穴
今回の落とし穴は以下です。
フローの説明
1. ファイルが作成または変更されたとき
サイトのアドレス、ライブラリ、フォルダーをドロップダウンリストから選択します。
トリガーの条件
今回作成するフローではひとつ注意することがあります。
このトリガーは ファイルが作成または変更されたタイミング でフローが実行されます。
SharePoint ライブラリに Excel ファイルが作成または変更されたとき にフローが実行されますが、フローの最後で SharePoint ライブラリの同じ場所に JSON ファイルを出力しています。このため、JSON ファイルが作成されたとき にもフローが実行され期待しない動作となってしまいます。下手をすれば無限ループになる可能性も・・
Excel ファイルのときだけフローが実行され、JSON ファイルのときはフローが実行されないようにできるといいですね。これを実装するためには トリガーの条件 を追加します。
下にある式を トリガーの条件 に追加することで、拡張子.xlsx のファイルに対してのみトリガーすることができます。
@if(endsWith(triggerOutputs()?['body/{FilenameWithExtension}'],'.xlsx'),true,false)
2. 出力ファイル名設定
replace関数を使って 拡張子付きファイル名 の 拡張子.xlsx を 拡張子.json に置き換えます。
replace(triggerOutputs()?['body/{FilenameWithExtension}'],'.xlsx','.json')
クラウドフロー初心者向けの補足説明
上記の式だけ分かっても何をどうしていいか分からないかも知れないので、初回のみ丁寧に補足説明します。
3. 入力テーブル名設定
ここも特に気を付けることはありません。
クラウドフロー初心者向けに補足説明を記してありますので、参考にどうぞ。
outputs('テーブルの取得')?['body/value'][0]['name']
クラウドフロー初心者向けの補足説明
テーブルの取得の実行結果が下図の通りとなり、ここからテーブル名を取得するには上記の式が必要になります。
上記の式と図を見てもピンとこない方は実行結果（詳細）を参考にしてください。
実行結果（詳細）
outputs('テーブルの取得')
outputs('テーブルの取得')?['body/value']
outputs('テーブルの取得')?['body/value'][0]
4. データ取得
ここも特に気を付けることはありません。
表内に存在する行を一覧表示で取得すると、下図のようなテーブルのデータが配列形式で取得できます。
Excel の表データにはない @odata.etag 、ItemInternalId というものが付いてきていますね。
5. データ整形
ここまでのフローは特に気を付けることはありませんでしたが、ここでは落とし穴があるので注意が必要です。とは言え、何をすべきか分かっていますので、手順通り対処すれば問題ありません。
フローは下の図の通りとなり、これまでのステップと比べいろいろな処理を実装しています。
クラウドフロー初心者向けに補足説明を記してありますので、参考にどうぞ。
クラウドフロー初心者向けの補足説明
items('行ループ')
string(items('行ループ'))
replace(string(items('行ループ')), '@odata.etag', '@odata-etag')
json(replace(string(items('行ループ')), '@odata.etag', '@odata-etag'))
removeProperty(json(replace(string(items('行ループ')), '@odata.etag', '@odata-etag')),'@odata-etag')
removeProperty(outputs('odata-etag削除'),'ItemInternalId')
concat(decodeUriComponent('%EF%BB%BF'), outputs('ItemInternalId削除'))
※decodeUriComponent('%EF%BB%BF') が BOM です。
CSV テーブルの作成 の実行結果となる文字列の先頭にBOMを付与しています。
6. データ出力
ここも特に気を付けることはありません。
サイトのアドレス、フォルダーのパスはドロップダウンリストから選択します。
ファイル名、ファイルコンテンツはそれぞれ先行ステップの 出力ファイル名の出力結果、ファイルコンテンツの出力結果 を選択します。
通常は式を編集する必要はありませんが、説明のために式の内容を記しています。
outputs('出力ファイル名')
outputs('BOM付与')
まとめ
いかがでしたか？実際にフローを作成してうまく動けば、Excelの関数が分かれば開発できる と思えなくはないですが、落とし穴についてはやはり何かしらの情報がないときびしいと思います。特に初心者の方にとっては、発生したエラーが関数の使い方が誤っていることが原因なのか、そうでないのか切り分けることすらきびしいと思うので、この記事が参考になれば幸いです。