powerbihubspothubspot-api

Hubspot and Power BI Coding Issue


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


Solution

  • 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"