I have been playing with some code I have used elsewhere successfully on the hubspot API but it seems to be failing and I cant figure out why. The whole code is below but I seem to fail on "outputList = try if initReq[paging][next][after] = null then initData else gather(initData, apiUrl) otherwise error "Failed outputList"". initReq[paging][next][after] does seem to have data in it however the function fails due to refering to "results" which does not have the paging data in it. Any thoughts on how to approach this please ? TIA
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 = "xxxxxxxxxxxx",
queryStringContentTypevar = "application/json",
queryStringnew = "",
//headers
headers = [Headers = [
#"Content-Type"="application/json", Authorization = "Bearer " & queryStringaccessTokenvar]
],
initReq = try Json.Document(Web.Contents(apiUrl, headers)) otherwise error "Failed to retrieve data from the API 1st Pass",
// 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",
debugResponse = try Json.Document(Web.Contents(uri, headers))[paging][next][after],
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[paging][next][after] = null then initData else gather(initData, apiUrl) otherwise error "Failed outputList"
in
outputList
Thanks
Terran
So I changed the approach a little however it does not work with https://api.hubapi.com/crm/v3/owners where as the above does. If I work out the issue I will post updates here.
T
let
// Define Parameters
searchFilterLimit = "100", // Default limit, ensure this is defined as a parameter
searchFilterarchived = "false", // Ensure this is defined as a parameter
queryStringAccessTokenVar = hapikey_token,
// Base API URL
baseUrl = "https://api.hubapi.com/crm/v3/objects/companies",
// Function to Fetch Data Iteratively with Debug Logging
fetchData =
let
loop = List.Generate(
() => [afterToken = "0", data = {}],
each [afterToken] <> null,
each
let
query = [limit = searchFilterLimit, archived = searchFilterarchived, after = [afterToken]],
response = try Web.Contents(baseUrl, [
Headers = [#"Content-Type"="application/json", Authorization = "Bearer " & queryStringAccessTokenVar],
Query = query
]) otherwise null,
parsedResponse = if response <> null then try Json.Document(response) otherwise null else null,
results = if parsedResponse <> null and Record.HasFields(parsedResponse, "results") then parsedResponse[results] else {},
nextPageToken = if parsedResponse <> null and Record.HasFields(parsedResponse, "paging") and
Record.HasFields(parsedResponse[paging], "next") and
Record.HasFields(parsedResponse[paging][next], "after")
then parsedResponse[paging][next][after]
else null
in
[afterToken = nextPageToken, data = results],
each [data]
),
finalData = List.Combine(loop)
in
finalData,
// Debugging Output
debugOutput = try fetchData otherwise [Error="API Call Failed", Details=fetchData],
// Convert to Table
#"Converted to Table" = Table.FromList(debugOutput, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "properties", "createdAt", "updatedAt", "archived"}, {"Column1.id", "Column1.properties", "Column1.createdAt", "Column1.updatedAt", "Column1.archived"}),
#"Expanded Column1.properties" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.properties", {"createdate", "domain", "hs_lastmodifieddate", "hs_object_id", "name"}, {"Column1.properties.createdate", "Column1.properties.domain", "Column1.properties.hs_lastmodifieddate", "Column1.properties.hs_object_id", "Column1.properties.name"})
in
#"Expanded Column1.properties"