Forum Discussion
Jigar1276
4 years agoHelper I
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...
- 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
4 years agoHelper I
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
4 years agoNot applicable
Works great for deals. Have a contacts / companies version?