I want to get data from the API described in the following path using powerquery
https://kintone.dev/en/docs/kintone/rest-api/records/get-cursor/
The API will use a single parameter is the cursor ID. Then will call many times the same url until
next value in response equals false.
Here's how I implemented it. But the result is not what I expected
1. Generate cursor id (can retrieve 500 records after 1 call)
2. Implement an API call function
Get_Records_With_Cursor_Page = (enpoint as text, cursorID as text, #"Password Auth", HTTP_Errors as table) =>
let
Options = [Headers=[#"Cache-Control" = "no-cache, no-store, must-revalidate", #"X-Cybozu-Authorization" = #"Password Auth"], ManualStatusHandling = HTTP_Errors[ErrorCode]],
RequestUrl = enpoint & "/k/v1/records/cursor.json?" & "id=" & cursorID,
RawData = Web.Contents(RequestUrl, Options),
Metadata = Value.Metadata(RawData),
ResponseCode = Metadata[Response.Status],
Response = if List.Contains(HTTP_Errors[ErrorCode], ResponseCode) then
HTTP_Errors[ErrorMsg]{List.PositionOf(HTTP_Errors[ErrorCode], ResponseCode)}
else if ResponseCode = 200 then Json.Document(RawData)
else Extension.LoadString("error"),
data = Response[records],
next = Response[next]
in
data meta [Next = next];
3. I tried implementing with pagination solution but the result is pages with same content and same as first API call
CheckNextPage = (response) as logical =>
let
flagNext = response[next]
in
flagNext;
Table.GenerateByPage = (getNextPage as function) as table =>
let
listOfPages = List.Generate(
() => getNextPage(null), // get the first page of data
(lastPage) => (lastPage) <> null, // stop when the function returns null
(lastPage) => getNextPage(lastPage) // pass the previous page to the next function call
),
tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"RecordsData"}),
firstRow = tableOfPages{0}?
in
// if we didn't get back any pages of data, return an empty table
// otherwise set the table type based on the columns of the first page
if (firstRow = null) then
Table.FromRows({})
// check for empty first table
else if (Table.IsEmpty(firstRow[RecordsData])) then
firstRow[RecordsData]
else
Value.ReplaceType(
Table.ExpandTableColumn(tableOfPages, "RecordsData", Table.ColumnNames(firstRow[RecordsData])),
Value.Type(firstRow[RecordsData])
)
GetAllPages = (enpoint as text, cursorID as text, #"Password Auth", HTTP_Errors as table) =>
Table.GenerateByPage((previous) =>
let
nextPage = if (previous = null) then null else Value.Metadata(previous)[Next]?,
page = if nextPage = null or nextPage = true then Get_Records_With_Cursor_Page(enpoint, cursorID, #"Password Auth", HTTP_Errors) else null
in
page
4. I also tried entering all the parameters into the function in step 2 and using the Invoke function several times, the invork results are the same and the same as the first call.
And when I don't use the Invoke function, but instead I refresh the query many times, the results after each refresh will get the results of each consecutive data page until the end.
I have a question why when calling the function many times can return data for the first page?
I wonder if my implementation calls the API multiple times or not? And how to call multiple times and get all data with this API
Please help me if you guys have any information or solution about this problem. Thank you!
