はじめに

こんにちは、パワーオートメーターメガネおじさんです。
やりたいことは後述のとおりですが、かなり悩んだあげく超絶クソな実装になりました。カスタムコネクタを使うと簡単にできるようです。

Microsoft Flow – Custom Connectors – How to read an Excel file from SharePoint?
こちらはGraphAPIを使っているようです。こちらの方が断然スマートな気がします。試してないけど。

やりたいこと

• SharePointのあるフォルダにExcelが幾つかある。
• それらのExcelにはテーブル「test」があり、各Excelには必ず同一キー値が存在する。
• フォルダ内にある全Excelのテーブル「test」上のあるキー値に紐づく行を別Excelに書き出す。

です。

失敗例

実装

「ファイルの取得 (プロパティのみ)」アクションでサイト、フォルダを指定します。

次に「行の取得」アクションを使います。

ポイントはパラメータ「ファイル」なのですが、PowerAutomateのUI的にはテキストボックスの右側のフォルダボタンっぽいのをクリックして、読み込むファイルを指定してあげないといけないようです。今回は「ファイルの取得 (プロパティのみ)」アクションで取得したファイルの配列を1件ずつ実行したいので、パラメータ「ファイル」は可変にしたいところです。
なので、↓みたい「ファイルの取得 (プロパティのみ)」アクションの戻り値の「完全パス」にしてみました。※なお、Excelに書き込む部分は特に難しくないと思うので省略。

実行結果

エラーになりました。エラーメッセージを見ると「ファイル」の指定の仕方が間違っているようです。

{
  "status": 400,
  "message": "The parameter 'file' has an invalid value 'Shared Documents/test4/Book1.xlsx'."
}

今度は↓のようにしてみました。テキストボックスの右側のフォルダっぽいボタンを使わずに、通常の文字列として入力してみました。

なぜか上記と同様なエラーになってしまいます。

パラメータ値の設定方法の違い

フォルダっぽいボタンを使ってファイルを指定するのと、ファイルのパスを直接書き込むとではコードが異なるようです。

ファイルのパスを直接書き込んだ場合のコード

ファイルのパスを直接書き込んだ場合のコードのプレビューを見てみます。

{
    "inputs": {
　　～(略)～
        "path": "/drives/@{encodeURIComponent('(略)')}/files/@{encodeURIComponent('/test4/Book1.xlsx')}/tables/～(略)
　　～(略)～
}

フォルダっぽいボタンを使ってファイルを指定した場合のコード

{
    "inputs": {
    ～(略)～
        "path": "/drives/@{encodeURIComponent('(略)')}/files/@{encodeURIComponent('01KJUAPFB6SE4PX2CYEREKSWIL5Z3KBTFD')}/tables/～(略)
    ～(略)～
}

filesの後ろのencodeURIComponentの引数がこちらの場合は謎の34桁の文字列です。これはどうやら、ブックIDと呼ばれるもののようで、Excel OnlineのアクションはブックIDを使ってファイルを特定するようです。
ちなみにブックIDはこちらの記事を参考にしてください。
Excel OnlineをAPI経由で更新する方法

解決策

ブックIDが必要なのは分かりました。では、どうやってブックIDを拾ってくるかが問題なのですが、上記の参考記事に倣って1件ずつ調べるわけにはいきません。「ファイルの取得 (プロパティのみ)」アクションの戻り値にブックIDがあれば最高なんですが。

「ファイルの取得 (プロパティのみ)」アクションの戻り値

結論としては戻り値に明らかにブックIDを指すものはありませんでした。ただ、{Thumbnail}にそれっぽいのがありました。実際の戻り値は自分で取得してください。

"{Thumbnail}": {
    "Large": "https://japaneast1-mediap.svc.ms/～(略)～FNfLbt0z%2fitems%2f01E67NVMTKD4U5QTXPNRGKF56A4SYMMSJV%3fversion%3dPubli～(略)～

これはファイルのサムネイルのURLのようです。アクセストークンが付いていてかなり長いURLです。その中にブックIDっぽい文字列がありました(%2fitems%2fの後ろ)。今回はこのサムネイルのURLからブックIDを取り出すことにします。

Thumbnailのフォーマット

ThumbnailのURLは以下で構成されているようです。

https://japaneast1-mediap.svc.ms/transform/thumbnail
?provider=spo
&inputFormat=xlsx
&cs=fFNQTw
&docid=https%3a%2f%2f・・・
&width=800
&height=800

ブックIDはクエリパラメータ「docid」の中に入っています。これはURLをURLエンコードした文字列のようです。docidは以下で構成されています(下記は分かりやすいようにURLデコードしてます)。

https://[テナント名].sharepoint.com:443/_api/v2.0/drives/b!4ULcV(略)t0z/items/01E67NVMTKD4U5QTXPNRGKF56A4SYMMSJV
?version=Published
&access_token=(略)

ブックIDを取り出す流れ

1. Thumbnailから"https%3a%2f%2f"以降を切り取り・・・変数:docid
2. 「docid」をURLデコード・・・変数：URLデコード後
3. 「URLデコード後」からURIパス部分のみ取り出す・・・変数：URIパス
4. 「URIパス」から"/items/"以降を切り取り・・・変数：ブックID

正規表現使えたらいいんですけどね。PowerAutomateに用意されている関数だけで頑張ります。
ちなみに関数のリファレンスはこちらです。
Azure Logic Apps および Power Automate の式で関数を使用するためのリファレンス ガイド

今回は以下の関数を使用します。

• indexOf
• substring
• uriComponentToString
• uriPath

実装

変数定義

上記の"ブックIDを取り出す流れ"で記載した変数に加え、Thumbnailを代入用の変数も定義します。全て文字列、初期値なしで定義します。

各アクション

サムネイルへの代入

ここはそのまま。

Thumbnailから"https"以降を切り取り

substring(variables('サムネイル'),indexOf(variables('サムネイル'),'https%3a%2f%2f'))

「docid」をURLデコード

uriComponentToString(variables('docid'))

「URLデコード後」からURIパス部分のみ取り出す

uriPath(variables('URLデコード後'))

「URIパス」から"/items/"以降を切り取り

substring(variables('URIパス'),add(indexOf(variables('URIパス'),'items/'),6))

最後の引数"6"は'items/'+1の値です。普通のプログラムであればlength('items/')+1とするところですが面倒なので。
これでブックIDを取得できます。取得したブックIDは一番最初の「行の取得」アクションの「ファイル」パラメータに設定します。

終わりに

今回ブックIDを取得するために変数を5つも定義しましたが、当然1つだけで十分なのですが、1つにすると関数が長くなってしまい可読性が悪くなってしまうので関数ごとに変数を定義しました。5つも変数を使うこと自体が可読性悪いかもしれませんが。
また関数を駆使してブックIDを取り出すことに成功しましたが、サムネイルのURLフォーマットが変わってしまうかもしれないので、ここまでやるんだったらSharePointからファイルをダウンロードしてVBAを走らせた方が早かったかもしれませんし、冒頭でご紹介したとおりカスタムコネクタを使っても良いかもしれません。
とはいえ、VBAとカスタムコネクタについてはプログラム初心者は難しい気がするので、今回の方が初心者には分かりやすいかもしれません。
そもそもSharePoint APIでブックIDを返却してくれれば良いのですが。

謝辞

@kzkamiya さん