The salesforce_search formula lets you query Salesforce objects like Leads, Opportunities, or Accounts using filters directly from your spreadsheet. The formula returns all matching records in a tabular format.
Demo
Syntax
=salesforce_search(object_type, fields, filter_string, settings)
Arguments
| Arguement | Required? | Description |
| object_type | YES | Salesforce object you want to query. (Opportunities, Leads, Accounts). Not case sensitive |
| fields | YES | A comma-separated list of fields to return. You can also reference a cell range (A1:A10) |
| filter_string | Optional | A string representing the filter logic to apply. If omitted, all records are returned. |
| settings | Optional | The ability to limit and sort the output as well as include headers. |
Field List Syntax (fields)
The fields parameter defines which fields to return for each matching record.
You can:
- List fields directly: "Name", "Stage Name", "Amount"
- Reference a range of cells: B1:G10
- Mix casing: Field names are not case sensitive, but we recommend matching Salesforce names for clarity.
- Include related fields: You can use field from related objects, by using the format Field Name (Relation). Example: Email (Owner).
Direct Field Names
=salesforce_search("Opportunity", "Name, Lifestyle Stage, Amount")
Referencing Header Row
=salesforce_search("Opportunity", B1:G10)
Pulls the fields listed from Columns B to G.
Filter Syntax (filter_string)
Supported Operators
| Operator | Description | Example |
| = | Equals (works for text, numbers, and dates) | "Stage=Closed Won" |
| > | Greater than (numbers or dates) | "Amount>1000" |
| < | Less than (numbers or dates) | "Close Date<2024-12-01" |
| >= | Greater than or equal to (numbers or dates) | "amount>=5000" |
| <= | Lesser than or equal to (numbers or dates) | "Close Date<=2024-12-31" |
| IN | Is one of (comma-separated list) | "Stage in Prospecting, Qualification" |
| NOT IN | Is not one of (comma-separated list) | "Name not in John, Jane" |
| CONTAINS | Text contains (not case-sensitive) | "Name contains renewal" |
ℹ️ Pro Tip: You can combine multiple filters using AND, OR, and parentheses for grouping.
Example: "(Stage=Proposal AND Amount >5000) OR (Name contains Enterprise)"
Settings
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.
| Setting | Description | Example |
| limit | Max number of results to return | limit=10 |
| sort | Sort by a field, ascending (asc) or descending (desc) | sort=Amount:desc |
| include_header | Whether to include column headers in the output. Defaults to false. | include_header=true |
ℹ️ Pro Tip: Multiple settings may be combined: "limit=10,sort=Amount:desc,include_header=true"
Examples
Returns opportunities sorted by amount in descending order with column headers (we have omitted the filter parameter):
=salesforce_search("Opportunity", "Name,Stage,Amount",,"sort=amount:desc, include_header=true")
Returns fields specified by cells B1:F1 in leads where the email contains "support":
=salesforce_search("Lead", B1:F1, "Email contains support")
Filters opportunities with stage "Proposal" and amount greater than 25000:
=salesforce_search("Opportunity", "Name,Stage,Amount", "Stage = Proposal and Amount > 25000")
Filters opportunities based on a dynamic list of names stored in cell G10:
=salesforce_search("Opportunity", "Name,Amount,Close Date", "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 Opportunities 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.
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. For guidance on using the function, please click here.
- If you intend to use a NOT condition, you could use the `not in` operator listed above.
- Example: (Close Date <= Date Y AND Product Type not in Recurring)
Common Errors
| Error Message | Cause |
| Internal error executing the custom function | This error happens in Google Sheets when a formula takes longer than 30 seconds to execute. Try executing the formula again. |
| Salesforce connection requires reauth | Open the sidebar and reauthorize Salesforce for the formula to continue to work. |
| Invalid object type | The object name doesn’t match any in your Salesforce schema. |
| Invalid field name | One or more fields don't exist in the object you queried. |
| Filter syntax error | The filter_string is formatted incorrectly. |