Forum Discussion
Hubspot API POST request with relative path
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"
Hi lbendlin ,
I've already retrieved a table with multiple columns from the GET API call (a list of records that are transformed into a table). One of those columns is called "id" (TaskID) (print screen below). I need to retrieve additional data associated with these TaskIDs by making a POST API call.
I need to loop through each TaskID in the "id" column, which means creating a function that can be invoked in the same table as the "id" column. The POST API doc can be found here (down below, I have also attached a print screen of what API call I'm using): https://developers.hubspot.com/docs/api/crm/search
I've already created a function (below code) that works and provides the desired results, but I've noticed that it's slow. I'm wondering if using a relative path might improve performance when publishing the report to Power BI Services. Additionally, I'd like to ensure that the report refreshes automatically when it's published to Power BI Services. Could you help me modify the function to implement a relative path?
Thanks for your assistance.
(id as text)=>
let
url = "https://api.hubapi.com/crm/v3/objects/companies/search",
headers = [#"Content-Type"="application/json", Authorization="Bearer XXX"],
postData = "{""filters"": [{""propertyName"": ""associations.task"", ""operator"": ""EQ"", ""value"":"""& 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"
use the "IN" operator to request a bunch of them at the same time.
https://developers.hubspot.com/docs/api/crm/search#in-operator
Hi lbendlin, thank you, I understand, but how do I modify my code with the IN operator to include all the values (without manually writing each id) from the "id" column as described above? Could you please alter my function so I can understand how to write the syntax?
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.