🎃

Power Automate と SharePoint REST API を使ってリストアイテムの特定の列の値を集計して取得する

2023/01/09に公開

SharePoint

Power Automate

tech

SharePoint をデータソースとしたアプリやフローでは、場合によってはそのリストアイテムの列の値を集計したいということがあると思います。
今回は、その集計結果を API から取得する方法を記載したいと思います。

ちなみに本件では、CAML というものを使用します。その書き方について詳しく知りたいという場合は、参考リンクを確認されるか、画面からいろいろ条件を指定し作成した標準のビューの定義情報を REST API などを使って取得し、中身を見比べてみることをお勧めします。

事前準備

以下のような、数値列(Number01-03)をもったカスタムリストを用意します

作業手順

Power Automate クラウドフローを使って SharePoint REST API を実行したいと思います。

実行用のフローを作成する

全体像は以下のような感じです。

集計結果を取得するための CAML を作成する

まず、今回の REST API を実行するのに必要な CAML というものを作成します。
作成アクションで作成していますが、変数でもなんでもよいです。
以下に中身を記載します。
今回は条件として、タイトル列に A または B または F を含むものを対象に、Number 列をそれぞれ合計するような指定をしています。

<View>
  <ViewFields>
    <FieldRef Name=\"ID\" />
    <FieldRef Name=\"Title\" />
    <FieldRef Name=\"Number01\" />
    <FieldRef Name=\"Number02\" />
    <FieldRef Name=\"Number03\" />
  </ViewFields>
  <Query>
    <Where>
      <Or>
        <Contains>
          <FieldRef Name=\"Title\" />
          <Value Type=\"Text\">A</Value>
        </Contains>
        <Or>
          <Contains>
            <FieldRef Name=\"Title\" />
            <Value Type=\"Text\">B</Value>
          </Contains>
          <Contains>
            <FieldRef Name=\"Title\" />
            <Value Type=\"Text\">F</Value>
          </Contains>
        </Or>
      </Or>
    </Where>
    <OrderBy>
      <FieldRef Name=\"ID\" Ascending=\"TRUE\" />
    </OrderBy>
  </Query>
  <RowLimit>1</RowLimit>
  <Aggregations Value=\"On\">
    <FieldRef Name=\"ID\" Type=\"COUNT\" />
    <FieldRef Name=\"Number01\" Type=\"SUM\" />
    <FieldRef Name=\"Number02\" Type=\"SUM\" />
    <FieldRef Name=\"Number03\" Type=\"SUM\" />
  </Aggregations>
</View>

SharePoint に HTTP 要求を送信するアクションで REST API を実行する

次に、先ほど作成した CAML を使った API を実行します。

項目	設定値
サイトのアドレス	{対象のカスタムリストを配置したサイト}
方法	POST
URI	`_api/lists/getbytitle('SpHttpReqAggregateTest')/RenderListDataAsStream`
ヘッダー	Accept : application/json;odata=nometadata, Content-Type : application/json
ボディ	以下に記載

{
  "parameters": {
    "ViewXml": "{直前のアクションで作成した CAML}"
  }
}

実行結果を確認する

以下のような数式を使って、実行結果を確認します。
作成アクションで作成していますが、変数でもなんでもよいです。

数式の例

対象件数 : @{first(outputs('SharePoint_に_HTTP_要求を送信します')?['body']?['ROW'])?['ID.COUNT']}
Number01合計 : @{first(outputs('SharePoint_に_HTTP_要求を送信します')?['body']?['ROW'])?['Number01.SUM']}
Number02合計 : @{first(outputs('SharePoint_に_HTTP_要求を送信します')?['body']?['ROW'])?['Number02.SUM']}
Number03合計 : @{first(outputs('SharePoint_に_HTTP_要求を送信します')?['body']?['ROW'])?['Number03.SUM']}

実行結果

注意事項

公式ドキュメントは発見できていませんが、集計対象が 5,000件程度まででないと動作しないと思われます
CAML に関して、集計を含むものを作成する場合は RowLimit は 1 でよいので必ず指定してください

参考

SharePoint REST API の RenderListDataAsStream の概要

CAML の概要

感想

以前は、～lists/{listid}/getitems エンドポイントに対して CAML を POST することで、複雑な検索要件を必要とする処理を行っていたのですが、公式ドキュメントに～lists/{listid}/RenderListDataAsStream があるので、こちらを使うのが正しいような気がします。
CAML を使えば、ME (実行者を指定)や TODAY (実行当日を指定)といった動的な要素を検索条件を含めることができたり、URL パラメータが長くなってしまうことが防げたりするので、いろいろな場合で使えると思います。
また、合計だけでなく、平均や最大値、最小値といった SharePoint 標準ビューで用意されている他の集計方法も選択することができます。