Forum Discussion
WillBatesHydro
Helper I
3 years agoHubspot Private Apps - Powerquery pagination
Has anybody managed to paginate hubspots api with the new private apps setup? Managed to make a call easily enough like so; let Source = Json.Document(Web.Contents("https://api.hubapi.com/...
JackSelman
Helper I
3 years agoHere 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...
DrewSmith
Helper III
2 years agoThis is great, thank you. Is there any other guides for other tables? I need to do the same with at least 4 other tables but adding other columns in.