LoginSignup
41
45

More than 3 years have passed since last update.

@Yellow11

Do untilを使い、ExcelやSharePointリストのデータ取得数制限を回避して全件取得する

Last updated at Posted at 2019-12-13

この記事はOffice 365 Advent Calendar 2019 (12/13)に参加しています。
予定では承認ワークフローのお話でしたが、内容が薄くなりそうだったので差し替え。

今日は13日の金曜日！
JSONをよく使う Microsoft Flow 改め Power Automate のお話です。
Power AutomateからPower Appsへテーブルを送る時にも使えます。

2019/12/16 16:30 追記
アクションの「設定」から改ページを弄って全件取得する方法を書き忘れていたので追記しました。

データ取得数制限

Power AutomateでExcelテーブルやSharePointリストからまとまったデータを取得したい！
データが多い場合、まず失敗します。

コネクタは、物によって一度に取得できるデータ数に制限があるからです。

例えばExcel Online (Business)コネクタのアクション表内に存在する行を一覧表示は、一度に256行までしか取得できません。
(2019/12/13現在)
image.png

そしてSharePointコネクタのリストを読み出すアクション複数の項目の取得は、一度に1000アイテムが制限値です。
(2019/12/13現在)
image.png

Do until

それらを解決するのがDo untilです。
image.png
Do untilは設定した条件がtrueになるまで処理を繰り返し実行します。

これを利用してExcelであれば256行、SharePointであれば1000アイテムずつ取得する処理を繰り返し、全件取得します。

ロジックはこんな感じ。
PowerAutomateからExcelテーブルを読み出す.png

前置き

この記事はExcelテーブルを表内に存在する行を一覧表示で取得する前提で話を進めます。

必要に応じて表内に存在する行を一覧表示を任意のアクションへ、Excelの制限値256を任意の値に置き換えて読んで下さい。

実装

下準備

まず変数を三つ初期化します。

名前 種類 初期値
LoopCount 整数 0
DataEmpty ブール値 false
JoinArray アレイ Blank

image.png
image.png
image.png

Do untilの設定

組込みの制御からDo untilをフローに追加します。
image.png
値の選択には変数DataEmptyを設定。
image.png
これでDataEmpty=trueになるまで処理を繰り返します。

Excelテーブルの読み出し

Do untilにはまずアクション表内に存在する行を一覧表示を追加します。
image.png

詳細オプションを表示し、スキップ数が256*変数LoopCountとなるように設定します。
構文は以下の通り。

スキップ数
mul(256,variables('LoopCount'))

変数LoopCountの初期値は0のため、初回のDo until実行では256*0で0行がスキップされます。

Excelテーブルを全て取得済みか判定

次にアクション表内に存在する行を一覧表示の応答が空(empty)かどうかを判定します。

empty=trueであれば全ての行を取得したのでDo untilを終了させる処理を行います。
empty=falseであればDo untilを繰り返すための前処理を行います。

まず制御からアクション条件を追加します。
image.png

値の選択には以下の構文を入れ、trueに等しいか判定させます。

表内に存在する行を一覧表示の応答は空？
empty(body('表内に存在する行を一覧表示')?['value'])

image.png

body('表内に存在する行を一覧表示')?['value']がアクション表内に存在する行を一覧表示の応答なので、empty 関数を使う事で空かどうか判定しています。

empty=trueの場合

変数DataEmptyをtrueに設定します。
image.png

Do untilの終了

empty=trueの場合、ここでDo untilが繰り返します。
そして繰り返し条件DataEmpty=trueが満たされているので、Do untilが終了します。

empty=falseの場合

empty=falseの場合、Do untilを繰り返す前に以下の処理を行います。

変数LoopCountを+1する

まず変数LoopCountを+1します。
image.png
これでExcelテーブルの読み出しで設定したスキップ数が変動し、取得済みの行が自動でスキップされます。

変数JoinArrayに取得したテーブルを格納する

初回と2回目以降では処理が異なるので条件を設定します。
image.png

値の選択に入っている式は以下の構文です。

この処理が初回か否か
empty(variables('JoinArray'))

変数JoinArrayは初期値が空白(Blank)なので、初回は必ずtrueを返します。

trueの場合

変数JoinArrayにアクション表内に存在する行を一覧表示からvalueを格納します。
image.png

ここでDo untilが繰り返します。

falseの場合

変数JoinArrayと表内に存在する行を一覧表示のvalueをUnion 関数で結合します。

まずデータ操作からアクション作成を追加します。
image.png

入力に入れる構文は以下の通り。

変数JoinArrayと取得したテーブルをUnion
union(variables('JoinArray'),body('表内に存在する行を一覧表示')?['value'])

image.png

これで変数JoinArrayに格納済みのテーブルと、今回の繰り返しで取得した256行が結合されました。

最後にアクション作成の出力を変数JoinArrayに格納します。
image.png

ここでDo untilが繰り返します。

変数JoinArrayをアクション作成でラップする

無事Do untilが終了したら、アクション作成で変数JoinArrayをラップします。

image.png

これは変数のままだと他のアクションに渡せない場合があるので、その対策として行っています。

これで無事、データ取得数制限を回避してExcelテーブルを全件取得出来ました。
取得したExcelテーブルは配列になっているので下記のように好きに使っちゃいましょう。

おまけ:「設定」から改ページをONにして、しきい値を大きく設定してデータ全件取得する

アクションの「設定」から改ページをONにして、しきい値を大きく設定してもデータ全件取得できます。
image.png
image.png

但しこの方法はDo untilを使う方法に比べ、実行時間が倍近く長くなります。
参考に実行時間の比較を載せておきます。

  • 読み込んだExcelテーブル
    • 3059行
    • 39列

Do untilを使う方法の実行時間
image.png

改ページを使う方法の実行時間
image.png

どちらが良いか、用途に合わせて選んでください。

まとめ

Power Automateに限らず、「自分のしたい事を実現するにはどういうデータが必要か」を意識できれば自然と手段は見えてきます。

見えたらTry and Error を繰り返し、自身の知識とスキルをブラッシュアップしていきましょう。

来年も皆さんが充実したOffice 365 Lifeを送れますように。
No Data, No Life.

41
45
2

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
41
45