Forum Discussion

JackSelman's avatar
JackSelman
Helper I
3 years ago

Here you go WillBatesHydro, this is what I'm using for companies. 

let
    baseuri = "https://api.hubapi.com/crm/v3/objects/companies?limit=100&properties=name
&properties=country
&properties=domain
&properties=industry
&properties=hubspot_owner_id
&properties=notes_last_updated
&properties=lifecyclestage
&properties=city
&properties=region_hidden_
&properties=numberofemployees
&properties=num_associated_deals
&properties=hs_num_open_deals
&properties=annualrevenue
&properties=num_contacted_notes
&properties=description
&properties=timezone
&properties=linkedin_company_page",
    headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer PAT"]],

    initReq = Json.Document(Web.Contents( baseuri, headers)),
    #"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, headers))[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, headers)) ,
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),
    #"Removed Other Columns" = Table.SelectColumns(expand,{"id", "properties"})
in
    #"Removed Other Columns"

I just can't get this to work for the Marketing Email Events API that uses hasMore and offset instead of paging... 

WillBatesHydro's avatar
WillBatesHydro
Helper I
3 years ago

Thanks for that! much better than my solution, want to send the code you currently have for the marketing email events?

  • JackSelman's avatar
    JackSelman
    Helper I
    3 years ago

    Glad that's worked for you WillBatesHydro! Here's what I'm working with on the marketing email events. It's stuck loading, so I can't figure out if I've built the query incorrectly or there's too much data to pull and I'm just being impatient (although I left it running overnight to no avail...)

    let
    baseuri = APIURL&"/email/public/v1/events?&limit=1000&properties=recipient&properties=type&properties=portalId&properties=appId&properties=appName&properties=emailCampaignId&property=subject&property=dropReason&property=dropMessage&property=duration",
    headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer PAT"]],

    initReq = Json.Document(Web.Contents( baseuri, headers)),
    #"Converted to Table" = Record.ToTable(initReq),
    initData = initReq[events],
    
//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))[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[events] ,

//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 = "hasMore"] ) = 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 = "hasMore"] ) = true then gather( initData , baseuri ) else initData
in
    outputList