Hubspot Private Apps - Powerquery pagination

[WillBatesHydro]

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/crm/v4/objects/companies?paged", [Headers=[Authorization="Bearer pat-XXXXXXXXXXX"]]))
in
Source

All my code for paginating Hubspot APIs have the api key in the base url so obviosuly not able to achive teh same ouytcome with barer tokens in headers. Any ideas would be massivly apreictaed!

Thanks 

will

[Syndicate_Admin]

@Syndicate_Admin  and @WBHydro  - this thread has been really useful. Thank you. 

I'm looking to paginate my deals, meetings, and companies data. I can get all but the last page I'm presuming as my records are all rounded numbers. How did you overcome this in the end? 

[WillBatesHydro]

Hey 🙂

I kwno what you mean regarding the last page of contacts, used to have a really long winded work around fo rit. but the below is my my current (slickest solution)

let
baseuri = "https://api.hubapi.com/crm/v3/objects/companies?limit=100&" & "properties=name&properties=lifecyclestage",
headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer YOUR PAT TOCKEN"]],

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"}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns", "properties",
Record.FieldNames(#"Removed Other Columns"{0}[properties]),
Record.FieldNames(#"Removed Other Columns"{0}[properties]))
in
#"Expanded Custom"

[DrewSmith]

Just checking, does this refresh ok in the PowerBI Service? 

[WillBatesHydro]

Not sure sorry, i only use powerquery in excel

[DrewSmith]

Thank you. This has done it 🙂 really apprichate you sharing that code. 

All the best. 

[Syndicate_Admin]

This is the URL I sued to filter from the 1st January 2023 

"https://api.hubapi.com/email/public/v1/events?&startTimestamp=1672531200000&limit=1000&properties=re..."

the timestamp is in milliseconds, I use https://currentmillis.com to convert the date to milliseconds 

Cheers

Will

[Syndicate_Admin]

Ignore my previous comments, ive got it working! 

Not sure how many rows you were expecting but ive got well over a million. for that reason i'd highly recommend using the start/end date time stamps or event type to filter. Excel will really struggle otherwise. none the less, the  code below will work. 

let
baseuri = "https://api.hubapi.com/email/public/v1/events?&limit=1000&properties=recipient&properties=type&prope...",
headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer pat-YOURTOKENHERE"]],

initReq = Json.Document(Web.Contents( baseuri, headers)),
initTable = 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)),
newTable = Record.ToTable(newReq),
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 newReq[hasMore] = true then
let
nextData = @gather(newdata, newUri)
in
List.Combine({data, nextData})
else
List.Combine({data, newdata})
in
check,

//before we call gather(), we want see if its even necessary. First request returns only one page? Return.
outputList = if initReq[hasMore] = true then
@gather(initData, baseuri)
else
initData
in
outputList

Let me know how you get on 

cheers

[Syndicate_Admin]

Well, when I say work, I mean it will do what is as expected but depending on how long you have been using the crm it probably wont load to the workbook as there are too many rows. 

You'll get something like this

As i say, id recommend using some filters in your URL 

[JackSelman]

@Syndicate_Admin  Thank you so much! This has worked a treat! I'm going to create one query for each calendar year then append through PowerBI to avoid this issue. 

[JackSelman]

Struggling with this myself @WillBatesHydro! HAve you had much more luck? I'm stuck on how to get the marketing email events data in a paginated format. I do have a couple of queries that work (for the modern versions where there's a paging value), but legacy APIs with a hasMore and offset value are proving tricky 🙂 

[WillBatesHydro]

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

[JackSelman]

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... 

[DrewSmith]

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.

[WillBatesHydro]

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

[JackSelman]

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