kintoneからpowerqueryで1万件越えのレコードを取得する

概要

kintoneから複数のレコードを取得する際の上限は1万件ですが、1万件程度であれば超えてほしい場面は頻繁にあります。
自分の使いなれている言語であればパパっと実装できることがほとんどですが、powerqueryは慣れていないためちょっと手間取ったので備忘録です。

(appId as text, apiToken as text, query as text, fields as list) as table =>
    let
        domain = "htpps://example.cybozu.com",
        pageSize = 100,
        headers = [#"X-Cybozu-API-Token"=apiToken],

        GetPage = (lastId as nullable text) =>
            let
                queryBase = if lastId = null then "" else "$id > " & lastId,
                query = queryBase & "order by $id asc limit" & Text.From(pageSize),
                actualFields = List.Distinct(List.Combine({fields, {"$id"}})),
                tempQuery = Un.BuildQueryString([
                    app = appId,
                    query = query,
                    fields = Text.Combine(actualFields, ",")
                ]),
                url = domain & "/k/v1/records.json?" & tempQuery,
                source = Json.Document(Web.Contents(url, [Headers = headers])),
                records = source[recirds]
            in
                records,
                
        GetAllPages = (seed as list, acc as list) =>
            let 
                lastRecord = if List.COunt(seed) > 0 then List.Last(seed) else null,
                lastId = id lastRecord <> null and Record.HasFields(lastRecord, "$id")
                    then Text.From(Record.Fields(lastRecord, "$id")[value])
                    else null,
                nextPage = if List.Count(seed) = pageSize then GetPage(lastId) else {},
                newAcc = List.Combine({acc, seed}),
                result = if List.Count(nextPage) = 0 then newAcc else @GetAllPages(nextPage, newAcc)
            in
                result,
        initialPage = GetPage(null),
        allRecords = GetAllPages(initialPage, {}),
        listBuffer = List.Buffer(allRecords),
        TransformKintoneRecord = (reec as record) as record =>
            Record.TransformFields(rec,
                List.TransformFields(rec,
                    List.Transform(Record.FieldNames(rec), (fname) =>
                        {
                            fname,
                            (val) =>
                                if val[type] = "SUBTABLE" then
                                    List.Transform(val[value], each
                                        Record.TransformFields(_[value],
                                            List.Transform(
                                                Record.FieldNames(_[value]),
                                                (sfname) => {sfname, each _[value]}
                                            )
                                        )
                                    )
                                else
                                    val[value]
                        }
                    )
                )
            )
        ,
        cleanedRecords = List.Transform(listBuffer, each TransformKintoneRecord(_))
    in Table.FromRecords(cleanedRecords)