Forum Discussion
Solved
Hubspot API POST request with relative path
Hi all, I have the following API POST query which works great: let url = "https://api.hubapi.com/crm/v3/objects/companies/search", headers = [#"Content-Type"="application/json", Authorization=...
Hello everyone,
I'm excited to share a breakthrough I've achieved after extensive research into integrating HubSpot API with BI tools like Power BI, which I know has been a challenge for many in this community.
The Key Solution: The most effective method I've discovered involves creating a dynamic function within Power Query. This function efficiently loops through the deals table in HubSpot, associating each deal ID with its corresponding company ID.
Getting Started: Your first step is to create a dynamic function. Provided below is the precise syntax necessary for constructing a function. This function will adeptly navigate through the deals table in HubSpot, effectively linking each deal ID to its respective company ID.
let
Source = (id as text)=>
let
url = "https://api.hubapi.com/crm/v4/associations/deals/companies/batch/read",
headers = [#"Content-Type"="application/json", Authorization="Bearer pat-XXX"],
postData = "{""inputs"": [{ ""id"": """& id &"""}]}",
response = Web.Contents(
url,
[
Headers = headers,
Content = Text.ToBinary(postData)
]
),
jsonResponse = Json.Document(response),
results = jsonResponse[results],
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
in
SourceYour second step is to extract the deals table from HubSpot. To ensure seamless functionality when publishing your BI report in Power BI services, it's crucial to implement a Relative path and Pagination strategy. This approach resolves the common issue many have faced regarding publishing the BI report on BI services and setting up an automatic refresh schedule.
Syntax and Implementation: Below is the specific syntax you should use to retrieve the deals table (API GET request). This same logic can be applied to other tables such as companies and tasks, allowing for a more comprehensive and integrated data analysis experience.
let
baseuri = "https://api.hubapi.com",
relativePath = "/crm/v3/objects/deals",
headers = [#"Content-Type"="application/json", Authorization="Bearer pat-XXX"],
params = [limit="100", properties={"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,hs_is_closed,hs_is_closed_won,engagements_last_meeting_booked,hs_closed_won_date,hs_deal_stage_probability_shadow,hs_deal_stage_probability,one_off_consulting_revenue,hs_arr,hs_tcv,solution__main_,deal_country,dealtype,win_probability"}],
// Define the GetPage function within the main let-expression
GetPage = (data as list, after as nullable text) as list =>
let
queryParams = if after <> null then params & [after=after] else params,
response = Json.Document(Web.Contents(baseuri, [RelativePath=relativePath, Headers=headers, Query=queryParams])),
newdata = response[results],
combinedData = List.Combine({data, newdata}),
hasMore = Record.HasFields(response, "paging"),
nextAfter = if hasMore then response[paging][next][after] else null,
result = if nextAfter <> null then @GetPage(combinedData, nextAfter) else combinedData
in
result,
// Initial call to GetPage
finalData = GetPage({}, null),
expandedTable = Table.FromList(finalData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(expandedTable, "Column1", {"id", "properties", "createdAt", "updatedAt", "archived"}, {"id", "properties", "createdAt", "updatedAt", "archived"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1",{"id", "properties"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Removed Other Columns", "properties", {"amount", "amount_in_home_currency", "closed_lost_reason", "closed_won_reason", "closedate", "createdate", "deal_country", "dealname", "dealstage", "dealtype", "engagements_last_meeting_booked", "hs_arr", "hs_closed_won_date", "hs_deal_stage_probability", "hs_deal_stage_probability_shadow", "hs_forecast_amount", "hs_forecast_probability", "hs_is_closed", "hs_is_closed_won", "hs_lastmodifieddate", "hs_manual_forecast_category", "hs_object_id", "hs_tcv", "hubspot_owner_id", "num_contacted_notes", "num_notes", "one_off_consulting_revenue", "pipeline", "solution__main_", "win_probability"}, {"amount", "amount_in_home_currency", "closed_lost_reason", "closed_won_reason", "closedate", "createdate", "deal_country", "dealname", "dealstage", "dealtype", "engagements_last_meeting_booked", "hs_arr", "hs_closed_won_date", "hs_deal_stage_probability", "hs_deal_stage_probability_shadow", "hs_forecast_amount", "hs_forecast_probability", "hs_is_closed", "hs_is_closed_won", "hs_lastmodifieddate", "hs_manual_forecast_category", "hs_object_id", "hs_tcv", "hubspot_owner_id", "num_contacted_notes", "num_notes", "one_off_consulting_revenue", "pipeline", "solution__main_", "win_probability"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Expanded properties", {{"hs_forecast_amount", each Text.BeforeDelimiter(_, "."), type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"hs_lastmodifieddate", type datetime}, {"hs_manual_forecast_category", type text}, {"hs_object_id", Int64.Type}, {"hubspot_owner_id", Int64.Type}, {"num_contacted_notes", Int64.Type}, {"num_notes", Int64.Type}, {"pipeline", type text}, {"id", Int64.Type}, {"amount", type number}, {"amount_in_home_currency", type number}, {"closed_lost_reason", type text}, {"closed_won_reason", type text}, {"closedate", type datetime}, {"createdate", type datetime}, {"dealname", type text}, {"dealstage", type text}, {"dealtype", type text}, {"hs_forecast_amount", Int64.Type}, {"hs_forecast_probability", type number}, {"hs_is_closed", type text}, {"hs_is_closed_won", type text}, {"engagements_last_meeting_booked", type datetime}, {"hs_closed_won_date", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"closedate", type date}, {"createdate", type date}, {"hs_lastmodifieddate", type date}, {"hs_closed_won_date", type date}, {"engagements_last_meeting_booked", type date}, {"id", type text}, {"hs_deal_stage_probability_shadow", Percentage.Type}, {"hs_deal_stage_probability", Percentage.Type}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type1", "Get Batch Comp/deal id", each #"Get Batch Comp/deal id"([id])),
#"Expanded Get Batch Comp/deal id" = Table.ExpandTableColumn(#"Invoked Custom Function", "Get Batch Comp/deal id", {"Column1"}, {"Column1"}),
#"Expanded Column1.0" = Table.ExpandRecordColumn(#"Expanded Get Batch Comp/deal id", "Column1", {"to"}, {"to"}),
#"Expanded to" = Table.ExpandListColumn(#"Expanded Column1.0", "to"),
#"Expanded to1" = Table.ExpandRecordColumn(#"Expanded to", "to", {"toObjectId"}, {"toObjectId"}),
#"Invoked Custom Function1" = Table.AddColumn(#"Expanded to1", "Get Batch Task/deal id", each #"Get Batch Task/deal id"([id])),
#"Expanded Get Batch Task/deal id" = Table.ExpandTableColumn(#"Invoked Custom Function1", "Get Batch Task/deal id", {"Column1"}, {"Column1"}),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Get Batch Task/deal id", "Column1", {"to"}, {"to"}),
#"Expanded to2" = Table.ExpandListColumn(#"Expanded Column2", "to"),
#"Expanded to3" = Table.ExpandRecordColumn(#"Expanded to2", "to", {"toObjectId"}, {"toObjectId.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded to3",{{"toObjectId", "Company_id"}, {"toObjectId.1", "Task_id"}, {"hs_tcv", "total_contract_value"}, {"hs_arr", "ARR"}, {"deal_country", "Market"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Company_id", Int64.Type}, {"Task_id", Int64.Type}, {"one_off_consulting_revenue", Int64.Type}, {"ARR", type number}, {"total_contract_value", type number}, {"win_probability", Percentage.Type}, {"solution__main_", type text}, {"Market", type text}, {"amount", type number}})
in
#"Changed Type2"
show a sample output of the call that collects the IDs.
Hi lbendlin,
This is the code that gives me the IDs:
let
tasksproperties = "hs_timestamp,hs_task_body,hubspot_owner_id,hs_task_subject,hs_task_status,hs_task_priority,hs_task_type,hs_body_preview",
propertiesQString = "&properties=" & Text.Replace(tasksproperties, ",", "&properties="),
baseuri = "https://api.hubapi.com/crm/v3/objects/tasks?limit=100"&propertiesQString,
headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer XXX"]],
initReq = Json.Document(Web.Contents( baseuri, headers)),
#"Converted to Table" = Record.ToTable(initReq),
initData = initReq[results],
//We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records,
//then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then
//create a table from those records
gather = (data as list, uri) =>
let
//get new offset from active uri
newOffset = Json.Document(Web.Contents(uri, headers))[paging][next][after],
//build new uri using the original uri so we dont append offsests
newUri = baseuri & "&after=" & newOffset,
//get new req & data
newReq = Json.Document(Web.Contents(newUri, headers)) ,
newdata = newReq[results] ,
//add that data to rolling aggregate
data = List.Combine({data, newdata}),
//if theres no next page of data, return. if there is, call @gather again to get more data
check = if Table.Contains ( Record.ToTable(newReq) , [Name = "paging"] ) = true then @gather (data , newUri) else data
in check,
//before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
outputList = if Table.Contains ( Record.ToTable (initReq) , [Name = "paging"] ) = true then gather( initData , baseuri ) else initData ,
//then place records into a table. This will expand all columns available in the record.
expand = Table.FromRecords(outputList),
#"Removed Columns" = Table.RemoveColumns(expand,{"properties", "createdAt", "updatedAt", "archived"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}})
in
#"Changed Type"
The output looks like this:
The id column should then be passed in as a list of values to the IN operator, but I don't know how to do that.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ3NTIxNjA3sVCK1QFzLQzNLQ3MULiGRqZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t]), body = "{ ""filterGroups"": [ { ""filters"": [ { ""propertyName"": ""dealstage"", ""operator"": ""IN"", ""values"": [""" & Text.Combine(Source[id],""",""") & """] } ] } ] }", #"Added Custom" = Table.AddColumn(Source, "Custom", each Web.Contents("https://api.hubapi.com/crm/v3/objects/deals/search",[ Headers = [#"Content-Type" = "application/json"], Content = Text.ToBinary(body)])) in #"Added Custom"gives you the sample payload:
Hi lbendlin , It didn't work for me I got Formula.Firewall: Query 'Tasks (2)' (step 'Expanded Table Column1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
But I managed to extract the correct information by using this query:
let
tasksproperties = "hs_timestamp,hs_task_body,hubspot_owner_id,hs_task_subject,hs_task_status,hs_task_priority,hs_task_type,hs_body_preview",
propertiesQString = "&properties=" & Text.Replace(tasksproperties, ",", "&properties="),
baseuri = "https://api.hubapi.com/crm/v3/objects/tasks?limit=100"&propertiesQString,
headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer XXX"]],
initReq = Json.Document(Web.Contents( baseuri, headers)),
#"Converted to Table" = Record.ToTable(initReq),
initData = initReq[results],
//We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records,
//then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then
//create a table from those records
gather = (data as list, uri) =>
let
//get new offset from active uri
newOffset = Json.Document(Web.Contents(uri, headers))[paging][next][after],
//build new uri using the original uri so we dont append offsests
newUri = baseuri & "&after=" & newOffset,
//get new req & data
newReq = Json.Document(Web.Contents(newUri, headers)) ,
newdata = newReq[results] ,
//add that data to rolling aggregate
data = List.Combine({data, newdata}),
//if theres no next page of data, return. if there is, call @gather again to get more data
check = if Table.Contains ( Record.ToTable(newReq) , [Name = "paging"] ) = true then @gather (data , newUri) else data
in check,
//before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
outputList = if Table.Contains ( Record.ToTable (initReq) , [Name = "paging"] ) = true then gather( initData , baseuri ) else initData ,
//then place records into a table. This will expand all columns available in the record.
expand = Table.FromRecords(outputList),
#"Removed Other Columns" = Table.SelectColumns(expand,{"id", "properties"}),
#"Expanded properties" = Table.ExpandRecordColumn(#"Removed Other Columns", "properties", {"hs_body_preview", "hs_createdate", "hs_lastmodifieddate", "hs_object_id", "hs_task_body", "hs_task_priority", "hs_task_status", "hs_task_subject", "hs_task_type", "hs_timestamp", "hubspot_owner_id"}, {"hs_body_preview", "hs_createdate", "hs_lastmodifieddate", "hs_object_id", "hs_task_body", "hs_task_priority", "hs_task_status", "hs_task_subject", "hs_task_type", "hs_timestamp", "hubspot_owner_id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded properties",{{"id", type text}, {"hs_body_preview", type text}, {"hs_createdate", type datetime}, {"hs_lastmodifieddate", type datetime}, {"hs_object_id", Int64.Type}, {"hs_task_body", type text}, {"hs_task_priority", type text}, {"hs_task_status", type text}, {"hs_task_subject", type text}, {"hs_task_type", type text}, {"hs_timestamp", type datetime}, {"hubspot_owner_id", Int64.Type}}),
// I have turned the id column into a list
id = #"Changed Type"[id],
url = "https://api.hubapi.com/crm/v3/objects/companies/search",
headers2 = [#"Content-Type"="application/json", Authorization="Bearer XXX"],
postData = "{
""filterGroups"":[{
""filters"":[
{""propertyName"": ""associations.task"", ""operator"": ""IN"", ""values"":[" & Text.Combine(List.Transform(List.Distinct(id), each """" & _ & """"),",") & "]}]}]}",
response = Web.Contents(
url,
[
Headers = headers2,
Content = Text.ToBinary(postData)
]
),
jsonResponse = Json.Document(response),
results = jsonResponse[results],
#"Converted to Table2" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table2"The problem is now that there seems to be a limit when you for example input 1000 values you get a bad request (400), But if you input 100 values it works without a problem, so I think that some type of pagination must be added to the list (of IDs) I'm using as well, please feel free to add your suggestion on how I can write that pagination syntax?
that needs to be part of the POST request.
According to the documentation:
Paging through results
By default, the search endpoints will return pages of 10 records at a time. This can be changed by setting the
limit parameter in the request body. The maximum number of supported objects per page is 100.For example, the request below would return pages containing 20 results each.
curl https://api.hubapi.com/crm/v3/objects/contacts/search \ --request POST \ --header "Content-Type: application/json" \ --data '{ "limit": 20 }