Delete every duplicate google sheet entry except the last one

Hi @husayngokal, welcome to the Community! 👋
 

I have a form that populates a Google Sheet each time someone fills it. To tackle duplicate entries, I want to delete every entry except the latest one from a participant - this zap will run every time someone fills the form.

I am unable to find a clear way to select every record before the latest one in the search for duplicate entries. In this case, since “3” is the highest record from the search, I want to delete every record that the search found before “3”. There’s only one other here - “2”.

Instead of deleting the latest row from a specific user who has previously filled out the form, a better approach might be to have a two spreadsheets for this. The current one that is linked to the form which would keep a record of every form submission that takes place. And another spreadsheet that the Zap will add or update rows in - resulting in a duplicate free list of the latest form entries.

• Trigger: New Spreadsheet Row (Google Sheets) - triggers when a new entry is added into spreadsheet 1 (e.g. the one that’s linked to the form)
• Action: Lookup Spreadsheet Row (Google Sheets) - searches spreadsheet 2 (e.g. duplicate free list) for the person using a unique value, like their email address for example, to see if they’ve already got an entry. It has the ability to create a new row if an existing row wasn’t found so this would take care of cases where it’s an entry from someone new.
• Action: Only continue if... (Filter by Zapier) - checks the _zap_search_was_found_status field output by the previous action was true (meaning it found an existing entry for that person). See our Add conditions to Zaps with filters guide to learn more about using filters.
• Action: Update Spreadsheet Row (Google Sheets) - updates the row that was found by the Lookup Spreadsheet Row action. You’d need to use the custom value option to select (from the Lookup Spreadsheet Row action) the ID row that needs to be updated. See our Add custom values to dropdown menu fields in Zaps guide for more details on using that option.

While this approach wouldn’t run through and remove all the existing duplicates from the spreadsheet it would mean that spreadsheet 2 remains duplicate free going forward. Do you think this approach above could work for your needs here?
 

PS: How do I test a full run of a zap?

You could use the Test steps option in the Zap editor to have test all of the steps all at once. You can learn more about how to do that here: Test all steps Alternatively, you could switch on the Zap and run a live test by performing relevant action that would trigger the Zap.

Hope that helps. If I've misunderstood what you're looking to do or you run into any issues in setting this up just let us know. Always happy to help further!