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

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 

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"