PowerAutomateでExcelファイルをJSONファイルに変換する

<script src=”https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js”></script>
<script language="JavaScript">
$(document).ready( function () {
   $("a[href^='http']:not([href*='" + location.hostname + "'])").attr('target', '_blank');
})
</script>

# 概要

PowerAutomateのクラウドフローを利用してExcelファイルをJSONファイルに変換します。

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/a2502f68-c4a5-a6f3-81dc-abdebc168cdb.png>

# 対象

PowerAutomateクラウドフロー初心者および初級レベルの方

# 伝えたいこと

PoewrAutomateはシステム開発経験のない現場の作業者でも作成できるローコード開発ツールと言われており、Excelの関数が分かれば開発できると言われています。机上では簡単に作成できることになっていますが、実際はいろいろと落とし穴があり、それらをクリアしないと思うように作成できません。
ここでは、私がハマった落とし穴とその対策方法を紹介します。（落とし穴はあると分かっていれば怖くありませんよね？）

# クラウドフローの概要

フローの概要は以下の通りです。

<IMG WIDTH=80% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/bf48d383-6351-70fa-9bc1-bcac044a2e77.png>

### フローでよく使っているアクションの説明

<DL>
<DT>データ操作 > 作成</DT>
<DD><IMG WIDTH=60% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/20edc331-ccce-2400-6455-85923e29c8f6.png><BR>
アクションの名前は <B>作成</B> ですが、どちらかというと<B>関数を使ってデータを編集する</B>という感覚に近いです。<BR><IMG WIDTH=5% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/23b8682b-9a4f-7e4d-62c5-a87b6f496dfe.png>フロー中でこのアイコンを見たら関数を使ってデータを編集していると思ってください。
</DD>
</DL>

<DL>
<DT>コントロール > スコープ</DT>
<DD><IMG WIDTH=60% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/941de333-cb27-0a36-f4c1-3359331955ec.png><BR>
上の図のように作成1～作成3のアクションをまとめてフローを見やすくするために使用しています。
</DD>
</DL>

:::note info
スコープについてはこちらの Qiita記事 を参考にしてください。
:::
https://qiita.com/kojisaiki/items/aeecd7f20c224e77c665#%E3%82%B9%E3%82%B3%E3%83%BC%E3%83%97%E3%82%A2%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E3%82%92%E6%B4%BB%E7%94%A8%E3%81%97%E3%82%88%E3%81%86

### フローの概要説明

<DL>
<DT>1. ファイルが作成または変更されたとき</DT>
<DD>今回は Book1.xlsx が変更されたタイミングをトリガーとしています。</DD>

<DT>2. 出力ファイル名設定</DT>
<DD>入力ファイル名 Book1.xlsx の拡張子を変更し、Book1.json を出力ファイル名として設定します。</DD>

<DT>3. 入力テーブル名設定</DT>
<DD>Book1.xlsx 内の読み込むテーブルを設定します。</DD>

<DT>4. データ取得</DT>
<DD>入力テーブルからデータを取得します。</DD>

<DT>5. データ整形</DT>
<DD>取得したデータをJSON形式に整形します。</DD>

<DT>6. ファイル出力</DT>
<DD>JSON形式に整形したデータをファイル（Book1.json）に出力します。</DD>


### Excelファイルの前提条件

+ Excel 2007 以降の xlsx 形式のファイルであること。
+ テーブルが１つだけ設定されていること。
（テーブルが複数設定されていた場合は最初の一つ目のみ対象となります）
:::note info
Excelのテーブルって何？という方は下記URLを参考にしてください。
:::
https://curio-shiki.com/blog/excel/excel-table

# 落とし穴

今回の落とし穴は以下です。

+ データ取得の際、余計な情報が取得され、簡単には削除できない
  4\. データ取得 の際、テーブルデータとともに Excel の内部情報が取得される。
  これを削除したいが、一筋縄ではいかず工夫が必要となる。

# フローの説明

## 1\. ファイルが作成または変更されたとき

サイトのアドレス、ライブラリ、フォルダーをドロップダウンリストから選択します。

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/65823b5a-8ea9-9c12-d2ad-00203f05356d.png>

