フォルダ内のExcelを一括処理するクラウドフロー

# はじめに
今日はクラウドフローで複数のExcel から情報を取り出します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/b4494e43-f6cb-70d0-a8a6-02d1122d0b50.png)

部署ごとで取りまとめた参加者一覧を、すべて取得するフローです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/6a3ad8e5-1a7f-519f-1c81-08f66b750a35.png)

取得元となるExcel は、簡単のため初めからテーブルを用意しています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/5c1d3437-8f85-6ce6-98c6-c8a4a2a5ce7f.png)

配布するフォーマットとしてテーブルの使用ができない場合は、少々複雑になりますが、Office Scriptsを利用する **「スクリプトの実行」** や **「テーブルの作成」** アクションを併用してください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/bbb3d118-ee71-e7b4-2d94-7f9680dfd26a.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/59db2e53-11c0-cabe-2296-05de6a1e2f59.png)


# 結論
こんなことはせずに、**Microsoft Forms**で申請フォームを作った方がいいです。
どうしてもファイルが分かれてしまう場合でも、**Power Query**でまとめて取得する方が単純です。

…と、詮無きことを初めに言っておきます(∵)
それでも、どうしても、Power Automate フローで処理したい場合や、1つの手段として参考に知っておきたい方は、以降もご覧ください。

# フローの流れ
まずはこのフローの全体のスクリーンショットをお見せします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/04db5e14-899e-88aa-417c-55aaa5e4af59.png)

やりたいことのわりには、かなりステップが多いです。
あまりまっとうなやり方のようにも思えないので、オススメはしません。(しつこいですが何度か述べます)

# このフローのポイント
Excelを動的に指定するのがとても大変だったので、まずは**何が大変か？** を解説します。
## Excel を扱うためのインプット
まず、1つのExcel ファイルから情報を取得する方法を確認してみましょう。
こんな感じですね。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/f514327c-13d5-9824-82a6-88cb517f328b.png)

複数のファイルを処理するためには、すくなくともこの`ファイル`を動的な指定にする必要があります。
ループ回さないといけないですからね。

動的な指定にするために、このアクションで指定しているインプット情報についてもう少し詳しく確認します。
指定したインプットは、メニューの**コードのプレビュー**から確認することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/91f08844-0e3a-68a8-9f3e-7ca237d76376.png)

このプレビュー中の`inputs`->`parameters`の中身が、アクションに指定しているインプット情報です。
フローを作成している画面では、プルダウンで簡単に指定できていますが、裏側ではこんな謎の文字列が使われているんですね。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/7c586d75-d010-b7b9-070d-5d2822d23caa.png)

なので、ここでは`file`に指定されている`01GUGTXNDMB2L7APU4ERHL2PVJTRVLZCTS`と同等の情報を動的に指定することが必須となります。(ファイルごとに異なる値です)

## インプット情報を動的に取得したい
さて、フォルダ内の複数のファイルを指定するために、先ほどの`file`に指定できる情報を取得しましょう。

フォルダ内のファイルを取得するには、SharePointコネクタの **「ファイルの取得(プロパティのみ)」** を使用します。
エントリーフォルダを制限することで、特定のフォルダ内のファイルを取得できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/c37b6253-1297-dd90-2ff9-611194db55d1.png)

しかしながら、このアクションで取得できるファイルの情報の中に、ファイルを特定するための情報はいくつかあるのですが、そのどれもが`file`に指定できる情報とは異なる値となっています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/a664f208-305f-4035-0367-227a040c5c3c.png)

**「ファイル メタデータの取得」** アクションでも、目的の情報がありません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/5b76a419-a5db-c35e-5f94-bd9440173449.png)

ファイルの情報を取得できるアクションは、粗方試したのですが、この`file`をそのまま取得できるアクションは見つかりませんでした…

## 問題の解決
ダメもとで、フローの実行結果から、目的の値`01GUGTXNDMB2L7APU4ERHL2PVJTRVLZCTS`で検索をかけてみたところ、偶然にも？見つけることができました！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/04d05444-a11f-dd2a-398b-8ba41821c819.png)

スクロールして、どこで取れたのか確認すると・・・
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/c0fc2d55-5c4e-4d81-2847-f388c96ccf92.png)
・・・サムネイル？

つまり、このサムネイルURLから何とかして取り出すことができれば使えそうです。

# フローの解説
ということで、全体の流れをさらいつつ、フローの解説を行います。

## ①目的のファイルを抽出
最初にフォルダ内からExcel ファイルをすべて取り出します。
先ほど説明した **「ファイルの取得(プロパティのみ)」** を使用すればOKですね。

ただし、このままでは同じフォルダにある他のファイルやフォルダも巻き込んでしまいます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/78fb8653-6a65-a1f2-a34e-1cd2ac7ce16c.png)

**「アレイのフィルター処理」** を使い、ファイル名や拡張子でフィルターしましょう。
このフローでは拡張子付きのファイル名が`.xlsx`で終わることを条件にフィルタしています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/d7cc9d96-6ffe-a4e9-e10d-b82fef5dd4c3.png)

## ②`file`の取り出し
抽出したExcelファイルを対象にApply to each でループ処理を行います。
サムネイルURLから`file`に指定する情報を取り出すことから始めましょう。

最初に`{Thumbnail}/Small`を取り出します。(サイズはなんでもいいです)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/148508df-9a3e-2282-4b69-65631da847aa.png)

取り出したURLは以下のような形式です。
```uri:{Thumbnail}/Small
https://eastus1-mediap.svc.ms/transform/thumbnail?provider=spo&inputFormat=xlsx&cs=fFNQTw&docid={fileが含まれるURL}&access_token={アクセストークン}&width=96&height=96
```

`docid`という属性の中にほしい情報があるので、まずは`docid`を取り出します。
「&docid=」から「&」までの文字を取り出せばよいので、split関数を使って、以下のように取り出しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/9c126000-9901-d085-0423-ab92f34684a8.png)

```powerfx:作成：サムネイルURL内のdocId
split(split(outputs('作成'), '&docid=')?[1], '&')?[0]
```
「指定文字で区切った何番目の値」という指定方法です。
PowerAutomate フローでの文字列処理には、よくこのsplit関数を使いますので覚えておくとよいと思います。

取り出したdocidは次のような文字列です。
```uri:作成：サムネイルURL内のdocId (output)
https%3A%2F%2F***.sharepoint.com%3A443%2F_api%2Fv2.0%2Fdrives%2Fb!ZyHUfhrcDUi_Yrz1JztkhXPR-FcSuXlElbX8IdlU93YfUHA3Vp79R4aubyL2fvUN%2Fitems%2F01GUGTXNDMB2L7APU4ERHL2PVJTRVLZCTS%3Fversion%3DPublished
```
パーセントエンコーディングという形式で変換されているので、デコードして元に戻してあげます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/33a1651c-4113-ee77-cc0c-01fb95ec48c3.png)
```powerfx:作成：デコードしたdocId
decodeUriComponent(outputs('作成：サムネイルURL内のdocId'))
```
デコードした結果です。
```uri:作成：デコードしたdocId (output)
https://***.sharepoint.com:443/_api/v2.0/drives/b!ZyHUfhrcDUi_Yrz1JztkhXPR-FcSuXlElbX8IdlU93YfUHA3Vp79R4aubyL2fvUN/items/01GUGTXNDMB2L7APU4ERHL2PVJTRVLZCTS?version=Published
```

またここから「?」の前の文字列のうち、「/」で区切った「9番目」の要素を取り出します。
関数では以下となります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/5038f104-db5e-f648-3f5f-bf296ac83cbc.png)
```powerfx:作成：itemId
split(split(outputs('作成：デコードしたdocId'),'?')?[0],'/')?[8]
```
配列の要素は0始まりなのが注意点ですね。
ここまでしてようやく`file`の取り出しができました。大変でしたね…

ちなみに、同じ情報の中に、ドキュメントライブラリを指定するための`drive`の情報も含まれています。
思いついたら何かに応用してみてください(∵)

## ③テーブルの取得
ファイルが特定できるようになったので、次はテーブルに記載された情報を取り出したい…のですが、テーブルを指定する`Id`がわかっていません。

なので、いったん **「テーブルの取得」** アクションでファイルの中のテーブルを全て取りだし、テーブル名でフィルタして`Id`を検索します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/20d7f861-4205-5c20-5662-d877556b589d.png)


## ④テーブルの情報を取得して変数に格納
取得した`テーブルId`を使って、テーブルの情報を取得します。

先に、取得した情報をまとめるための変数を定義しておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/1ef3884d-a5a5-4c8e-7cec-b1f26ef94572.png)

**「表内に存在する行を一覧表示」** アクションを使用します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/ce5481c2-fa5c-4fcc-b43f-aa9fc2ebe6b8.png)

ここで`テーブルId`をカスタム値として使用しますが、フィルタした結果は配列になっているので、**first/last** 関数や**object?[0]** の形式で配列から単一の要素を取り出します。(型変換)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/e9922fcb-7723-80bd-7995-44bb83fc8a43.png)
```powerfx:テーブル
body('アレイのフィルター処理：テーブル名')?[0]?['id']
```

取得した結果をまとめるために、変数に追加しますが、「配列変数に追加」アクションを使用するには、1行ずつ追加しないといけないので、Apply to each がまた必要になります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/b9b9db4c-f939-569f-b071-5f43898ef208.png)

これでもいいのですが、不要なループは実行時間がかかるなど、問題があるので可能であれば避けたいです。

一度に配列に追加するため、ここでは**union** 関数を使用しています。
変数の自己参照はできないため、作成アクションに変数をまるまる保管してあげるのがポイントです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/ead0f986-59f5-8be2-5a55-8929e90ae994.png)
```powerfx:変数の設定
union(
    outputs('作成：Temporary'),
    outputs('表内に存在する行を一覧表示')?['body/value']
)
```


## ⑤HTMLテーブルにしてメッセージとして送信
最後に、結果をテーブルにして送信します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/9776516b-3a40-2c1f-ceaf-4b640e7fd56c.png)

今回は、テーブルの列構成に関わらず実行できるようにしているため、不要な列(ItemInternalIdなど)が含まれています。
キレイに成形したい場合は、テーブルの形式に合わせて「選択」アクションでMAPしてあげると綺麗にできます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2572227/16c38b60-c8d8-f971-5851-77d7a108f7bd.png)


# おわりに
Power Automate でのファイルの扱いって、ファイルを特定する方法がアクションごとに癖があって難しいですね。

Graph API を使えばもう少しバチっと決まりそうな気もしますが、今回は標準アクションのみで実装してみました。
(逆に難しくなっている気もします)

個人的にはクラウドフローとExcel の相性が良くない気がしているのですが、なにかのご参考になれば幸いです。