The Hubspot_search formula lets you search across your HubSpot instance for a list of records like deals, contacts, or companies, based on specific filters. This formula returns all matching rows directly into your spreadsheet.

Demo

Syntax

Arguments

Field List Syntax (fields_list)

Filter Syntax (filter_string)

Settings

Examples

Best Practices & Tips

Common Errors

Demo

Syntax

=hubspot_search(object_type, fields, filter_string, settings)

Arguments

Field List Syntax (fields_list)

The fields parameter defines which fields to return for each matching record.

You can:

• List fields directly: "Deal Name, Amount, Close Date".
• Reference a cell range: B1:O1.
• Mix casing: It’s case-insensitive (though we recommend matching the actual HubSpot field names for readability).
• You can also include association fields by referencing the field and the association in a bracket - First Name (Associated Contact).

Field List Examples:

Direct Field Names

=hubspot_search("Deals", "Deal Name, Amount, Close Date")

Referencing Header Row

=hubspot_search("deals", B1:O1)

Pulls the fields listed from columns B to O. 

Filter Syntax (filter_string)

Supported Operators

ℹ️ NOTE: Multiple filters can be combined:

• (`condition1` and `condition2` and `condition3` ) OR (`condition4` and `condition5`)

Example:

 "(Deal Name contains Limited and Amount > 5000) or (Deal Name contains Public)"

Setttings

You can customize the results returned by the formula using optional settings in the fourth parameter. These are provided as a comma-separated string of key-value pairs.

ℹ️ NOTE: You can combine settings: "limit=10,sort=amount:desc,include_header=true"

Examples

Here are some examples of how you can use the `hubspot_search` formula in your spreadsheet:

Returns the top 10 deals sorted by amount in descending order, with column headers. 

=hubspot_search("deals", "Deal Name,Amount,Close Date",,"limit=10,sort=Amount:desc,include_header=true")

Returns all deals where the owner's email contains “charlie” and the fields are dynamically pulled from B1 to G1.

=hubspot_search("deals", B1:G1,"Deal Owner Email contains charlie")

Filters deals where the deal name matches values in cell G32 (e.g., a comma-separated list like "Deal A, Deal B").

=hubspot_search("deals","deal name, amount, close date","deal name IN "&G10)

ℹ️ Note: In this example, G10 is concatenated into the filter string with the & operator. You would want to use this to filter across multiple values. For instance, to filter all Deals with names Acme, Bravo, and Charlie, G10 can be “Acme,Bravo,Charlie”. You may also use a semicolon (;) as a separator instead of a comma.

Filters deals with amounts over 10,000 and close a date after Jan 1, 2025.

=hubspot_search("deals",B1:F1,"amount > 10000 and Close Date > 2025/01/01")

ℹ️ Note: Acceptable date formats are YYYY/MM/DD or YYYY-MM-DD

Filters deals using a more complex condition with logical grouping.

=hubspot_search("deals",B1:G1,"(amount > 400 and deal name contains Dealeg) OR (Deal name contains Govt)")

Best Practices & Tips:

• Use cell references for field lists and filters to make your formulas dynamic and reusable.
• If you intend to reference multiple values using the IN condition, you can use the =textjoin() function. See how to use the function here.
• If you intend to use a NOT condition, you could use the not in operator listed above.
  • E.g.: (Close Date <= Date Y AND Product Type not in Recurring)

Common Errors