PowerQuery: Sharepoint Rest API の @OData.nextlink を処理する関数を作ってみた。$skiptoken で P_ID が使えるなら使える筈

# 背景

Power Query/BI で、SharePoint REST でデータを取るときに面倒なのが・・

1000 件以上を取得する際に @OData.nextlink を処理しないといけないこと。

で、これを楽にしたいなと思って関数を作ってみたのでその備忘録

https://learn.microsoft.com/ja-jp/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service?tabs=csom

# 概要

以下のコードを空のクエリに貼り付けて、後は Call してやれば OK

<summary>RetreiveRestAPI()<details>

```powerquery: RetreiveRestAPI()

(siteUrl as text, relativePath as text, optional startId as number, optional top as number) => 
let        
    beginId = if startId = null then 0 else startId - 1,
    rowCountInAPage = if top = null then 500 else top,
    retreiveList = (siteUrl as text, relativePath as text,  finishedId as number, top as number) => 
        let
            retreivedList = try Function.InvokeAfter(
                ()=> Xml.Tables(Web.Contents(
                    siteUrl,
                    [
                        RelativePath = relativePath,
                        Query=[
                            #"$skiptoken" = "Paged=TRUE&p_ID=" & Text.From(finishedId),
                            #"$top" = Text.From(top)
                    ]]
                )),
                #duration(0,0,0,0.1)
            )
            otherwise null,
            entry = retreivedList{0}[entry],
            content = Table.ExpandTableColumn(entry, "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"metadata"}),
            metadata = Table.ExpandTableColumn(content, "metadata", {"properties"}, {"properties"}),
            properties = Table.ExpandTableColumn(metadata, "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"data"}),
            result = Table.SelectColumns(properties,{"data"}),
            checkAttribute = try retreivedList[link]{0}{0}[#"Attribute:rel"] otherwise null,
            hasNext = if checkAttribute = "next" then true else false
        in 
            [
                result = result, 
                lastId = Number.From(Table.Last(result)[data][Id]{0}{0}[#"Element:Text"]),  // $skiptoken で指定する p_ID
                hasNext = hasNext,  // @OData.nextlink があるか？
                nextLink = retreivedList[link]{0}{0}   // @OData.nextlink の確認用。p_ID ではなく、Tokenの可能性もあるので。
            ]
    ,
    retreivedPagenationList = List.Generate(
        () => [
            firstData = retreiveList(siteUrl, relativePath, beginId, rowCountInAPage), 
            data = firstData[result],
            id = firstData[lastId],
            hasCurrent = true,
            hasNext = firstData[hasNext]
        ],
        each  [hasCurrent], 
        (state) => 
            let
                nextList = retreiveList(siteUrl, relativePath, state[id], rowCountInAPage),
                nextId = nextList[lastId],
                hasCurrent = state[hasNext],
                hasNext = Logical.From(nextList[hasNext])
            in
                [data = nextList[result], id = nextId, hasCurrent = hasCurrent, hasNext = hasNext],
        each [data]
    ),
    result = Table.Combine(retreivedPagenationList)
in
    result

```

</details></summary>

## 実際の利用例

こんな感じで、Base URL と パラメーターを指定してやると・・
※paging が起こるように、top を小さくしてますが、省略した場合 500 を利用

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/e7f2e24d-5231-5f74-6205-7339c14a17c0.png)

あとは、展開するだけ。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/8e88efd8-5fcb-cbcf-0edc-95e8b39f5d06.png)

:::note

null が Table となって取れてしまうので、Value.Type で条件式を使って適当に置換は必要かも

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/36419e42-891a-b40f-452e-3339c1a0b379.png)

:::

# 詳細

SharePoint の @OData.nextlink の場合、$skiptoken は、token ではなく、ID で飛ばせる（全部かどうかは知らないが・・:thinking: 

```url: 実際の例（デコード済み）
$skiptoken=Paged=TRUE&p_ID=2&$top=2
```

$skiptoken については以下

https://learn.microsoft.com/ja-jp/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests#page-through-returned-items

ということで、以下の方針で作成してみた

1. API Call して、取得した最後（最大）の ID で、再度取得
   - 現状 ID 昇順ソートで取れてたので、List.Max は使ってない
1. nextlink が取得出来る間は List.Generate() でループ
1. 最後に、Table.Combine で合成

取得した Table 構成は items や fields などによって違うので、展開は呼び出し側にお任せ

nextlink が無い場合なんかもあるので、その場合は、内部関数だけで処理して確認が必要

<summary>内部関数の結果を取得した状態<details>

```powerquery: RetreiveRestAPI - 内部関数結果取得版

(siteUrl as text, relativePath as text, optional startId as number, optional top as number) => 
let        
    beginId = if startId = null then 0 else startId - 1,
    rowCountInAPage = if top = null then 500 else top,
    retreiveList = (siteUrl as text, relativePath as text,  finishedId as number, top as number) => 
        let
            retreivedList = try Function.InvokeAfter(
                ()=> Xml.Tables(Web.Contents(
                    siteUrl,
                    [
                        RelativePath = relativePath,
                        Query=[
                            #"$skiptoken" = "Paged=TRUE&p_ID=" & Text.From(finishedId),
                            #"$top" = Text.From(top)
                    ]]
                )),
                #duration(0,0,0,0.1)
            )
            otherwise null,
            entry = retreivedList{0}[entry],
            content = Table.ExpandTableColumn(entry, "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"metadata"}),
            metadata = Table.ExpandTableColumn(content, "metadata", {"properties"}, {"properties"}),
            properties = Table.ExpandTableColumn(metadata, "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"data"}),
            result = Table.SelectColumns(properties,{"data"}),
            checkAttribute = try retreivedList[link]{0}{0}[#"Attribute:rel"] otherwise null,
            hasNext = if checkAttribute = "next" then true else false
        in 
            [
                result = result, 
                lastId = Number.From(Table.Last(result)[data][Id]{0}{0}[#"Element:Text"]),  // $skiptoken で指定する p_ID
                hasNext = hasNext,  // @OData.nextlink があるか？
                nextLink = retreivedList[link]{0}{0},   // @OData.nextlink の確認用。p_ID ではなく、Tokenの可能性もあるので。
                next = retreivedList
            ]
    ,
    retreivedPagenationList = List.Generate(
        () => [
            firstData = retreiveList(siteUrl, relativePath, beginId, rowCountInAPage), 
            data = firstData[result],
            id = firstData[lastId],
            hasCurrent = true,
            hasNext = firstData[hasNext]        ],
        each  [hasCurrent], 
        (state) => 
            let
                nextList = retreiveList(siteUrl, relativePath, state[id], rowCountInAPage),
                nextId = nextList[lastId],
                hasCurrent = state[hasNext],
                hasNext = Logical.From(nextList[hasNext])
            in
                [data = nextList[result], id = nextId, hasCurrent = hasCurrent, hasNext = hasNext],
        each [data]
    ),
    result = Table.Combine(retreivedPagenationList)
in
    retreiveList(siteUrl, relativePath, beginId, rowCountInAPage)

```

</details></summary>

# あとがき

あとは、Sharepoint REST で色々使ってみて問題があったら修正していこう :laughing: