Search Column

Search a column in Google Sheets for a specific term.

The column that you're searching against must have a value in every cell, even if it's 0. The search stops if it encounters a blank cell.

Options

Field Definition Type Required

Use Spreadsheet ID?

Choose if you want to specify a Google Sheets spreadsheet using its ID.

  • No: Select a spreadsheet from the Spreadsheet option and ignore the Spreadsheet ID field.

  • Yes: Select -- Enter Spreadsheet ID -- from the Spreadsheet option and enter the ID in the Spreadsheet ID field.

The Spreadsheet ID is the last string of characters in the URL for your spreadsheet. For example, in the URL https://docs.google.com/spreadsheets/d/1qpyC0XzvTcKT6EISywvqESX3A0MwQoFDE8p-Bll4hps/edit#gid=0, the spreadsheet ID is 1qpyC0XzvTcKT6EISywvqESX3A0MwQoFDE8p-Bll4hps.

Dropdown

TRUE

Spreadsheet

A list of the spreadsheets in your Google Sheets account.

Due to an API limitation, a maximum of 100 spreadsheets appear in the dropdown menu.

If the specific spreadsheet you want to search isn't listed, use the Spreadsheet ID option.

Dropdown

TRUE

Spreadsheet ID

Unique identifier for your selected spreadsheet.

Text

FALSE

Worksheets

A list of worksheets in the spreadsheet.

Dropdown

TRUE

Column to Search

A list of columns in the worksheet.

Dropdown

TRUE

Result Set

Choose a method for your search:

  • First Matching Row: Returns the first row that matches the Search Term.

  • All Matching Rows: Returns the list of all rows that match the Search Term.

Dropdown

TRUE

Input

Field Definition Type Required

Search Term

The value that you want to search for in the column.

This is a string literal search, using an exact string match.

For example, if your spreadsheet column contains the following values:

  • jessie@example.com

  • jessiedoe@example.com

  • jessie.doe@example.com

If you set the Search Term for this column as jessiedoe@example.com, the output is 2, which is the row number where the exact match was found.

Searching for values using wild cards returns a null value, for example, jessie* or jessie*@example.com. The term must be an exact match to the value in the spreadsheet cell.

Text

FALSE

Output

Field Definition Type

Row Number

Location of the first row where the value was found.

If the exact value isn't found in the column, this field returns null.

This value is returned when you select First Matching Row for the Result Set option.

Number

Rows

A list containing all the row numbers where the value was found.

If the exact value isn't found in the column, this field returns an empty list.

This list is returned when you select All Matching Rows for the Result Set option.

List

Related topics

Google Sheets connector

Workflow elements

Google Sheets API documentation