Google Apps Script to find duplicates in a Google spreadsheet

Sometimes you want to find duplicate rows in a Google spreadsheet, but don’t want to examine every column — maybe you want to exclude a timestamp or other columns.

This Google Apps Script takes a list of columns that you care about.  Then it temporarily copies those columns to a new sheet, looks for duplicates, highlights the duplicate rows in red on the main sheet, and removes the temporary sheet.

The script is attached to this Google Spreadsheet. Make a copy of this sheet, or just copy the script.  You can do that by going to Tools – Script Editor and copying the script to the script editor of your own spreadsheet. Then edit the line that defines the important columns. After you re-open your spreadsheet, the “My Scripts” menu will show up. The first time you run the script, it will bring up the permissions dialog. You’ll need to run it again to make it work.

Here’s a video of it in action:

9 thoughts on “Google Apps Script to find duplicates in a Google spreadsheet

  1. scott

    Karl,
    Thanks for the script! It was 99% what I was looking for, just need to see if I can make the search not case sensitive. :-)
    Scott

  2. karl.kranich Post author

    Scott,
    Glad to be of assistance. Probably all you need to do is go into Tools – Script editor, find this line: if (data[i].join() == data[j].join()) and change it to something like:
    if (data[i].join().toLowerCase() == data[j].join().toLowerCase())

    Karl

  3. scott

    Karl, that’s what I had in mind, but now I don’t need to lookup the function name :-) Thanks again!!
    Scott

  4. Vasu

    Great code Karl. Thanks for sharing. I was wondering if you could give advice on how to merge two separate rows in a Google Sheet that have one cell in common (ie. both row 1 and 3 of a hypothetical dataset have 1 cell that has the value 15. How to merge the two rows into one at the shared cell value)?

    Thanks in advance!
    Vasu

  5. karl.kranich Post author

    Vasu,
    How about if you create a short spreadsheet that has some examples in it for me. Maybe a few rows that should be merged, and a row showing what the data should look like after a merge (are we concatenating text, or summing numbers, etc). Then share the spreadsheet with karl.kranich@gmail.com and I’ll see what I can do.

    Karl

  6. Marjorie Frick

    I clicked on the link but it doesn’t take me to the template. It takes me to a gallery. Is there another way I can access the code?

  7. karl.kranich Post author

    Marjorie,
    I edited the post to point to a shared Google Sheet, since I’m not sure how to use the templates gallery any more.

    Karl

Leave a Reply

Your email address will not be published. Required fields are marked *