はじめに
Power AutomateでSPOリストから複数の項目を取得するには例えば以下の「複数項目の取得」アクションを利用します。
このアクションでは「フィルター クエリ」にてODataフィルタークエリを記載することで、取得するアイテムをフィルタすることができるわけですが、一部ちょっと沼ってしまったのと日本語で纏めてある記事がちょっと少ないな？と思ったので覚書程度に纏めます。
この記事で紹介しないこと
SPOリストの内部名などSPOリスト側の設定については触れません。
内部名は英語で作成するようにして、また内部名に使えないような単語は設定しないようにしようね！
また、ODataがなにかとかも触れていないです。
参考文献
公式ドキュメントとしては、このあたりが参考になるかな？
OData
この記事では「複数の項目を取得」アクションをベースにお話しします。
サポートされているODataフィルタークエリ
サポートされる OData のクエリ オプションをみてみるとサポートされているODataクエリを確認できます。
「フィルター クエリ」ではfilter=の箇所が対象となりますのでここが対象になりますね。
ただしこちらはあくまでもSharePoint REST API v1でサポートされているODataフィルタークエリですので、一部「複数の項目の取得」アクションではサポートされていないので注意が必要です。
* 後で触れます。
基本となるフィルター演算子
サポートされる演算子を簡単に纏めた感じです。
等しい
特定のフィールドの値が、特定の値と等しい行のみを取得したい場合は eq 演算子を利用します。
使用例
cityフィールドの値が'Rondon'である行にフィルター
フィルタークエリ
city eq 'Redmond'
_api/web/lists/getbytitle('[リスト名]')/items?$filter=city eq 'Redmond'
等しくない
特定のフィールドの値が、特定の値と等しくない行のみを取得したい場合は ne 演算子を利用します。
使用例
cityフィールドの値が'London'でない行にフィルター
フィルタークエリ
city ne 'London'
_api/web/lists/getbytitle('[リスト名]')/items?$filter=city ne 'London'
より大きい
特定のフィールドの値が、特定の値より大きい行のみ取得したい場合は gt 演算子を利用します。
使用例
priceフィールドの値が20より大きい行にフィルター
フィルタークエリ
price gt 20
_api/web/lists/getbytitle('[リスト名]')/items?$filter=price gt 20
以上
特定のフィールドの値が、特定の値以上の行のみ取得したい場合は ge 演算子を利用します。
使用例
priceフィールドの値が10以上の行にフィルター
フィルタークエリ
price ge 10
_api/web/lists/getbytitle('[リスト名]')/items?$filter=price ge 10
より小さい
特定のフィールドの値が、特定の値より小さい行のみ取得したい場合は lt 演算子を利用します。
使用例
priceフィールドの値が20より小さい行にフィルター
フィルタークエリ
price lt 20
_api/web/lists/getbytitle('[リスト名]')/items?$filter=price lt 20
以下
特定のフィールドの値が、特定の値以下の行のみ取得したい場合は le 演算子を利用します。
使用例
priceフィールドの値が10以下の行にフィルター
フィルタークエリ
price le 10
_api/web/lists/getbytitle('[リスト名]')/items?$filter=price le 10
論理 and
フィルター条件に複数の条件を指定し、その条件すべてに一致する行のみ取得したい場合は and 演算子を利用します。
使用例
priceフィールドの値が200以下かつ、priceフィールドの値が3.5より大きい行にフィルター
フィルタークエリ
price le 200 and price gt 3.5
_api/web/lists/getbytitle('[リスト名]')/items?$filter=price le 200 and price gt 3.5
論理 or
フィルター条件に複数の条件を指定し、その条件いずれかに一致する行のみ取得したい場合は or 演算子を利用します。
使用例
priceフィールドの値が3.5以下の列か、priceフィールドの値が200より大きい行にフィルター
フィルタークエリ
price le 3.5 or price gt 200
_api/web/lists/getbytitle('[リスト名]')/items?$filter=price le 3.5 or price gt 200
優先順位のグループ化
フィルター条件の演算子には四則演算と同様に優先順位があり、その優先順位をグループ化することができます。(後述)
これを行うには () 演算子を利用します。
使用例
priorityフィールドの値が1であるか、cityフィールドの値がRedmondである列かつ、priceフィールドが100より大きい行にフィルターします。
フィルタークエリ
(priority eq 1 or city eq 'Redmond') and price gt 100
_api/web/lists/getbytitle('[リスト名]')/items?$filter=(priority eq 1 or city eq 'Redmond') and price gt 100
演算子の優先順位
演算子の優先順位参照。
フィルター条件の演算子には四則演算と同様に優先順位があり、優先順位に従ってクエリは評価されます。
同じグループの演算子は左にあるクエリの方が優先順位が高くなり、右に行くほど低くなります。
| グループ | 演算子 | 説明 |
|---|---|---|
| グループ化 | ( ) | 優先順位のグループ化 |
| リレーショナル | gt | より大きい |
| ge | 以上 | |
| lt | より少ない | |
| le | 以下 | |
| 等価 | eq | 等しい |
| ne | 等しくない | |
| 条件付き AND | and | 論理 and |
| 条件付き OR | or | 論理 or |
文字列演算子
SharePoint REST APIでは現在以下2つのAPIがサポートされています。
startswith
特定のフィールドの値が、特定の値で始まる行のみ取得したい場合は startswith 演算子を利用します。
使用例
Nameフィールドの値が'Ai'で始まる行にフィルター
フィルタークエリ
startswith(Name, 'Ai')
_api/web/lists/getbytitle('[リスト名]')/items?$filter=startswith(Name, 'Ai')
substringof
特定のフィールドにて、特定の値が含まれている行のみ取得したい場合は substringof 演算子を利用します。
使用例
Nameフィールドの値に'Ai'で含まれる行にフィルター
フィルタークエリ
substringof(Name, 'Ai')
_api/web/lists/getbytitle('[リスト名]')/items?$filter=substringof(Name, 'Ai')
上記以外の文字列演算子について
ODataでのフィルター演算子というと、上記のほかに endswith や length , tolower , toupper , trim などなどありますが、これらは現在SharePoint REST APIではサポートされていないようなのでご注意ください。
特に endswith は startswith がサポートされているので、できるかな？と考えがちですが、サポートされていないようなのでご注意ください。
日付固有の関数
- day
- month
- year
- hour
- minutes
- second
がサポートされているみたいな書き方されていますが、こいつら使えないです。
ここのissuesが本件に該当するスレッドですかね。
解決していないのにmsftbotによって、会話がないからという理由でclosedされていますね。
これサポートされる未来はくるんですかね。
色々な列の種類でのフィルター
フィールド名はそれっぽい名前にしているので各自読み替えてください。
1行テキスト
Title eq 'Sample'
数値
Number eq 100
選択肢(単一)
選択肢フィールドはオブジェクト形式で取得できますが、列名だけの指定でフィルター可能です。
Choice eq 'Choice-A'
選択肢(複数)
複数選択可能な選択肢でも単一アイテムのみ選択可能な選択肢列とフィルター方法は同じになります。
Choices eq 'Choices-A'
例えば、選択肢として「Choices-A、Choices-B、Choices-C」が選択可能で、「Choices-A と Choices-B」が選択されたアイテムのみにフィルターしたい場合は以下のようにします。
Choices eq 'Choices-A' and Choices eq 'Choices-B' and Choices ne 'Choices-C'
and Choices ne 'Choices-C' がないと、「Choices-A と Choices-B」が選択されたアイテムだけでなく、「Choices-A と Choices-B と Choices-C」というアイテムまで選択されてしまうので注意してください。
日付
日付をフィルターする場合は yyyy-mm-dd 形式でフィルター条件を記載します。
Date le '2022-12-01'
ちなみにPower Automateの関数利用する場合は以下。
Date ge '@{utcNow()}'
日付と時刻
時刻まで含めてフィルターする場合は yyyy-mm-ddTHH:MM:SS 形式で指定します。
yyyy-mm-ddTHH:MM:SSZ のように末尾にZを付けるとUTCでフィルターされるので注意。
また、SPOリストは秒までは保持しないので、SSの秒の箇所は00となりますね。
DateTime le '2022-01-23T13:09:00'
はい/いいえ
はい/いいえ はBoolean(true or false)フィールドですが、 Boolean eq true などではフィルターできません。
はい(Yes)のみにフィルターしたい場合は1を指定します。
いいえ(false)のみにフィルターしたい場合は0を指定します。
Boolean eq 1
ユーザー
ユーザーフィールドは選択肢フィールド同様オブジェクト形式で取得できますが、選択肢と同じように列名だけ指定する方法ではフィルターできません。
フィルターするにはユーザーのメールアドレスを指定する必要があります。
ということで、実行結果より User/Email eq 'メールアドレス' としたくなるかもしれませんが、この指定方法では以下のようなエラーとなります。
1 つ以上のフィールドの種類が正しくインストールされていません。リストの設定ページに移動して、これらのフィールドを削除してください。
正しくは以下のように指定します。
User/EMail eq 'メールアドレス'
ここで沼った。
正直これ書くためにブログ書いてるまである。
User/EMail のように"EMail"の"M"は大文字にする必要があるんですね。
イメージ
サポートしてない(はず)です。
添付ファイル
添付ファイル名でのフィルターはできないです。
できるのは、添付ファイルが存在するかどうか？ですね。
添付ファイルありのみにフィルターしたい場合は以下のように指定します。
Attachments eq 1
{HasAttachments} eq 1 とかではないので注意.。
おわりに
ざっくりPower AutomateのSPOコネクタでのODataフィルタークエリまとめでした。
といいつも一部SharePoint REST APIでの指定方法も記載しましたが。
ミスなどあればご指摘くださいー。
