Forum Discussion
Handling a Paginated API with Prebuilt Next Page URI in Response [EDITED]
Solved it using a custom recursive function which grabs the offset, builds the next page's uri, appends the data to a rolling total of the data, then makes another call with the new uri, provided a next page exists to be called upon.
(baseuri as text) => let headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer APIKEY"]], initReq = Json.Document(Web.Contents(baseuri, headers)), initData = initReq[data], //We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records, //then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then //create a table from those records gather = (data as list, uri) => let //get new offset from active uri newOffset = Json.Document(Web.Contents(uri, headers))[next_page][offset], //build new uri using the original uri so we dont append offsests newUri = baseuri & "&offset=" & newOffset, //get new req & data newReq = Json.Document(Web.Contents(newUri, headers)), newdata = newReq[data], //add that data to rolling aggregate data = List.Combine({data, newdata}), //if theres no next page of data, return. if there is, call @gather again to get more data check = if newReq[next_page] = null then data else @gather(data, newUri) in check, //before we call gather(), we want see if its even necesarry. First request returns only one page? Return. outputList = if initReq[next_page] = null then initData else gather(initData, baseuri), //then place records into a table. This will expand all columns available in the record. expand = Table.FromRecords(outputList) in expandThis returns a fully expanded table of records from from all pages of data.
Extensions of functionality or efficieny modifications are more than welcome!
Solved it using a custom recursive function which grabs the offset, builds the next page's uri, appends the data to a rolling total of the data, then makes another call with the new uri, provided a next page exists to be called upon.
(baseuri as text) =>
let
headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer APIKEY"]],
initReq = Json.Document(Web.Contents(baseuri, headers)),
initData = initReq[data],
//We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records,
//then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then
//create a table from those records
gather = (data as list, uri) =>
let
//get new offset from active uri
newOffset = Json.Document(Web.Contents(uri, headers))[next_page][offset],
//build new uri using the original uri so we dont append offsests
newUri = baseuri & "&offset=" & newOffset,
//get new req & data
newReq = Json.Document(Web.Contents(newUri, headers)),
newdata = newReq[data],
//add that data to rolling aggregate
data = List.Combine({data, newdata}),
//if theres no next page of data, return. if there is, call @gather again to get more data
check = if newReq[next_page] = null then data else @gather(data, newUri)
in check,
//before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
outputList = if initReq[next_page] = null then initData else gather(initData, baseuri),
//then place records into a table. This will expand all columns available in the record.
expand = Table.FromRecords(outputList)
in
expandThis returns a fully expanded table of records from from all pages of data.
Extensions of functionality or efficieny modifications are more than welcome!
Hi Anonymous , this looks like something I could use in my case.
I have borrowed your script and applied it to the API im working with, but I'm hitting a wall when the looping is finished.
I suspect this has something to do with the fact that when there are no more results the API does not return the "next_page" part of the JSON, its just not there.
When running the API with a limit of 1, the paging is there
When running with a limit of 100 (there are 33 rows in the demo database) the "paging" part is gone
I have played around with a try/otherwise solution, but it just wont work.
Did you have the same issue?
Wes
This isnt something I had to deal with because the API I was using returns "next_page: null" once we've exhausted all the data instead of just returning nothing. That said, all you should need to do is modify my stop condition
check = if newReq[next_page] = null then data else @gather(data, newUri)
and the initial request checker
outputList = if initReq[next_page] = null then initData else gather(initData, baseuri),
to check if the paging key is contained in the json. You might be able to do it with error handling in the way youve been trying, but off the top of my head, you could also try and cast the json to regular text then use Text.Contains to see if ""paging": {" exists as a substring.
Thanks, yes I mended the stop condition to look for the "paging" column as shown below, and the query run without errors now.
But its not returning more than 100 rows still.
The List.Contains ( Table.ColumnNames() part works by itselft in a standalone query, but not sure how to check if it works inside the stop condition inside the "gather" function.
Do you see any major flaws?
let baseuri = "https://api.hubapi.com/crm/v3/objects/contacts limit=100&archived=false&hapikey=" & apikey , //headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer " & apikey ]], initReq = Json.Document(Web.Contents(baseuri)), #"Converted to Table" = Record.ToTable(initReq), initData = initReq[results], //We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records, //then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then //create a table from those records gather = (data as list, uri) => let //get new offset from active uri newOffset = Json.Document(Web.Contents(uri))[paging][next][after], //build new uri using the original uri so we dont append offsests newUri = baseuri & "&after=" & newOffset, //get new req & data newReq = Json.Document(Web.Contents(newUri)), newdata = newReq[results], //add that data to rolling aggregate data = List.Combine({data, newdata}), //if theres no next page of data, return. if there is, call @gather again to get more data check = if List.Contains ( Table.ColumnNames(newReq as table), "paging" ) = true then @gather(data, newUri) else data in check, //before we call gather(), we want see if its even necesarry. First request returns only one page? Return. outputList = if List.Contains (Table.ColumnNames( #"Converted to Table" as table) , "paging" ) = true then gather(initData, baseuri) else initData, //then place records into a table. This will expand all columns available in the record. expand = Table.FromRecords(outputList) in expandHi there Anonymous dide you manage to solve this? I'm trying to build a paginated call to the HubSpot API too. I've tried your code but only returned 100 records.
