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!
Anonymous
3 years agoNot applicable
Hi, Nope, i do all the work with the APIkey, would definately reccomend you to do the same.
JackSelman
3 years agoHelper I
Thanks Anonymous! Sorry if I'm looking at the wrong one, but I thought API keys were being sunsetted?