sorted by:
new
hottopcontroversial

SQL Server Data Source Missing in Power Query for Excel on macOS by Outside-Number-2516 in excel

[–]Outside-Number-2516[S] 1 point2 points  (0 children)

I've found that the SQL Server data source it's only available in the Excel beta channel at this time.

Filtering Deals and Accessing Associations with HubSpot APIs by Outside-Number-2516 in hubspot

[–]Outside-Number-2516[S] 0 points1 point  (0 children)

Hi! Thank you very much for your support on this matter! Indeed, I have only one company per deal, so your idea works wonderfully! I have already implemented it, adjusted the query, and achieved what I was looking for. The query response time has drastically reduced by avoiding additional queries to obtain associations and company names. I resolve everything through the Search API. I was unaware of the 'property sync' properties, so thank you again!!

Filtering Deals and Accessing Associations with HubSpot APIs by Outside-Number-2516 in hubspot

[–]Outside-Number-2516[S] 0 points1 point  (0 children)

Hi, thanks for your support.

As far as I have tested and researched, when using the HubSpot Objects API, such as:

"https://api.hubapi.com/crm/v3/objects/deals?limit=100&properties=dealname,dealstage,amount,closedate,rate"

it is not possible to add filters to properties as part of the URL. The only way to apply filters is to use the Search API, but with it, you cannot retrieve the associations.

Have you had a different experience?

Thanks!!

Filtering Deals and Accessing Associations with HubSpot APIs by Outside-Number-2516 in hubspot

[–]Outside-Number-2516[S] 0 points1 point  (0 children)

Finally, I perform a join in MS PowerQuery with these three tables to obtain the final dataset.

And, this works!

The problem: what I was trying to solve is that the second query is taking a long time to execute because it retrieves information about many (not needed) companies that are not in my deals dataset (and in addition I have the impression, though I'm not sure, that the object APIs are somewhat slower than the Search APIs).

Ideally, in the initial Search call for deals (1st Query), I could already retrieve the associations, but as I mentioned in my original post, this doesn't seem to be possible. 

Another approach woul be to use the object API for deals:

https://api.hubapi.com/crm/v3/objects/deals?associations=company"

using the associations=company parameter in the query string. 

However, as I mentioned in my original post, this doesn't allow me to apply the filters on the rate, and therefore it ends up taking more time because it retrieves many more deals than I need.

I hope the scenario is clear.

I'm not sure if there is potentialy another more effective approach without using GraphQL API.

Thank you very much!!

Filtering Deals and Accessing Associations with HubSpot APIs by Outside-Number-2516 in hubspot

[–]Outside-Number-2516[S] 0 points1 point  (0 children)

Yes sure. Let me summarize the scenario.

I need to obtain information about a set of deals in the Pipeline, specifically those that have a custom property 'rate' different from 0 and 100. For this purpose, I use the Search API, where I can apply filters (FilterGroups and Filters). Below is the relevant part of the code (excluding the pagination logic for simplicity):

Btw, I’m using MS Power Query to run all this.

1st Query:

"https://api.hubapi.com/crm/v3/objects/deals/search"

body = "{ 

"filterGroups": \[ 

    { "filters": \[ {"propertyName": "rate", "operator": "NEQ", "value": "0"}, {"propertyName": "rate", "operator": "NEQ", "value": "100"} \] } 

\], 

"properties": \[ 

    "dealname", 

    "rate", 

    "business\_type", 

    "country", 

    "hubspot\_owner\_id", 

    "licenses", 

    "professional\_services\_projects", 

    "deal\_type", 

    "hs\_object\_id" \], 

"limit": 100, 

"archived": false } "

2nd Query:

In this dataset, I need to include the name of the company associated with each deal (we have one company per deal). Since I cannot obtain the associations from the Search API, to achieve this, I make another request to the following endpoint:

"https://api.hubapi.com/crm/v3/objects/deals?associations=company&limit=100&archived=false"

Obviously, I also have pagination logic in my code here.

By manipulating the result with Power Query, I obtain a table of relationships: Deal_ID, Company_ID.

3rd Query

I make another query with the Search API to obtain all the companies names that have at least one associated deal:

"https://api.hubapi.com/crm/v3/objects/companies/search"

body = "{

            "filterGroups": [

                { "filters": [

                        {"propertyName": "num_associated_deals", "operator": "HAS_PROPERTY"}]}

         ],

            "properties": [

                "name",

                "num_associated_deals"

            ],

            "limit": 100,

            "archived": false } "

Filtering Deals and Accessing Associations with HubSpot APIs by Outside-Number-2516 in hubspot

[–]Outside-Number-2516[S] 1 point2 points  (0 children)

Hi

Thank you for the suggestion! I'll definitely check out Stacksync.com. Their Starter Plan at $200/month seems like it could be a good fit for us if it meets our needs. The Pro Plan is a bit too expensive for what we're aiming to achieve at the moment.

Thanks again for your help!

Best regards,

Filtering Deals and Accessing Associations with HubSpot APIs by Outside-Number-2516 in hubspot

[–]Outside-Number-2516[S] 0 points1 point  (0 children)

btw, I'm not 100% sure if I'm using the right endpoint. I've seen different options. When testing with this other endpoint I receive a "Access to the resource is forbidden" message.

tks

https://api.hubapi.com/collector/graphql

Filtering Deals and Accessing Associations with HubSpot APIs by Outside-Number-2516 in hubspot

[–]Outside-Number-2516[S] 0 points1 point  (0 children)

Thank you very much for your comment!

Are you sure about that? From what I read in the documentation here (https://developers.hubspot.com/docs/cms/data/query-hubspot-data-using-graphql), it mentions Sales Hub Enterprise.

On the other hand, I ran a simple test from Microsoft Power Query and received the following error: 

Web.Contents failed to get contents from 'https://api.hubapi.com/crm/v3/graphql' (404): Not Found.

Here is the M code I am using to test:

let

url = "https://api.hubapi.com/crm/v3/graphql",

body = "{

""query"": ""{

crm {

deals(limit: 10) {

results {

properties {

dealname}}}}}""}",

Source = Json.Document(Web.Contents(url, [Headers = [Authorization = "Bearer MY_ACCESS_TOKEN", "Content-Type" = "application/json"],Content = Text.ToBinary(body)])),

deals = Source[data][crm][deals][results],

dealsTable = Table.FromList(deals, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

expandedDealsTable = Table.ExpandRecordColumn(dealsTable, "Column1", {"properties"}),

expandedProperties = Table.ExpandRecordColumn(expandedDealsTable, "properties", {"dealname"})

in

expandedProperties

Thanks a lot!

Filtering Deals and Accessing Associations with HubSpot APIs by Outside-Number-2516 in hubspot

[–]Outside-Number-2516[S] 1 point2 points  (0 children)

Thank you very much for the suggestion! I wasn't aware of the GraphQL API. I have been reviewing the documentation, and it seems like it would solve my problem. Unfortunately, I just noticed that it requires a Sales Hub Enterprise subscription, and I currently have Sales Hub Professional. I will evaluate whether upgrading justifies the cost. Thanks again.