Get data from Hubspot by making API calls (Without using connectors)

[Syndicate_Admin]

Hi,

I want to use this Hubspot API to get data into the Power BI: 

https://legacydocs.hubspot.com/docs/methods/contacts/get_contacts

I am using following M Query to do that:

let
    APIURL = "https://api.hubapi.com",
    MaxRecords = 100,
    Total =
    let
        Source = Json.Document(Web.Contents( APIURL & "/contacts/v1/contacts/statistics?hapikey=my-api-key")),
        Total = Source[contacts]
    in
        Total,
    OP= 
        let
            data =
                List.Generate(
                    () => [ a = 0, c = 0 ],
                    each [a] <= Total,
                    each [
                            a = [a] + MaxRecords,
                            c = [a]
                        ]
                    ),
                    op = Table.FromRecords(data)[a]
        in
            op,
    vidOffset=0,
    Loop = List.Transform( OP, each 
            let
                source = Json.Document(Web.Contents(APIURL, 
                    [
                        RelativePath = "/contacts/v1/lists/all/contacts/all?hapikey=my-api-key" & "&vidOffset=" & Number.ToText(vidOffset) & "&count=" & Number.ToText(MaxRecords)  & "&loop=" & Number.ToText(_)
                    ]
                ))
            in
                source
        ),
    #"Converted to Table" = Table.FromList(Loop, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"contacts", "has-more", "vid-offset"}, {"contacts", "has-more", "vid-offset"}),
    #"Expanded contacts" = Table.ExpandListColumn(#"Expanded Column1", "contacts"),
    #"Expanded contacts1" = Table.ExpandRecordColumn(#"Expanded contacts", "contacts", {"vid", "canonical-vid", "properties", "identity-profiles"}, {"vid", "canonical-vid", "properties", "identity-profiles"}),
    #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded contacts1", "properties", {"firstname", "company", "lastname"}, {"firstname", "company", "lastname"}),
    #"Expanded firstname" = Table.ExpandRecordColumn(#"Expanded properties", "firstname", {"value"}, {"value"}),
    #"Expanded company" = Table.ExpandRecordColumn(#"Expanded firstname", "company", {"value"}, {"value.1"}),
    #"Expanded lastname" = Table.ExpandRecordColumn(#"Expanded company", "lastname", {"value"}, {"value.2"})
in
    #"Expanded lastname"

I have 37,500+ records in Hubspot Contacts.

This code is fetching first 100 records again and again. We are getting these two fields in each API call:

"vid-offset" is some random number and not giving proper pagination.

The problem is with the next API calls I made, where we need to make next calls ony if "has-more" is true and we need to pass "vid-offset" value which we received in previous call as API parameter "vidOffset". 

Can anyone help me with editing my M query please?

Thanks in advance.

[Syndicate_Admin]

I found and tried following code but it is giving blank list:

