Overview
You can automatically remove duplicate rows in Google Sheets by building a Zap that detects new data as it arrives in your spreadsheet from multiple sources and deletes any matching row that already exists. The Zap would use a Lookup Spreadsheet Row action to search for an existing match, then a Delete Spreadsheet Row action to remove the duplicate. This approach keeps your spreadsheet clean on an ongoing basis without any manual effort.
Step-by-step guide
- Create a new Zap and set your trigger. In the Zap editor, choose Google Sheets as the trigger app and select New Spreadsheet Row as the trigger event. Connect your Google account and select the spreadsheet and worksheet you want to monitor.
- Add a Lookup Spreadsheet Row action. Add a new action step, choose Google Sheets, and select Lookup Spreadsheet Row as the action event. Select the same spreadsheet and worksheet as your trigger. Set the Lookup Column to the field you want to use to detect duplicates - for example, an email address, order ID, or name. Map the corresponding value from your trigger step into the Lookup Value field. Set Search from last row to False so the Zap searches from the top of the sheet rather than starting from the trigger row.
- Add a Filter step to check if a duplicate was found. Add a Filter by Zapier step and set it to only allow the Zap to continue if the Row of the row it found Does not exactly match the Row of the row that triggered the Zap. If only the recently added row at the bottom of the sheet is found, the Zap stops here and the new row is kept.
- Add a Delete Spreadsheet Row action. Add another Google Sheets action and choose Delete Spreadsheet Row. Map the row number returned by the trigger into the Row field. This tells the Zap to delete the duplicate row that was just added.
- Test your Zap. Turn on the Zap then add a duplicate row to your sheet manually to run the Zap and confirm it finds and deletes the newly added duplicate row.
Key notes and limitations
- This Zap is reactive, not retroactive - it removes duplicates as new rows are added, but will not clean up duplicates that already exist in your sheet before the Zap is turned on.
- The Lookup Spreadsheet Row action searches for the first matching row it finds. If there are multiple duplicates already in the sheet, only one will be deleted per Zap run. To handle multiple matching rows use the Lookup Spreadsheet Rows (Advanced) and Delete Spreadsheet Row(s) actions instead which can handle multiple rows.
- Deleting a row may cause other Zaps using the New or Updated Spreadsheet Row trigger tied to the same worksheet to re-trigger on every row below the deleted row. To avoid this, consider using the Clear Spreadsheet Row action instead, which clears the data from the row while keeping the row itself intact.
- Make sure the lookup column contains values that are genuinely unique identifiers - using a column with repeated values by design (such as a status or name field) will cause false positives.
Frequently asked questions
Q: Will this Zap delete the original row or the duplicate?
A: The Zap deletes the duplicate row that was added to the bottom of the spreadsheet keeping the original intact. This is necessary because deleting a row higher up in the sheet affects the numbers of all the rows below it, which would cause the Zap to re-trigger on those existing rows.
Q: Can I run this on a schedule to check for duplicates regularly?
A: Yes. You can use a Schedule by Zapier trigger to run the Zap at regular intervals. You would then need a different approach to search for newly added rows, this is more complex and may require a multi-step Zap using Looping by Zapier.
Q: What if my sheet already has duplicates before I turn the Zap on?
A: To clean up existing duplicates directly in your spreadsheet, you can use the built-in Google Sheets tool: select your data range, go to Data, then Data cleanup, then Remove duplicates - the How to find and remove duplicates in Google Sheets guide on our blog covers this in more detail.
Q: How do I remove duplicates across multiple columns?
A: The Supporting Lookup Column and Supporting Lookup Value fields on the Lookup Spreadsheet Row action let you find match on a second column. If you need to match on a combination of fields, consider adding a helper column to your sheet that concatenates the values you want to check (for example, combining first name and last name into one cell), then use that helper column as your lookup field.
Q: Does this work with Google Sheets shared with my team?
A: Yes, as long as the Google account connected to your Zap has edit access to the shared sheet.