This article will guide you through using Zapier to find and update spreadsheet rows in the same sheet.
Before you begin, familiarize yourself with Zapier's formatting requirements for Google Sheets.
1. Find specific spreadsheet rows
After you’ve set up your trigger:
- In the Zap editor, click the Action step. A dialog box will open.
- In the search box, search for and select Google Sheets. The right sidebar will open to the Setup tab.
- Click the Action event field to open the dropdown menu.
- Select Lookup Spreadsheet Row.
- Click the Account field. A popup window will open.
- Select an existing account from the list, or click + Connect a new account. Learn how to connect a new account.
- Click Continue.
- In the Configure tab, click the Spreadsheet dropdown menu, and select your spreadsheet.
- Click the Worksheet dropdown menu, and select your sheet.
- In the Lookup Column dropdown field, select the column in your spreadsheet you want the Zap to search.
- In the Lookup Value dropdown field, select the value that the Zap should search for. You can select information from a previous step.
- Click Continue.
- If you want to perform the action live to test the step, click Test step.
- The Data in tab will display the fields and values that you set up in the action step.
- The Data out tab will display the fields and values that Google Sheets returns as confirmation.
Optional field settings:
- Supporting Lookup Column and Supporting Lookup Value: You can set up another column and value to search. This ensures your Zap only finds rows that match both conditions.
-
Search from last row:
- To search from the first spreadsheet row, select False.
- To search from the last spreadsheet row, select True.
- If no search results are found: Define what happens if no matching rows are found.
-
Create Google Sheets Spreadsheet row if it doesn’t exist yet?:
- Select the checkbox if you want to create a new row.
Tip
If you need to find multiple rows, use the Lookup Spreadsheets Rows (Advanced) to retrieve up to 500 rows.
2. Updating existing rows with new information
- In the Zap editor, click the Action step. A dialog box will open.
- In the search box, search for and select Google Sheets. The right sidebar will open to the Setup tab.
- Click the Action event field to open the dropdown menu.
- Select Update Spreadsheet Row.
- In the Spreadsheet dropdown menu, select the spreadsheet. This should be the same as in the previous step.
- In the Worksheet dropdown menu, select the sheet. This should be the same as in the previous step.
- Next to the Row field , click the three dot icon . A dropdown menu will open.
- Click Custom. A modal will open. Using a custom value for the Row ID means it will dynamically update each time the Zap runs.
- In the search box, search and select the ID field from your previous Lookup Spreadsheet Row action step.
- In your column fields, map the data from your trigger or action step that you want to add to your spreadsheet. The Zap will update the selected row with this new information.
- Click Continue.
- If you do not want to test the step live, click Skip test.
After setting up your Update Spreadsheet Row action, you can continue adding more actions or publish the Zap.
Note
The Update Spreadsheet Row action step can only update one row per Zap run.
Tip
- While this workflow focuses on updating rows within a single sheet, you can apply similar logic to lookup between different sheets. To do this, you'll need to have a unique identifier, like an email address or customer reference number, that exists in both sheets. Use this identifier as the "Lookup Value " when setting up your lookup action.
- If your data is in line items and you need to update multiple rows, use the Update Spreadsheet Row(s) action.
Troubleshooting tips
You may encounter the following common error messages when using these actions:
- Google Sheets error: Required field "Row" (row) is missing
- Google Sheets error: Required field "Lookup Value" (lookup_value) is missing
- Google Sheets error: Nothing could be found for the search
- Google Sheets error: There was an error writing to your Google Sheets, unable to parse range
- Google Sheets error: Unable to get a value when looking for "responses". Is it empty or missing?