let
        APIURL = "https://api.hubapi.com/contacts/v1/lists/all/contacts/all?hapikey=91250982-2af8-4145-bc13-e4d344383df5",
        MaxRecords = 100,
        Source = 
                let
                jobsJsonPaginated = List.Generate( () => 
                                [pageResult = null, nextOffset = 0, hasMore = true],
                                each [hasMore] = true, //or [nextOffset] <> 0,
                                        each [pageResult = 
                                                let
                                                        _url = APIURL & "&vidOffset=" & Text.From(nextOffset) & "&count=" & Number.ToText(MaxRecords),
                                                        response =  Json.Document(Web.Contents(_url))
                                                in
                                                        response, 
                                                        nextOffset = try [#"vid-offset"] otherwise 0,
                                                        hasMore = try [#"has-more"] otherwise false,
                                                        counter = [counter] + 1],
                                        each [pageResult]),
                
                                        jobsJsonPaginated2 = List.Skip(jobsJsonPaginated, 1)
                in 
                        jobsJsonPaginated2
in
    Source

[Syndicate_Admin]

@Jigar1276 

You can also take a look at the following document about the help functions about processing pagination:

Helper functions for M extensions for Power Query connectors | Microsoft Docs

Best Regards

Paul

[Syndicate_Admin]

Thanks a lot for reply @V-pazhen-msft ,

This looks like connectors, but I am looking for M query so I can get the data directly into power bi rather then using the gateway.

Also I have almost written following code which gives me the page by page data but problem is only that once all the pages are fetched it agains starting the fetching, so it seems never ending loop.

let
	APIURL = "https://api.hubapi.com/deals/v1/deal/paged?&limit=100&hapikey=my-api-key&offset=",
	
	Pagination = List.Skip(List.Generate( () => [Last_Key = "0", Counter=0],
		each [Last_Key] <> null,
		each [Last_Key = try if [Counter]=0 then "" else [Source][Value][offset] otherwise null,
		//each [Last_Key = try if Number.ToText([Source][Value][offset])="0" then null else [Source][Value][offset] otherwise null,
		Source = try if [Counter]=0 then
			Json.Document(Web.Contents(APIURL & Last_Key))
		else
		Json.Document(Web.Contents(APIURL & Number.ToText(Last_Key))),
		Counter = [Counter]+1
		],
		each [Source]
	),1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"HasError", "Value"}, {"Column1.HasError", "Column1.Value"}),
    #"Expanded Column1.Value" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Value", {"deals", "hasMore", "offset"}, {"deals", "hasMore", "offset"})
in
    #"Expanded Column1.Value"

The output it gives is as follow:

Required rows are only first 12 but it goes on and on repeating same 12 rows.

If you can please review and suggest to end the loop. Meanwhile I will go thru the link you sent.

Thanks a log.

[Syndicate_Admin]

Looks like your API has an offset parameter.  Please see this video for a different/simpler way to do this.

Power BI - Tales From The Front - REST APIs - YouTube

Pat

[Syndicate_Admin · Accepted solution]

Got the solution. The code to get all the deals is follow: 

let
	apiUrl = "https://api.hubapi.com",
	dealsProperties = "hs_forecast_amount,hs_manual_forecast_category,hs_forecast_probability,amount,amount_in_home_currency,closedate,createdate,dealname,dealstage,dealtype,pipeline,hubspot_owner_id,num_notes,num_contacted_notes,closed_lost_reason,closed_won_reason",
	propertiesQString = "&properties=" & Text.Replace(dealsProperties, ",", "&properties="),
	suffixUrl = "/deals/v1/deal/paged?hapikey=my-api-key-here&limit=250&includeAssociations=true" & propertiesQString & "&offset=",
	Source = 
	let
	jobsJsonPaginated = List.Generate( () => 
		[pageResult = null, nextOffset = 0, counter = 1],
		each [counter] <= 1 or [nextOffset] <> 0,
		each [pageResult = try 
		let
			response =  Json.Document(Web.Contents(apiUrl, [RelativePath = suffixUrl & Text.From([nextOffset])]))
		in
			response
			otherwise null, 
				nextOffset = try pageResult[offset] otherwise 0,
				counter = [counter] + 1],
		each [pageResult]),
	
	jobsJsonPaginated2 = List.Skip(jobsJsonPaginated, 1)
in 
    jobsJsonPaginated2,
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

[Syndicate_Admin]

Works great for deals. Have a contacts / companies version?

[Syndicate_Admin]

Hi @Jigar1276 I know this reply is very late lol. As a workaround, maybe you can use a 3rd party connector, it can save you a lot of time compared to using M code or using a gateway. I've tried windsor.ai, supermetrics (which doesn't have PBI as a destination, so you first have to export to Google Sheets and then export to PBI) and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Hubspot connector in the data sources list:

After that, just grant access to your Hubspot account using your credentials, then on preview and destination page you will see a preview of your Hubspot fields:

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.