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

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

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

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

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

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

例えばExcel Online (Business)コネクタのアクション[表内に存在する行を一覧表示](https://docs.microsoft.com/ja-jp/connectors/excelonlinebusiness/#list-rows-present-in-a-table)は、一度に**256行**までしか取得できません。
(2019/12/13現在)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/0351186c-6248-4eaf-3638-2f16911d5e3b.png)

そしてSharePointコネクタのリストを読み出すアクション[複数の項目の取得](https://docs.microsoft.com/ja-jp/connectors/sharepointonline/#get-items)は、一度に**1000アイテム**が制限値です。
(2019/12/13現在)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/f23057e4-2e3d-8420-f87f-9494897745a7.png)

#Do until
それらを解決するのが**Do until**です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/2a311a66-9d96-9bb8-6e8c-3fd225411978.png)
Do untilは**設定した条件が`true`になるまで処理を繰り返し実行します。**

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

ロジックはこんな感じ。
![PowerAutomateからExcelテーブルを読み出す.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/ccc5fe57-3449-6c87-aaa3-c0c54bfa0edf.png)

#前置き
この記事はExcelテーブルを[表内に存在する行を一覧表示](https://docs.microsoft.com/ja-jp/connectors/excelonlinebusiness/#list-rows-present-in-a-table)で取得する前提で話を進めます。

必要に応じて[表内に存在する行を一覧表示](https://docs.microsoft.com/ja-jp/connectors/excelonlinebusiness/#list-rows-present-in-a-table)を任意のアクションへ、Excelの制限値`256`を任意の値に置き換えて読んで下さい。

#実装
##下準備
まず変数を三つ初期化します。

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/da81461b-8c39-df0f-3f93-1bf668b4cec4.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/43a62c41-41a0-d63d-88f8-563f9007ce4a.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/a1904af9-dfd3-eb05-80d5-45a8e0beef6c.png)

##Do untilの設定
組込みの制御からDo untilをフローに追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/80e154fa-9d13-b34a-1094-79e0436085dc.png)
値の選択には**変数`DataEmpty`を設定。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/8816f100-5574-e42b-2320-1bb340ec90f6.png)
これで**`DataEmpty=true`になるまで処理を繰り返します。**

