背景

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

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

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

概要

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

RetreiveRestAPI()

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

実際の利用例

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

あとは、展開するだけ。

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

詳細

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

実際の例（デコード済み）

$skiptoken=Paged=TRUE&p_ID=2&$top=2

$skiptoken については以下

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

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

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

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

内部関数の結果を取得した状態

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)

あとがき

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