Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@ksgiksg(おいしみ)

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

Posted at

はじめに

今日はクラウドフローで複数のExcel から情報を取り出します。
image.png

部署ごとで取りまとめた参加者一覧を、すべて取得するフローです。
image.png

取得元となるExcel は、簡単のため初めからテーブルを用意しています。
image.png

配布するフォーマットとしてテーブルの使用ができない場合は、少々複雑になりますが、Office Scriptsを利用する 「スクリプトの実行」 や 「テーブルの作成」 アクションを併用してください。
image.png
image.png

結論

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

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

フローの流れ

まずはこのフローの全体のスクリーンショットをお見せします。
image.png

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

このフローのポイント

Excelを動的に指定するのがとても大変だったので、まずは何が大変か？ を解説します。

Excel を扱うためのインプット

まず、1つのExcel ファイルから情報を取得する方法を確認してみましょう。
こんな感じですね。
image.png

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

動的な指定にするために、このアクションで指定しているインプット情報についてもう少し詳しく確認します。
指定したインプットは、メニューのコードのプレビューから確認することができます。
image.png

このプレビュー中のinputs->parametersの中身が、アクションに指定しているインプット情報です。
フローを作成している画面では、プルダウンで簡単に指定できていますが、裏側ではこんな謎の文字列が使われているんですね。
image.png

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

インプット情報を動的に取得したい

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

フォルダ内のファイルを取得するには、SharePointコネクタの 「ファイルの取得(プロパティのみ)」 を使用します。
エントリーフォルダを制限することで、特定のフォルダ内のファイルを取得できます。
image.png

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

image.png

「ファイル メタデータの取得」 アクションでも、目的の情報がありません。
image.png

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

問題の解決

ダメもとで、フローの実行結果から、目的の値01GUGTXNDMB2L7APU4ERHL2PVJTRVLZCTSで検索をかけてみたところ、偶然にも？見つけることができました！
image.png

スクロールして、どこで取れたのか確認すると・・・
image.png
・・・サムネイル？

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

フローの解説

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

①目的のファイルを抽出

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

ただし、このままでは同じフォルダにある他のファイルやフォルダも巻き込んでしまいます。
image.png

「アレイのフィルター処理」 を使い、ファイル名や拡張子でフィルターしましょう。
このフローでは拡張子付きのファイル名が.xlsxで終わることを条件にフィルタしています。
image.png

②fileの取り出し

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

最初に{Thumbnail}/Smallを取り出します。(サイズはなんでもいいです)
image.png

取り出したURLは以下のような形式です。

{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

作成：サムネイルURL内のdocId
split(split(outputs('作成'), '&docid=')?[1], '&')?[0]

「指定文字で区切った何番目の値」という指定方法です。
PowerAutomate フローでの文字列処理には、よくこのsplit関数を使いますので覚えておくとよいと思います。

取り出したdocidは次のような文字列です。

作成：サムネイル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

作成：デコードしたdocId
decodeUriComponent(outputs('作成：サムネイルURL内のdocId'))

デコードした結果です。

作成：デコードしたdocId (output)
https://***.sharepoint.com:443/_api/v2.0/drives/b!ZyHUfhrcDUi_Yrz1JztkhXPR-FcSuXlElbX8IdlU93YfUHA3Vp79R4aubyL2fvUN/items/01GUGTXNDMB2L7APU4ERHL2PVJTRVLZCTS?version=Published

またここから「?」の前の文字列のうち、「/」で区切った「9番目」の要素を取り出します。
関数では以下となります。
image.png

作成：itemId
split(split(outputs('作成：デコードしたdocId'),'?')?[0],'/')?[8]

配列の要素は0始まりなのが注意点ですね。
ここまでしてようやくfileの取り出しができました。大変でしたね…

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

③テーブルの取得

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

なので、いったん 「テーブルの取得」 アクションでファイルの中のテーブルを全て取りだし、テーブル名でフィルタしてIdを検索します。
image.png

④テーブルの情報を取得して変数に格納

取得したテーブルIdを使って、テーブルの情報を取得します。

先に、取得した情報をまとめるための変数を定義しておきます。
image.png

「表内に存在する行を一覧表示」 アクションを使用します。
image.png

ここでテーブルIdをカスタム値として使用しますが、フィルタした結果は配列になっているので、first/last 関数やobject?[0] の形式で配列から単一の要素を取り出します。(型変換)
image.png

テーブル
body('アレイのフィルター処理：テーブル名')?[0]?['id']

取得した結果をまとめるために、変数に追加しますが、「配列変数に追加」アクションを使用するには、1行ずつ追加しないといけないので、Apply to each がまた必要になります。
image.png

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

一度に配列に追加するため、ここではunion 関数を使用しています。
変数の自己参照はできないため、作成アクションに変数をまるまる保管してあげるのがポイントです。
image.png

変数の設定
union(
    outputs('作成：Temporary'),
    outputs('表内に存在する行を一覧表示')?['body/value']
)

⑤HTMLテーブルにしてメッセージとして送信

最後に、結果をテーブルにして送信します。
image.png

今回は、テーブルの列構成に関わらず実行できるようにしているため、不要な列(ItemInternalIdなど)が含まれています。
キレイに成形したい場合は、テーブルの形式に合わせて「選択」アクションでMAPしてあげると綺麗にできます。
image.png

おわりに

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

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

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

1
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?