Forum Discussion

Jigar1276's avatar
Jigar1276
Helper I
4 years ago
Solved

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

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?

 

For ref: I got this connector which is working perfectly but I do not want to use connector and gateway setup.

https://github.com/ranmax123/powerbi-custom-connectors/tree/master/HubSpot

 

Thanks in advance.

Need Help
  • Jigar1276's avatar
    Jigar1276
    4 years ago

    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"

5 Replies

  • Jigar1276's avatar
    Jigar1276
    Helper I
    4 years ago

    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"
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

    • Jigar1276's avatar
      Jigar1276
      Helper I
      4 years ago

      Thanks a lot for reply Anonymous ,

       

      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.

       

      The API documentation is here: https://legacydocs.hubspot.com/docs/methods/deals/get-all-deals 

       

      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.