Hi all
I am building a solution to grab data from the API which does work but when it gets to the end of the JSON its not seeing the end as null for some reason and goes in to a infinate loop. Does any one have any suggestions on what to look for as far as the end of the data - key area is the gather subroutine.
let
apiUrl = “https://api.hubapi.com/crm/v3/objects/deals?limit=100&archived=false”,
apiUrlnew = “https://api.hubapi.com/crm/v3/objects/deals?limit=100&archived=false,after=”,
queryStringaccessTokenvar = “xxxxxxxxxxxxxx”,
queryStringClientSecretTokenvar = “xxxxxxxxxxxxx”,
queryStringContentTypevar = “application/json”,
queryStringnew = “”,
//headers = [Headers = [
// #“Content-Type”=queryStringContentTypevar, Authorization = "Bearer " & accessToken=queryStringaccessTokenvar]
// ],
//headers
headers = [Headers = [
#“Content-Type”=“application/json”, Authorization = "Bearer " & queryStringaccessTokenvar]
],
// Make the API request & Parse the JSON response
//initReq = Json.Document(Web.Contents(apiUrl, headers)),
initReq = try Json.Document(Web.Contents(apiUrl, headers)) otherwise error “Failed to retrieve data from the API 1st Pass”,
//initReq = try Json.Document(Web.Contents(apiUrl, headers)) otherwise error apiUrl,
// Convert JSON data to a table
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
//build new uri
newUrinextpage = try Json.Document(Web.Contents(uri, headers))[paging][next][after] otherwise error “Failed to retrieve data from the API 2nd Pass”,
newUri = apiUrlnew&newUrinextpage,
//get new req & data
newReq = try Json.Document(Web.Contents(newUri, headers)) otherwise error “Failed to retrieve data from the API 3rd Pass”,
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 newReq[paging][next][after] = 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 = try if initReq[pageDetails][nextPageUrl] = null then initData else gather(initData, BaseURI)otherwise error uri,
outputList = try if initReq[paging][next][after] = null then initData else gather(initData, apiUrl)otherwise error “Failed outputList”
in
outputList
Thanks
Terran