:::note warn
***トリガーの条件***
今回作成するフローではひとつ注意することがあります。
このトリガーは ***ファイルが作成または変更されたタイミング*** でフローが実行されます。
SharePoint ライブラリに ***Excel ファイルが作成または変更されたとき*** にフローが実行されますが、フローの最後で SharePoint ライブラリの同じ場所に JSON ファイルを出力しています。このため、***JSON ファイルが作成されたとき*** にもフローが実行され期待しない動作となってしまいます。下手をすれば無限ループになる可能性も・・
Excel ファイルのときだけフローが実行され、JSON ファイルのときはフローが実行されないようにできるといいですね。これを実装するためには ***トリガーの条件*** を追加します。
下にある式を ***トリガーの条件*** に追加することで、拡張子.xlsx のファイルに対してのみトリガーすることができます。

<IMG WIDTH=80% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/504feb75-c8ac-cd36-6b80-3e175d2f896f.png>

<IMG WIDTH=80% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/37c478a8-f55e-9571-5640-bbc211de120c.png>

```:トリガーの条件
@if(endsWith(triggerOutputs()?['body/{FilenameWithExtension}'],'.xlsx'),true,false)
```
:::

## 2\. 出力ファイル名設定

replace関数を使って 拡張子付きファイル名 の 拡張子.xlsx を 拡張子.json に置き換えます。

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/a3b11e06-b39a-c759-299f-ca7824cd6835.png>


```:出力ファイル名設定の式
replace(triggerOutputs()?['body/{FilenameWithExtension}'],'.xlsx','.json')
```

:::note info
<details>
<summary>クラウドフロー初心者向けの補足説明</summary>

上記の式だけ分かっても何をどうしていいか分からないかも知れないので、初回のみ丁寧に補足説明します。
<BR>

<IMG WIDTH=70% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/ebef5e1b-3f0c-68e5-ec9f-4ff6e7cc5572.png>

<DL>
    <DT>①検索         </DT><DD>コネクタとアクションが多いため、条件に<B>データ操作</B>を指定して検索します。</DD>
    <DT>②アクション選択</DT><DD>データ操作 > 作成 を選択します。</DD>
</DL>

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/63c7bca5-d898-71e8-3ddc-3ff3b1cd8951.png>

<DL>
    <DT>③コンテキストメニューの表示</DT><DD>三点リーダー（･･･）をクリックします。</DD>
    <DT>④メニューの選択     </DT><DD>コンテキストメニューから名前の変更を選択します。</DD>
    <DT>⑤名前の入力</DT><DD>名前を入力します。作成 を 出力ファイル名 に変更しています。</DD>
</DL>

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/ab9aa7d3-cbbd-8fa3-4208-1f47c4dd3ce3.png>

<DL>
    <DT>⑥入力   </DT><DD>入力エリアを選択します。</DD>
    <DT>⑦式     </DT><DD>式を選択し、fx エリアを選択します。</DD>
    <DT>⑧関数選択</DT><DD>replace関数を選択します。</DD>
</DL>

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/ad656105-662c-8521-5b75-0be6f9be2c23.png>

<DL>
    <DT>⑨動的なコンテンツ</DT><DD>動的なコンテンツを選択します。</DD>
    <DT>⑩挿入位置確認    </DT><DD>カーソル位置が replace() の括弧の内側にあることを確認します。</DD>
    <DT>⑪コンテンツ選択</DT><DD>拡張子付きのファイル名を選択します。</DD>
</DL>

<IMG WIDTH=50% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/0d4d60af-5cd7-6c3a-f7da-d1477677f0a9.png>

<DL>
    <DT>⑫置き換え文字列の入力</DT><DD>今回は 拡張子.xlsx を 拡張子.json に置き換えたいので <B>,'.xlsx','.json'</B> を入力します。<BR>
<A HREF="https://learn.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference#replace" TARGET="_blank" REL="noopener noreferrer">replace関数のリファレンス</A></DD>
    <DT>⑬入力完了</DT><DD>OKボタンをクリックします。<BR>
                    （図では更新ボタンになっています、あしからず）</DD>
</DL>

</details>
:::

## 3\. 入力テーブル名設定

