Forum Discussion

gotmike's avatar
gotmike
Frequent Visitor
10 years ago

how to create a query that paginates?

I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the resu...
Need Help
gotmike's avatar
gotmike
Frequent Visitor
7 years ago

Okay, so I'm the OP on this and unfortunately I lost track of this post for a couple YEARS... whoops.

 

But now I got back to it and in looking through all the responses, it seems to be a very hot topic.

 

My original question was about HubSpot CRM specifically and pagination in general.

 

I have finally found a solution, which I will post here for everyone else's benefit.

 

But first, a couple notes:

  1. My goal here was to get DEAL info from HubSpot CRM. Presumably, the same process would work for any other info from HubSpot like CONTACTS or COMPANIES. Also would probably work for many others who have a similar use case.
  2. Like many APIs out there, they PAGINATE the results, by default they will give you 100 per API call, but you can request up to 250 per call.
  3. Like many APIs that paginate, they also give you an OFFSET, so you provide that offset on subsequent calls to get additional pages.
  4. Like many APIs that paginate, they will give you a boolean indicator if there are more or not.
  5. Like many APIs out there, the HubSpot API breaks data up into separate API calls, so the first one gets you a list of IDs, which you then must use to make a subsequent call for each individual item.
  6. Like many APIs out there, the times sent are in unix timestamps. My query converts these to regular time, albeit hard-coded to Eastern Daylight Time.
  7. I believe all the fields I'm using in this are standard HubSpot fields, but there is a possibility there are some custom ones. If so, then you may get an error and have to remove those.
  8. I use two HubSpot API endpoints here, the first one that gives me a list of ALL DEALS with their dealId is https://api.hubapi.com/deals/v1/deal/paged. The second one that gives me individual deal properties is https://api.hubapi.com/deals/v1/deal/[dealId].
  9. Be aware that depending on your number of DEALS, this can take a really long time to execute. It is definitely not "efficient" and is not optimized in any way. I have over 4,000 deals and it takes maybe a half-hour or so.
  10. Also, be aware that HubSpot has a daily limit on API calls of about 40,000. So running this query with 4,000 deals will eat up a lot of your API calls.
  11. I believe I can solve #7 and #8 above by using some combination of two things to optimize this query:
    1. Specifying specific PROPERTIES in the initial API call may prevent me from having to make a separate call for each individual deal, if I know the exact properties I want
    2. Instead of using the above API endpoint for ALL DEALS, I can use the one for RECENTLY CREATED DEALS and specify a start date. This endpoint is https://api.hubapi.com/deals/v1/recent/created.

At any rate, with all the above caveats, here is the query which works for me and includes all the expanded tables/fields to get to the individual values.

 

None of the other solutions worked for me, but I used several of them to get to this point. Thanks to everyone for your help.

 

If I am able to speed this up using the items in #10 above, I will post that solution as well.

 

let
    // Start Values
    Pagination = List.Skip(List.Generate( () => [hsOffset = 0, Counter = 0, isMore = true],

   	   // Condition under which the next execution will happen
           each  [isMore] <> false,

           // retrieve results per call
           each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com/deals/v1/deal/paged?hapikey=[ENTER-YOUR-API-KEY-HERE]&limit=250&offset=" & Text.From(hsOffset))),
           
           hsOffset = try [WebCall][offset] otherwise 0,

           isMore = if [Counter] <1 then null else [WebCall][#"hasMore"],

           // internal counter
    	   Counter = [Counter] + 1

//           ,Table = Table.FromRecords(WebCall[deals])

            ]

//            ,each [Table]

    ),1),
    #"Converted to Table1" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"WebCall", "hsOffset", "isMore", "Counter"}, {"Column1.WebCall", "Column1.hsOffset", "Column1.isMore", "Column1.Counter"}),
    #"Expanded Column1.WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.WebCall", {"deals", "hasMore", "offset"}, {"Column1.WebCall.deals", "Column1.WebCall.hasMore", "Column1.WebCall.offset"}),
    #"Expanded Column1.WebCall.deals" = Table.ExpandListColumn(#"Expanded Column1.WebCall", "Column1.WebCall.deals"),
    #"Expanded Column1.WebCall.deals1" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall.deals", "Column1.WebCall.deals", {"dealId", "isDeleted"}, {"Column1.WebCall.deals.dealId", "Column1.WebCall.deals.isDeleted"}),

    // THIS IS THE SECOND API CALL FOR DEAL DETAILS
    #"Added Custom" = Table.AddColumn(#"Expanded Column1.WebCall.deals1", "DealInfo", each Json.Document(Web.Contents("https://api.hubapi.com/deals/v1/deal/" & Text.From([Column1.WebCall.deals.dealId]) & "?hapikey=[ENTER-YOUR-API-KEY-HERE]"))),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Column1.WebCall.offset", Int64.Type}, {"Column1.WebCall.hasMore", type logical}}),
    #"Expanded DealInfo" = Table.ExpandRecordColumn(#"Changed Type", "DealInfo", {"properties"}, {"DealInfo.properties"}),
    #"Expanded DealInfo.properties" = Table.ExpandRecordColumn(#"Expanded DealInfo", "DealInfo.properties", {"dealname", "createdate", "hubspot_owner_id", "hs_analytics_source", "deal_temperature", "hs_createdate", "dealtype", "closed_lost_reason", "closedate", "pipeline", "notes_last_contacted", "dealstage", "hs_analytics_source_data_2", "hs_analytics_source_data_1", "amount"}, {"DealInfo.properties.dealname", "DealInfo.properties.createdate", "DealInfo.properties.hubspot_owner_id", "DealInfo.properties.hs_analytics_source", "DealInfo.properties.deal_temperature", "DealInfo.properties.hs_createdate", "DealInfo.properties.dealtype", "DealInfo.properties.closed_lost_reason", "DealInfo.properties.closedate", "DealInfo.properties.pipeline", "DealInfo.properties.notes_last_contacted", "DealInfo.properties.dealstage", "DealInfo.properties.hs_analytics_source_data_2", "DealInfo.properties.hs_analytics_source_data_1", "DealInfo.properties.amount"}),
    #"Expanded DealInfo.properties.dealstage" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties", "DealInfo.properties.dealstage", {"value"}, {"DealInfo.properties.dealstage.value"}),
    #"Expanded DealInfo.properties.hs_analytics_source_data_2" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.dealstage", "DealInfo.properties.hs_analytics_source_data_2", {"value"}, {"DealInfo.properties.hs_analytics_source_data_2.value"}),
    #"Expanded DealInfo.properties.hs_analytics_source_data_1" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source_data_2", "DealInfo.properties.hs_analytics_source_data_1", {"value"}, {"DealInfo.properties.hs_analytics_source_data_1.value"}),
    #"Expanded DealInfo.properties.amount" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source_data_1", "DealInfo.properties.amount", {"value"}, {"DealInfo.properties.amount.value"}),
    #"Expanded DealInfo.properties.notes_last_contacted" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.amount", "DealInfo.properties.notes_last_contacted", {"value"}, {"DealInfo.properties.notes_last_contacted.value"}),
    #"Expanded DealInfo.properties.pipeline" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.notes_last_contacted", "DealInfo.properties.pipeline", {"value"}, {"DealInfo.properties.pipeline.value"}),
    #"Expanded DealInfo.properties.closedate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.pipeline", "DealInfo.properties.closedate", {"value"}, {"DealInfo.properties.closedate.value"}),
    #"Expanded DealInfo.properties.closed_lost_reason" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.closedate", "DealInfo.properties.closed_lost_reason", {"value"}, {"DealInfo.properties.closed_lost_reason.value"}),
    #"Expanded DealInfo.properties.dealtype" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.closed_lost_reason", "DealInfo.properties.dealtype", {"value"}, {"DealInfo.properties.dealtype.value"}),
    #"Expanded DealInfo.properties.hs_createdate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.dealtype", "DealInfo.properties.hs_createdate", {"value"}, {"DealInfo.properties.hs_createdate.value"}),
    #"Expanded DealInfo.properties.deal_temperature" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_createdate", "DealInfo.properties.deal_temperature", {"value"}, {"DealInfo.properties.deal_temperature.value"}),
    #"Expanded DealInfo.properties.hs_analytics_source" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.deal_temperature", "DealInfo.properties.hs_analytics_source", {"value"}, {"DealInfo.properties.hs_analytics_source.value"}),
    #"Expanded DealInfo.properties.hubspot_owner_id" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source", "DealInfo.properties.hubspot_owner_id", {"value"}, {"DealInfo.properties.hubspot_owner_id.value"}),
    #"Expanded DealInfo.properties.createdate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hubspot_owner_id", "DealInfo.properties.createdate", {"value"}, {"DealInfo.properties.createdate.value"}),
    #"Expanded DealInfo.properties.dealname" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.createdate", "DealInfo.properties.dealname", {"value"}, {"DealInfo.properties.dealname.value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded DealInfo.properties.dealname",{{"DealInfo.properties.createdate.value", Int64.Type}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"DealInfo.properties.closedate.value", Int64.Type}, {"DealInfo.properties.notes_last_contacted.value", Int64.Type}, {"DealInfo.properties.hs_createdate.value", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Create.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, -4, 0, [DealInfo.properties.createdate.value]/1000)),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Create.DateTime", type datetime}})
in
    #"Changed Type3"
gotmike's avatar
gotmike
Frequent Visitor
7 years ago

Actually, I was able to clean this up and use a single API call to get all the needed properties.

 

The code below makes one single API call that executes quickly and returns most if not all of the standard HubSpot properties.

 

It also converts all the Unix Epoch timestamps to regular DateTime values.

 

And, it assigns the correct data type to each column, so it will sort/filter and play nice in reports.

 

I think this is ultimately what I was after.

 

I hope this helps someone else looking to use Power BI for HubSpot and/or anyone running into this pagination issue.

 

let
    // ENTER API KEY HERE
    apiKey = "[API-KEY-GOES-HERE]",

    // GO THROUGH PAGINATION
    Pagination = List.Skip(List.Generate( () => [hsOffset = 0, Counter = 0, isMore = true],

   	   // Condition under which the next execution will happen
           each  [isMore] <> false,

           // retrieve results per call
           each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com/deals/v1/deal/paged" &
                            "?hapikey=" & myApiKey &
                            "&limit=250" &
                            "&properties=notes_last_updated" &
                            "&properties=dealname" &
                            "&properties=amount" &
                            "&properties=closedate" &
                            "&properties=num_associated_contacts" &
                            "&properties=createdate" &
                            "&properties=pipeline" &
                            "&properties=hubspot_owner_id" &
                            "&properties=num_contacted_notes" &
                            "&properties=hs_lastmodifieddate" &
                            "&properties=hs_analytics_source" &
                            "&properties=notes_last_contacted" &
                            "&properties=hubspot_owner_assigneddate" &
                            "&properties=deal_temperature" &
                            "&properties=dealstage" &
                            "&properties=hs_createdate" &
                            "&properties=hs_object_id" &
                            "&properties=hs_analytics_source_data_2" &
                            "&properties=hs_analytics_source_data_1" &
                            "&properties=num_notes" &
                            "&properties=dealtype" &
                            "&offset=" &
                            Text.From(hsOffset))),
           
           hsOffset = try [WebCall][offset] otherwise 0,

           isMore = if [Counter] <1 then null else [WebCall][#"hasMore"],

           // internal counter
    	   Counter = [Counter] + 1
        ]

    ),1),

    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall"}, {"Column1.WebCall"}),
    #"Expanded Column1.WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.WebCall", {"deals"}, {"Column1.WebCall.deals"}),
    #"Expanded Column1.WebCall.deals" = Table.ExpandListColumn(#"Expanded Column1.WebCall", "Column1.WebCall.deals"),
    #"Expanded Column1.WebCall.deals1" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall.deals", "Column1.WebCall.deals", {"dealId", "isDeleted", "properties"}, {"Column1.WebCall.deals.dealId", "Column1.WebCall.deals.isDeleted", "Column1.WebCall.deals.properties"}),
    #"Expanded Column1.WebCall.deals.properties" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall.deals1", "Column1.WebCall.deals.properties", {"notes_last_updated", "dealname", "closedate", "num_associated_contacts", "createdate", "pipeline", "hubspot_owner_id", "num_contacted_notes", "hs_lastmodifieddate", "hs_analytics_source", "notes_last_contacted", "hubspot_owner_assigneddate", "deal_temperature", "dealstage", "hs_createdate", "hs_object_id", "hs_analytics_source_data_2", "hs_analytics_source_data_1", "num_notes", "dealtype", "amount"}, {"notes_last_updated", "dealname", "closedate", "num_associated_contacts", "createdate", "pipeline", "hubspot_owner_id", "num_contacted_notes", "hs_lastmodifieddate", "hs_analytics_source", "notes_last_contacted", "hubspot_owner_assigneddate", "deal_temperature", "dealstage", "hs_createdate", "hs_object_id", "hs_analytics_source_data_2", "hs_analytics_source_data_1", "num_notes", "dealtype", "amount"}),
    #"Expanded notes_last_updated1" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall.deals.properties", "notes_last_updated", {"value"}, {"notes_last_updated.value"}),
    #"Expanded dealname" = Table.ExpandRecordColumn(#"Expanded notes_last_updated1", "dealname", {"value"}, {"dealname.value"}),
    #"Expanded closedate" = Table.ExpandRecordColumn(#"Expanded dealname", "closedate", {"value"}, {"closedate.value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded closedate",{{"closedate.value", Int64.Type}, {"notes_last_updated.value", Int64.Type}}),
    #"Expanded num_associated_contacts" = Table.ExpandRecordColumn(#"Changed Type", "num_associated_contacts", {"value"}, {"num_associated_contacts.value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded num_associated_contacts",{{"num_associated_contacts.value", Int64.Type}}),
    #"Expanded createdate" = Table.ExpandRecordColumn(#"Changed Type1", "createdate", {"value"}, {"createdate.value"}),
    #"Expanded pipeline" = Table.ExpandRecordColumn(#"Expanded createdate", "pipeline", {"value"}, {"pipeline.value"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded pipeline",{{"createdate.value", Int64.Type}}),
    #"Expanded hubspot_owner_id" = Table.ExpandRecordColumn(#"Changed Type2", "hubspot_owner_id", {"value"}, {"hubspot_owner_id.value"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Expanded hubspot_owner_id",{{"hubspot_owner_id.value", Int64.Type}}),
    #"Expanded num_contacted_notes" = Table.ExpandRecordColumn(#"Changed Type3", "num_contacted_notes", {"value"}, {"num_contacted_notes.value"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Expanded num_contacted_notes",{{"num_contacted_notes.value", Int64.Type}}),
    #"Expanded hs_lastmodifieddate" = Table.ExpandRecordColumn(#"Changed Type4", "hs_lastmodifieddate", {"value"}, {"hs_lastmodifieddate.value"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Expanded hs_lastmodifieddate",{{"hs_lastmodifieddate.value", Int64.Type}}),
    #"Expanded hs_analytics_source" = Table.ExpandRecordColumn(#"Changed Type5", "hs_analytics_source", {"value"}, {"hs_analytics_source.value"}),
    #"Expanded notes_last_contacted" = Table.ExpandRecordColumn(#"Expanded hs_analytics_source", "notes_last_contacted", {"value"}, {"notes_last_contacted.value"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Expanded notes_last_contacted",{{"notes_last_contacted.value", Int64.Type}}),
    #"Expanded hubspot_owner_assigneddate" = Table.ExpandRecordColumn(#"Changed Type6", "hubspot_owner_assigneddate", {"value"}, {"hubspot_owner_assigneddate.value"}),
    #"Changed Type7" = Table.TransformColumnTypes(#"Expanded hubspot_owner_assigneddate",{{"hubspot_owner_assigneddate.value", Int64.Type}}),
    #"Expanded deal_temperature" = Table.ExpandRecordColumn(#"Changed Type7", "deal_temperature", {"value"}, {"deal_temperature.value"}),
    #"Expanded dealstage" = Table.ExpandRecordColumn(#"Expanded deal_temperature", "dealstage", {"value"}, {"dealstage.value"}),
    #"Expanded hs_createdate" = Table.ExpandRecordColumn(#"Expanded dealstage", "hs_createdate", {"value"}, {"hs_createdate.value"}),
    #"Changed Type8" = Table.TransformColumnTypes(#"Expanded hs_createdate",{{"hs_createdate.value", Int64.Type}}),
    #"Expanded hs_object_id" = Table.ExpandRecordColumn(#"Changed Type8", "hs_object_id", {"value"}, {"hs_object_id.value"}),
    #"Changed Type9" = Table.TransformColumnTypes(#"Expanded hs_object_id",{{"hs_object_id.value", Int64.Type}}),
    #"Expanded hs_analytics_source_data_2" = Table.ExpandRecordColumn(#"Changed Type9", "hs_analytics_source_data_2", {"value"}, {"hs_analytics_source_data_2.value"}),
    #"Expanded hs_analytics_source_data_1" = Table.ExpandRecordColumn(#"Expanded hs_analytics_source_data_2", "hs_analytics_source_data_1", {"value"}, {"hs_analytics_source_data_1.value"}),
    #"Expanded num_notes" = Table.ExpandRecordColumn(#"Expanded hs_analytics_source_data_1", "num_notes", {"value"}, {"num_notes.value"}),
    #"Changed Type10" = Table.TransformColumnTypes(#"Expanded num_notes",{{"num_notes.value", Int64.Type}}),
    #"Expanded dealtype" = Table.ExpandRecordColumn(#"Changed Type10", "dealtype", {"value"}, {"dealtype.value"}),
    #"Expanded amount" = Table.ExpandRecordColumn(#"Expanded dealtype", "amount", {"value"}, {"amount.value"}),
    #"Changed Type11" = Table.TransformColumnTypes(#"Expanded amount",{{"amount.value", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type11", "CloseDate.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [closedate.value]/1000)),
    #"Changed Type12" = Table.TransformColumnTypes(#"Added Custom",{{"CloseDate.DateTime", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type12", "CreateDate.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [createdate.value]/1000)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "LastModified.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [hs_lastmodifieddate.value]/1000)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "LastContacted.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [notes_last_contacted.value]/1000)),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom3", {{"LastContacted.DateTime", null}}),
    #"Added Custom4" = Table.AddColumn(#"Replaced Errors", "OwnerAssigned.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [hubspot_owner_assigneddate.value]/1000)),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "HsCreateDate.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [hs_createdate.value]/1000)),
    #"Changed Type13" = Table.TransformColumnTypes(#"Added Custom5",{{"CreateDate.DateTime", type datetime}, {"LastModified.DateTime", type datetime}, {"LastContacted.DateTime", type datetime}, {"OwnerAssigned.DateTime", type datetime}, {"HsCreateDate.DateTime", type datetime}})
in
    #"Changed Type13"