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 API...
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"
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.