Forum Discussion
Anonymous
5 years agoNot applicable
Handling a Paginated API with Prebuilt Next Page URI in Response [EDITED]
I am trying to access data from the Asana API. When making a call to the api for say, "projects," GET /projects?limit=5&workspace=xxxxxxx Or in PBI, Json.Document(Web.Contents("https://app.a...
- 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!
KJanssens
4 years agoHelper II
For anyone looking for a way to limit the calls, when there are a lot of pages:
you can add a rowcount limit like below:
... SNIP ...
//add that data to rolling aggregate
data = List.Combine({data, newdata}),
datacount = List.Count(data),
//if theres no next page of data, return. if there is, call @gather again to get more data
check = if newReq[links] = null or datacount >= 10000 then data else @gather(data, newUri)
... SNIP ...in this case it will stop when 10000 rows have been fetched...