Forum Discussion
Handling a Paginated API with Prebuilt Next Page URI in Response [EDITED]
- Anonymous5 years ago
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,
I am a newbie to API paginations. This code is the easiest to follow on pagination i have found.
When i copy the code and update the credentials, i get the below error. Please help!!
Thanks, Bianca
- Anonymous4 years agoNot applicable
Hey Bianca, not all APIs are built the same! Depending on the API, the output json will have a different structure and may not have a "next_page" field. You need to figure out how your API handles pagination and modify the code accordingly. Add some details about the API youre using and maybe I can help