ここも特に気を付けることはありません。
クラウドフロー初心者向けに補足説明を記してありますので、参考にどうぞ。

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/c6cc0d4e-4b88-e2f2-462f-3cbad681cf0c.png>

```:テーブル名を取得する式
outputs('テーブルの取得')?['body/value'][0]['name']
```

:::note info
<details>
<summary>クラウドフロー初心者向けの補足説明</summary>

***テーブルの取得***の実行結果が下図の通りとなり、ここから***テーブル名***を取得するには上記の式が必要になります。

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/fbe4c856-e991-4211-5b2d-9f7b164a955d.png>

上記の式と図を見てもピンとこない方は実行結果（詳細）を参考にしてください。

<div style="margin-left:10pt;">
<details>
<summary>実行結果（詳細）</summary>

```:outputs取得の実行結果
outputs('テーブルの取得')
```
<IMG WIDTH=50% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/bbec856e-1fb9-ae5c-c212-852d62275d14.png>

```:value取得の実行結果
outputs('テーブルの取得')?['body/value']
```
<IMG WIDTH=50% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/736393ae-51e8-e71a-5bcb-cea67f5673e0.png>

```:要素0取得の実行結果
outputs('テーブルの取得')?['body/value'][0]
```
<IMG WIDTH=50% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/e3dec5ff-baee-0728-b35c-b5a1259ba11e.png>

</details>
</div>

</details>

:::

## 4\. データ取得

ここも特に気を付けることはありません。
***表内に存在する行を一覧表示***で取得すると、下図のようなテーブルのデータが配列形式で取得できます。

:::note warn
Excel の表データにはない @odata.etag 、ItemInternalId というものが付いてきていますね。
:::

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/ad3e33d7-6873-b2fa-f173-14f63e7e76ec.png>

## 5\. データ整形

ここまでのフローは特に気を付けることはありませんでしたが、ここでは落とし穴があるので注意が必要です。とは言え、何をすべきか分かっていますので、手順通り対処すれば問題ありません。

フローは下の図の通りとなり、これまでのステップと比べいろいろな処理を実装しています。

<IMG WIDTH=70% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/2c19075a-e905-bdf5-9348-8f73050cdb93.png>

<DL>
<DT>①行ループ</DT>
<DD><B>4. データ取得</B> で取得した配列形式のデータをもとにループします。</DD>
<DT>②現在行</DT>
<DD>実際のフローでは不要なアクションです。<BR>
ここではループした際にどのような値が入っているか確認する目的で入れています。</DD>
<DT>③odata.etag削除</DT>
<DD>@odata.etag を削除します。<FONT COLOR=RED>➡ここが落とし穴です。<BR>
@odata.etag をキーとしてデータを削除する際 removeProperty 関数を使用しますが、キーにピリオド(.)が含まれているとエラーになってしまいます。このため、④～⑦の手順を踏む必要があります。</FONT></DD>
<DT>④JSONをString化</DT>
<DD>JSONとして扱えないので、いったん文字列に変換します。</DD>
<DT>⑤odata.etagをodata-etagに置換</DT>
<DD>文字列に変換後、禁則文字のピリオド(.)をハイフン(-)に置換します。<BR>
今回はハイフン(-)に置換していますが、禁則文字でなければ他の文字でも問題ありません。</DD>
<DT>⑥StringをJSON化</DT>
<DD>JSONとして扱うため、文字列からJSONに復元します。</DD>
<DT>⑦odata-etag削除</DT>
<DD>@odata.etagを削除します。</DD>
<DT>⑧ItemInternalId削除</DT>
<DD>ItemInternalId を削除します。</DD>
<DT>⑨BOM付与</DT>
<DD>BOM（バイトオーダーマーク）を付与します。<BR>
PowerAutomate のフローで扱う文字コードは UTF-8 です。作成された JSON ファイルをテキストエディタ等で開くと稀に UTF-8 と認識できずに文字化けする場合があります。（この場合でも文字コードを指定して開けば文字化けせずに開けます。）テキストエディタで文字コード UTF-8 の JSON ファイルを文字化けすることなく確実に開くにはファイルの先頭に 0xEF 0xBB 0xBF の３バイトを付与しておくとよいため、これを行います。</DD>
</DL>

