Forum Discussion

JackSelman's avatar
JackSelman
Icon for Helper I rankHelper I
3 years ago

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's avatar
    WillBatesHydro
    Icon for Helper I rankHelper I
    3 years ago

    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's avatar
      JackSelman
      Icon for Helper I rankHelper 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
        Icon for Helper I rankHelper 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?