###Excelテーブルの読み出し
Do untilにはまずアクション[表内に存在する行を一覧表示](https://docs.microsoft.com/ja-jp/connectors/excelonlinebusiness/#list-rows-present-in-a-table)を追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/b8e516a4-d199-14a8-3a3d-957a5baebdee.png)

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

```C#:スキップ数
mul(256,variables('LoopCount'))
```

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

###Excelテーブルを全て取得済みか判定
次にアクション[表内に存在する行を一覧表示](https://docs.microsoft.com/ja-jp/connectors/excelonlinebusiness/#list-rows-present-in-a-table)の**応答が空(empty)かどうかを判定します。**

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

まず制御からアクション条件を追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/a7b1753a-c432-aadd-324e-fb78e0abfed2.png)

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

```C#:表内に存在する行を一覧表示の応答は空？
empty(body('表内に存在する行を一覧表示')?['value'])
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/bd3aee0a-7022-3da2-d323-2bf6c508b4ad.png)

`body('表内に存在する行を一覧表示')?['value']`がアクション[表内に存在する行を一覧表示](https://docs.microsoft.com/ja-jp/connectors/excelonlinebusiness/#list-rows-present-in-a-table)の応答なので、[empty 関数](https://docs.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference#empty)を使う事で空かどうか判定しています。

####empty=trueの場合
**変数`DataEmpty`を`true`に設定します。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/103200b6-0a5b-4f78-73ff-7ca4df8af8f3.png)

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

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

- 変数`LoopCount`を`+1`
- 変数`JoinArray`の処理
    - **初回**
        - 変数`JoinArray`にアクション[表内に存在する行を一覧表示](https://docs.microsoft.com/ja-jp/connectors/excelonlinebusiness/#list-rows-present-in-a-table)の応答から`value`を格納する
    - **2回目以降**
        - 変数`JoinArray`と[表内に存在する行を一覧表示](https://docs.microsoft.com/ja-jp/connectors/excelonlinebusiness/#list-rows-present-in-a-table)の`value`を[Union 関数](https://docs.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference#union)で結合し、改めて`JoinArray`に格納する

#####変数LoopCountを+1する
まず変数`LoopCount`を`+1`します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/ce2a6b8a-1ed8-0b3b-85b1-fea951c0bc0a.png)
これで**[Excelテーブルの読み出し](https://qiita.com/Yellow11/items/97e3a142c7510c8378eb#excel%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E8%AA%AD%E3%81%BF%E5%87%BA%E3%81%97)で設定したスキップ数が変動し、取得済みの行が自動でスキップされます。**

#####変数JoinArrayに取得したテーブルを格納する
**初回と2回目以降では処理が異なるので条件を設定します。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/409733b2-bc08-1187-c3ab-def24aaaee0f.png)

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

```C#:この処理が初回か否か
empty(variables('JoinArray'))
```

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

######trueの場合
変数`JoinArray`にアクション[表内に存在する行を一覧表示](https://docs.microsoft.com/ja-jp/connectors/excelonlinebusiness/#list-rows-present-in-a-table)から`value`を格納します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/e23d279d-1ea7-79a1-b2a7-13557375f42e.png)

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

######falseの場合
変数`JoinArray`と[表内に存在する行を一覧表示](https://docs.microsoft.com/ja-jp/connectors/excelonlinebusiness/#list-rows-present-in-a-table)の`value`を**[Union 関数](https://docs.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference#union)で結合します。**

まずデータ操作からアクション`作成`を追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/d17d7804-f422-0eec-347b-4cbca65570d2.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/3234d5b2-3863-582f-d719-1da60a1809a1.png)

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

最後にアクション`作成`の出力を変数`JoinArray`に格納します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/58397289-2dc7-e090-a726-a267ada31821.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/ae875a99-afc6-fd96-c078-fe9394f8f28a.png)

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

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

- 他のコネクタにそのまま渡す
- Apply to each でSharePointに登録する
- [標準コネクタのみで、Power AutomateからPower Appsにテーブルを送信する](https://qiita.com/Yellow11/items/f3eae092ba796c81e8b8)

#おまけ:「設定」から改ページをONにして、しきい値を大きく設定してデータ全件取得する
**アクションの「設定」から改ページをONにして、しきい値を大きく設定してもデータ全件取得できます。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/cd992643-751c-8c2c-2f50-31a80aa3bb72.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/9abb1463-ab3c-b6f5-af91-3235e1937185.png)

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

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

Do untilを使う方法の実行時間
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/d78617d8-f42c-d8c5-6eb7-b6c8e9a7149b.png)

改ページを使う方法の実行時間
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/356adf9b-e3d9-a325-d605-5de5c404d8b2.png)

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

#まとめ
- **一度に取れないなら繰り返せばいい**
    - 結果が同じなら手段は一つではありません
- **変数をうまく使おう**
    - Power Automateを便利に使うなら絶対に避けて通れません
- **場合に応じて関数を使おう**
    - Azure Logic Appsに関数のリファレンスがあるので、それを読んで勉強しよう
    - [Azure Logic Apps および Power Automate の式で関数を使用するためのリファレンス ガイド](https://docs.microsoft.com/ja-jp/azure/logic-apps/workflow-definition-language-functions-reference#length)
- **JSONにもっと馴染もう**
    - JSON完全に理解した ~~申し訳程度のJSON要素~~
    - 縄神様こと小尾さんが、Power Automate 上での JSON 値の扱い方に関して色々解説してくれてます
    - [JSON Value into Power Automate](https://www.slideshare.net/TomoyukiObi/json-value-into-power-automate)

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

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

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