クラウドフロー初心者向けに補足説明を記してありますので、参考にどうぞ。

:::note info
<details>
<summary>クラウドフロー初心者向けの補足説明</summary>

<DL>
<DT>②現在行</DT>
<DD>実際のフローでは不要なアクションです。<BR>
ここではループした際にどのような値が入っているか確認する目的で入れています。</DD>
</DL>

```:現在行の式
items('行ループ')
```

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/e316392e-b1dc-fefd-5fd8-e43ea3c93514.png>



<DL>
<DT>④JSONをString化</DT>
<DD>JSONとして扱えないので、いったん文字列に変換します。</DD>
<DT>⑤odata.etagをodata-etagに置換</DT>
<DD>文字列に変換後、禁則文字のピリオド(.)をハイフン(-)に置換します。<BR>
今回はハイフン(-)に置換していますが、禁則文字でなければ他の文字でも問題ありません。</DD>
</DL>

```:JSONをString化
string(items('行ループ'))
```
```:odata.etagをodata-etagに置換
replace(string(items('行ループ')), '@odata.etag', '@odata-etag')
```

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/fef01715-7947-2fb4-eccf-c5d534f6ab44.png>

<DL>
<DT>⑥StringをJSON化</DT>
<DD>JSONとして扱うため、文字列からJSONに復元します。</DD>
<DT>⑦odata-etag削除</DT>
<DD>@odata.etagを削除します。</DD>
</DL>

```:StringをJSON化
json(replace(string(items('行ループ')), '@odata.etag', '@odata-etag'))
```
```:odata-etagを削除
removeProperty(json(replace(string(items('行ループ')), '@odata.etag', '@odata-etag')),'@odata-etag')
```

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/0b3a7e1f-ad28-eea5-9ce3-1323e45807df.png>

<IMG WIDTH=50% SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/dcf278d0-d596-3ed2-8fab-81eaba8010a3.png>


<DL>
<DT>⑧ItemInternalId削除</DT>
<DD>ItemInternalId を削除します。</DD>
</DL>

```:ItemInternalId削除
removeProperty(outputs('odata-etag削除'),'ItemInternalId')
```

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/5a8c2351-1909-a3f8-f011-e1b41b9bf934.png>

<DL>
<DT>⑨BOM付与</DT>
<DD>BOM（バイトオーダーマーク）を付与します。<BR>
PowerAutomate のフローで扱う文字コードは UTF-8 です。作成された JSON ファイルをテキストエディタ等で開くと稀に UTF-8 と認識できずに文字化けする場合があります。（この場合でも文字コードを指定して開けば文字化けせずに開けます。）テキストエディタで文字コード UTF-8 の JSON ファイルを文字化けすることなく確実に開くにはファイルの先頭に 0xEF 0xBB 0xBF の３バイトを付与しておくとよいため、これを行います。</DD>
</DL>

```:BOM付与
concat(decodeUriComponent('%EF%BB%BF'), outputs('ItemInternalId削除'))

※decodeUriComponent('%EF%BB%BF') が BOM です。
　CSV テーブルの作成 の実行結果となる文字列の先頭にBOMを付与しています。
```

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/5ef9f47b-94a1-654f-27f0-61804c972286.png>


</details>

:::

## 6\. データ出力

ここも特に気を付けることはありません。
サイトのアドレス、フォルダーのパスはドロップダウンリストから選択します。
ファイル名、ファイルコンテンツはそれぞれ先行ステップの 出力ファイル名の出力結果、ファイルコンテンツの出力結果 を選択します。
通常は式を編集する必要はありませんが、説明のために式の内容を記しています。
```:ファイル名、ファイルコンテンツ
outputs('出力ファイル名')
outputs('BOM付与')
```

<IMG SRC=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3025334/527b7fab-f066-6d97-e2f1-97dad52ffdf9.png>

</details>

# まとめ

いかがでしたか？実際にフローを作成してうまく動けば、***Excelの関数が分かれば開発できる*** と思えなくはないですが、落とし穴についてはやはり何かしらの情報がないときびしいと思います。特に初心者の方にとっては、発生したエラーが関数の使い方が誤っていることが原因なのか、そうでないのか切り分けることすらきびしいと思うので、この記事が参考になれば幸いです。