Clean up messy sheet data and remove duplicate rows in one click

Short answer: Use a free Google Apps Script that tidies a whole tab (extra spaces, email case, phone formats) and moves duplicate rows to their own tab, so nothing is deleted. It runs in your own Google account; nothing is sent to us. Setup takes 6 steps: Check your headers; Make a Settings tab; Paste the script; Run it; What it changes; Where the duplicates go.

How to set it up

1. Check your headers. The tab you want to clean needs column names in row 1, such as Name, Email and Phone.
2. Make a Settings tab. Add a tab called Settings. In B1, type the name of the tab to clean. In B2, type the column that makes two rows the same person or item, such as Email, or several with commas, such as Name,Phone.
3. Paste the script. Open Extensions, then Apps Script, paste in the code from our email, and save. Reload the sheet and a Clean up menu appears.
4. Run it. Choose Clean up, then Clean and remove duplicates. A message tells you how many cells were cleaned and how many duplicate rows moved.
5. What it changes. Every text cell loses extra and leading spaces. Columns with email in the name become lowercase. Columns with phone in the name become (555) 123-4567 when they hold a 10 digit US number. Empty rows are dropped.
6. Where the duplicates go. The first row for each email (or each name and phone) stays. Later copies move to a Duplicates tab, so you can check them before deleting anything.

Want this set up for your business? Free written scope in 20 minutes

Get the script

Good to know

• Matching ignores capital letters and extra spaces, so ANN@X.COM and ann@x.com count as the same.
• Rows where the matching columns are all blank are never treated as duplicates.
• Make a copy of the tab first if you want the original as it was.

Questions

What does this script do?

It is a free Google Apps Script that tidies a whole tab (extra spaces, email case, phone formats) and moves duplicate rows to their own tab, so nothing is deleted.

How do I set it up?

In 6 steps: 1. Check your headers; 2. Make a Settings tab; 3. Paste the script; 4. Run it; 5. What it changes; 6. Where the duplicates go. The full steps are on this page.

Is it free, and does it send my data anywhere?

It is free: enter your email on this page and we email you the script (Code.gs). It runs in your own Google account; nothing is sent to us.

Can you install it for me?

Yes. We set it up in your Google account in a short Connect session, connect it to your sheet and check that it runs.

Want this connected to the rest of your tools? Send us a short brief.