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 Template. Open the script with Tools – Script Editor, and copy 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:

6 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

Leave a Reply

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