Forum Discussion
Hubspot Private Apps - Powerquery pagination
Yes mate, but not a particularly elgegant solution, perhaps you could help optomise. My code retreives all but the last page, for example I have 2942 company records in my CRM and it retrieves 2900 records. I then use two additonal queries, one to get the last record ID from the last page of results then uses that ID for one final call to retirieve the missing page and combines them both together to give me a full list. this is obviously for the company object but you can adjust the base url to meet your needs. As i say not the best solution but the only one that works for me so far.
Here is the code that paginates all but the last page
let
Pagination = List.Skip(List.Generate( () => [IsMore = null, Last_Key = 0, Counter = 0], // Start Value
each [IsMore] <> false,// Whilst this is true, keep going
each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com/crm/v3/objects/companies?limit=100&archived=false&after=" & Text.From([Last_Key]) & "" & PropList , [Headers=[Authorization="Bearer pat-ReplaceWithYourActualToken"]])), // retrieve results per call
Last_Key = try Json.Document(Web.Contents("https://api.hubapi.com/crm/v3/objects/companies?limit=100&archived=false&after="& Text.From([Last_Key]) & PropList , [Headers=[Authorization="Bearer pat-ReplaceWithYourActualToken"]]))[paging][next][after] otherwise 0,
IsMore = if [Counter] < 1 then null else try (Table.ColumnNames(Table.RemoveColumns(Table.PromoteHeaders(Table.Transpose(Record.ToTable(Json.Document(Web.Contents("https://api.hubapi.com/crm/v3/objects/companies?limit=100&archived=false&after="& Text.From([Last_Key]) & PropList, [Headers=[Authorization="Bearer pat-ReplaceWithYourActualToken"]])))), [PromoteAllScalars=true]),{"results"})){0}="paging") otherwise false,
Counter = [Counter]+1,
Table = Table.FromRecords(WebCall[results])
]
,each [Table] // selector
) ,1)
,
Custom1 = Table.Combine(Pagination)
in
Custom1
let me know if you have any questions or if you can indeed make this solution better. I have been attempting to utilize chatGPT to help with this but with no avail so far.
Cheers
Will
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...
Thanks for that! much better than my solution, want to send the code you currently have for the marketing email events?
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
This 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.