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!
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
expand
Hi 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.
Hi JackSelman , was just working with a project on the Hubspot API.
This is the code currently working for me. Let me know if it works for you.
let baseuri = baseurlContacts & apikey, initReq = Json.Document(Web.Contents(baseuri)), 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 Table.Contains ( Record.ToTable(newReq) , [Name = "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 Table.Contains ( Record.ToTable (initReq) , [Name = "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 expand@
Thanks for sharing this Anonymous! I'm struggling to get this to work as I'm not using an API key, but a private access token, so there's no key parameter to include. I'm getting stick when specifying the Header Authorization = Bearer, and so I can't even authenticate
let baseuri = "https://api.hubapi.com/crm/v3/objects/line_items?limit=100&archived=false&properties=name&properties=folder&properties=sku&properties=product_type&properties=hs_sku&properties=amount&properties=quantity", headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer PAT"]], initReq = Json.Document(Web.Contents(baseuri)), 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 Table.Contains ( Record.ToTable(newReq) , [Name = "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 Table.Contains ( Record.ToTable (initReq) , [Name = "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 expand"PAT" part.
I don't suppose this is an issue you've encountered?Hi, Nope, i do all the work with the APIkey, would definately reccomend you to do the same.
Brilliant wes2015. I've been beating my head against a wall trying to use the List.Generate() function but not matter what I did I could not find a method that tested the existance of the "next page" content so it fell over after retrieving the last page and there was no more pages to follow. The List.Generate() just does not work in this circumstance where the next page field does not exist.
The structure of your code does the job nicely. I used the Record.HasFields() function call instead to test for the existance of the "paging" field instead as a minor point of difference.
// process the JSON response
apiResults = if Record.HasFields(apiResponse,"results") = true then List.Combine( {apiResults, apiResponse[results]} ) else apiResults,// Get the next page of results if there are more to fetch
apiHasNextPage = if Record.HasFields(apiResponse,"paging") then
@GetDealsRecursive(apiResults,apiResponse[paging][next][after])
else
apiResults
