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 = ";archived=false",
apiUrlnew = ";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

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/crm/v3/objects/deals?limit=100&archived=false",
apiUrlnew = "https://api.hubapi/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

• powerbi
• hubspot
• hubspot-api

Share Improve this question Follow asked Mar 25 at 12:38 Terran BrownTerran Brown 52911 gold badge1010 silver badges2727 bronze badges 1

• So important. I will follow this question – Francesco Mantovani Commented Mar 27 at 16:01

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

So I changed the approach a little however it does not work with https://api.hubapi/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/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"