Premium Triggers and Actions - Google Sheets - Lookup Spreadsheet Row

Our GoogleSheet Lookup action lets you efficiently search and retrieve data from your Google Sheets documents. This tool supports case-sensitive searches and allows you to use custom values from any row within your workflow.

With this feature, you can automate tasks like cross-referencing data between sheets or databases, helping to minimize errors and improve efficiency. By integrating Google Sheets with your workflow processes, you can manage data more effectively and save time.

Choose a worksheet:

Select a worksheet within your spreadsheet that you wish to use for Looking up and retrieving data.

Select a Column and Lookup Value to find the matching row

The first row is considered as Headers and the same is shown as labels for each column. You can fetch the latest headers from the sheet by clicking "Refresh Headers".

Select an extra Column and Lookup Value

If an extra column is added, the values of both columns should match to determine the matching row.

Using as Custom Variables:

One advantage of using lookup is that when you search for a particular row, all of its data can be saved as a custom value that can be utilized throughout your workflow.

Consider the following worksheet as an example. If you need to search for a specific row, you can enter the contact phone number or email address in the lookup section. Once the lookup function finds the matching row, it will store all the values under the corresponding header name as custom variables.

When you add a Lookup action, an index is automatically generated. This index is essential for mapping the row data to the custom variables and allows for easy identification of multiple lookup values. It is not possible to remove or edit the index once it is added.

The values obtained from the lookup action for a particular row can be used in all other actions within your workflows, just like any other contact fields or custom variables.

Note: As previously mentioned, the index generated by the lookup function serves as a reference and is used for internal mapping of the custom variables in lookup actions.

To get the row number of the lookup value you can use {{sheet.x.rowNumber}}.

Here x is the index of the lookup action. 

Example:

The custom values of LookedUp Row can be utilized in all of the actions within your workflow. Some examples of how to do this are provided below.

In Communication Actions:

In If/Else:

Notes:

• If the Google Sheets Lookup Action is unable to find a matching row, all subsequent Google Sheets Actions that reference this Lookup Action will be skipped due to lack of a result.
• Since the Lookup Action results are available for If/Else Conditions you can use those to decide whether to create a spreadsheet row or update the spreadsheet row using Lookup, based on the Condition of having a Lookup Result or not.

Case Sensitivity Search

• By default, "Case Sensitivity Lookup" is disabled, meaning that the lookup will not take into account any case sensitivity.
• You can enable "Case Sensitivity Lookup" to do a case-sensitive lookup.

Example:

(a) If Case Sensitivity Lookup is disabled:

"John" === "john" is true and returns the matching row

(b)If Case Sensitivity Lookup is enabled:

"John" === "john" is false and does not return any matching row

Lookup Multiple Spreadsheet Row(s)

You can look up multiple rows in Google Sheets. The action type will go through the sheet and respond as output all the rows that satisfy the lookup conditions.

How to use?

• Go to Workflows and select Google Sheets Action.
• Select the account, drive, spreadsheet, and worksheet.
• Users will have an option to start the search from the bottom or top.

• Select the column you want to search and the value that you want to search.

• You can also select how many rows should be extracted in the output.

• The option to have case sensitive lookup and to create a new spreadsheet if the value is not found.

1. Extract Data by Category: Use the Google Sheets Lookup action to filter and retrieve data based on specific categories. For example, if you have a spreadsheet with customer details segmented by product interest, you can look up customers interested in a particular product and automate follow-up actions.
2. Find Leads Matching Criteria: Efficiently identify and manage leads that meet certain criteria. For instance, if you have a list of leads with various attributes, you can use the lookup action to find and update records of leads that match specific conditions, such as lead score or location.
3. Process Data with Specific Values: Automate data processing tasks by retrieving rows with certain values. If you have a spreadsheet with project tasks, you can use the lookup action to find and update tasks that are overdue or need attention, streamlining your project management workflows.

1. Refresh Headers: Always use the "Refresh Headers" option to ensure you have the latest column labels from your Google Sheets. This avoids errors caused by outdated or missing header information.
2. Enable Case Sensitivity: Turn on "Case Sensitivity Lookup" if you need to distinguish between different cases. This ensures precise matching of lookup values, which is crucial for accurate data retrieval.
3. Utilize Custom Variables: Save data from the lookup action as custom variables to streamline workflows. This allows you to use lookup results seamlessly in subsequent actions, reducing manual data handling.
4. Index Management: Note that once an index is created for a lookup action, it cannot be removed or edited. Keep track of your indexes to manage and reference the data effectively.

1. What happens if no matching row is found?
   • If the lookup action does not find a matching row, any subsequent actions that rely on this lookup will be skipped due to the lack of results.
2. Can I search for multiple rows in a Google Sheet?
   • Yes, you can configure the lookup action to find and return multiple rows that meet the lookup criteria.
3. How do I enable case-sensitive lookup?
   • By default, case sensitivity is disabled. To enable it, adjust the settings in the Google Sheets Lookup action to account for case differences in your search.
4. Can I use the lookup results in other actions within my workflow?
   • Yes, the data retrieved from the lookup action can be used in subsequent actions within your workflow, such as in communication actions or conditional logic.