Editor’s Note: Romain Vialard is a Google Apps Script Top Contributor. He has contributed interesting articles and blog posts about Apps Script. – Jan Kleinert

December 2011, updated March 2018

This tutorial shows how to avoid duplicates when you want to automate the process of copying data in Google Workspace and specifically how to remove duplicate rows in spreadsheet data.

Google Apps Script lets you copy email attachments from Gmail to a collection in Google Docs, sync spreadsheet data with a list page in Google Sites or with Google Calendar or with your contact list. The example shown below can be used to avoid duplicates in each of those cases.

Time to Complete

Approximately 5 minutes.

Prerequisites

Before beginning this tutorial, you should feel comfortable using the Script Editor and have experience using the most basic Spreadsheet functions.

Running a simple example

Open a new spreadsheet in Google Docs or use an existing spreadsheet containing duplicates.

If the spreadsheet is empty, add a few rows of data (for example, a list of contacts, parts inventory, etc.) and duplicate some of them.

Choose the menu Tools > Script Editor.

Copy and paste the following script:

Save the script.

Select the function removeDuplicates in the function dropdown list and click Run.

Take a look at your spreadsheet. There should be no more duplicates.

How the script works

First, we do a single call to the spreadsheet to retrieve all the data. We could have read our sheet row by row, but JavaScript operations are considerably faster than talking to other services like Spreadsheet. The fewer calls you make, the faster it goes. This is important because each script execution has a maximum run time of 6 minutes.

Our variable data is a JavaScript 2-dimensional array that contains all the values in our sheet. newData is an empty array where we put all rows which are not duplicates.

The first for loop iterates over each row in the data 2-dimensional array. For each row, the second loop tests if another row with matching data already exists in the newData array. If it is not a duplicate, the row is pushed into the newData array.

To finish, the script deletes the existing content of the sheet and inserts the content of the newData array.

Variation

In the example above, the script finds a duplicate when there are two identical rows, but you may also want to remove rows with matching data in just one or two of the columns. To do that, you can change the conditional statement.

This conditional statement finds duplicates each time two rows have the same data in the first and the second column of the sheet.

Reuse the method

Each time you need to copy data from one point to another you may want to check if this data has already been copied. For example, if you want to sync a label in Gmail to a collection in Google Docs to automatically retrieve important attachments, the solution shown above can be used.

1. First, retrieve the data you want to copy (from Gmail, Calendar, a spreadsheet, etc.)
2. Then, retrieve the data already stored in the targeted folder, spreadsheet, site, etc.
3. For each item you want to copy:
   • Make the assumption that you want to copy this item by the use of a boolean, for example: var toCopy = true; .
   • For each item stored in the targeted folder, check if it looks similar to the item you want to copy.
   • If it looks like the item has already been copied, then you don’t want to copy it again, so set toCopy = false; .
   • Once you have read all items stored in the targeted folder, check if toCopy is equal to true or not. If it’s true, then copy the item.

There are other solutions to avoid duplicates. For example, you can tag each item as ‘already processed’ once they have been copied. An example can be found in this tutorial: Sending emails from a Spreadsheet. Or, you can remove the item from the list of items to copy.

Summary

Congratulations, you’ve completed this tutorial. You should now be able to avoid duplicates when manipulating data with Apps Script.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Updated on January 3, 2021 by Swayam Prakash

Data redundancy is a common problem in database applications like Excel and Google Sheets. During manual data entry in the worksheet, some info may get recorded more than once. This leads to data duplication or redundancy. In this guide, I will show you how to remove duplicate data from Google Sheets.

I have mentioned a few tricks to highlight and get rid of similar information from the Google Sheet. There is a function that is native to Sheets. People commonly use this to find and remove repetitive information. Also, there is a dedicated in-built add-on that helps to remove duplicate data in Sheets. Every option I have mentioned is free to use.

Highlight and Remove Duplicates from Google Sheets

You can use any of the methods I have mentioned below.

Remove Duplicates Tool

This is very easy. Suppose I have a list of bikes and their engine displacement. If there is one bike and its displacement details repeated twice, then this tool will remove one of it

• Select the dataset in the Google Sheet. You may notice the data collection has two values from each data set repeated.
• Click on the option Data in the menu bar > scroll down to Remove Duplicates
  
• Then click the checkbox Data Has Header Row
• Also, click on the checkbox Select All
  
• Finally, click on Remove Duplicates. You will see the below message of the removal of duplicate data.
  

If you ask me, this above method is the easiest method to get the job done in removing duplicate entries in the Google Sheet. However, there is a catch. If the duplicate entry has a spelling error or you have up put any space, then the two duplicate values will not match. Hence, the Remove Duplicates tool will not work.

Remove Duplicates Using the UNIQUE Function

Google Sheets has another easy function that can help weed out the duplicate entries in the worksheet.

• Simply put the cursor on any blank cell
• Type this formula =UNIQUE(A2:B10)
  
• Press Enter

A2 and B10 are just examples. If you notice in the screenshot above, these cell numbers are the initial cell of one dataset and the final cell of the second dataset. You can accordingly mention the cell number in the formula as per your Google Sheet database.

Then after pressing enter the redundant value will be omitted and a new list will show up. This list won’t have any duplicate entries. See, how simple was that.?

Using Add-Ons to Remove Duplicates in Google Sheets

In Google sheets, you can also use add-ons to remove duplicate entries from the worksheet. First, you have to install the Add-on. It is free to download.

• Open Google Sheets
• In the menu bar, click on Add-ons
• Then select Get Add-ons
  
• Then in the search-box type Remove Duplicates
• In the results, click on the Remove Duplicates from AbleBits
  
• Then click Install and use the same Gmail account that you use to work on Google Sheets to give the add-on permissions.
  

Now, let’s see how to use the add-ons. It’s pretty straight-forward.

• Select the dataset in the sheet
• Then in the menu bar click on Add-On
• Under that expand to Remove duplicates > click Find Duplicate or Unique Rows
  
  
• Now you have to go through 4-steps.
• In the 1st, click on the checkbox that says Create A Backup of the sheet and check that the range of the dataset has been mentioned correctly.
  
• Then click Next
• In the 2nd step, click on the radio button Duplicates and click Next
  
• Next, in the 3rd step select the checkboxes Skip Empty Cells, My table has headers and Match case
  
• In the last and 4th step select the radio button Delete Rows Within Selection.
• Then click on Finish
  
• You will see a message that the duplicate row in the dataset has been removed. That’s it.
  

So, that’s how you remove duplicates from Google Sheets using various functions and add-ons. All the methods are quite easy to carry out. I hope that this guide was informative.

If you want to remove duplicates in Google Sheets, you need to use the UNIQUE function. This function extracts all the unique values and puts it in another place on the sheet.

This is very different than the way you can remove duplicates in MS Excel. In Excel, there is an option in the data tab to remove duplicates.

This Article Covers:

Remove Duplicates in Google Sheets

Suppose you have a dataset as shown below:

In this data, there are repetitions in names (Mark and Brenda).

Now let’s see how you can use the UNIQUE function to remove duplicates in Google Sheets.

Example 1: Remove Duplicates from a Single Column

Let’s say you only want to remove the duplicates from the Name column.

Here are the steps to do this:

• Select a cell where you want to get the list of unique items/names.
• Enter the following formula: = UNIQUE ( A2:A10 )
• Hit Enter.

This would instantly remove the duplicates and give you a list of unique names.

Note: If you want to remove the list, delete the first cell, or select the entire range and hit delete. Google Sheets will not let you delete individual cells (other than the first one – which would delete the entire list).

Since this is a formula, it would automatically update if you make any changes in the original list.

UNIQUE function returns a #REF! error if there is already some data in the cells that are supposed to be filled by the unique function.

If you hover the mouse over the cell with error, it will show a message stating the issue.

Handling Leading and Trailing Spaces

Another issue you may face is when there are leading or trailing spaces. For example, if the example below, there is a trailing space in Brenda (in A10). When the UNIQUE function is used on this data set, it considers both the names as unique and returns both the names in the result.

Here is how to handle this:

• Go to the cell where you want to get the list of unique items/names.
• Enter the following function: = UNIQUE ( TRIM ( A2:A10 ) )
• While in the eidt mode, press Ctrl + Shift + Enter. It would change the formula to = ArrayFormula ( UNIQUE ( TRIM ( A2:A10 ) ) ).
• Press Enter.

This would automatically account for any leading, trailing, or double spaces and give you the final result after removing these.

Example 2: Remove Duplicates from Multiple Columns

If you have more than one column, you can use the above method to remove duplicate rows and get unique data.

Suppose you have the data as shown below:

In this data, there are two duplicate rows (highlighted in orange and green).

Here are the steps to remove these duplicate rows:

• Select a cell where you want to get the list of unique items/names.
• Enter the following formula: = UNIQUE ( A2:B10 )
• Hit Enter.

If there are leading, trailing or double spaces, use the following formula:

I hope this helps you cleaning your data in Google Sheets. Do let me know your thoughts by leaving a comment.

You May Also Like the Following Google Sheets Tutorials:

1 thought on “How to Remove Duplicates in Google Sheets”

Duplicate values can be annoying many of the times. As I work on excel regularly and seldom spending find few duplicate cells or vale kills a lot of time. This tutorial is handy to remove all the duplicates at once and be more productive. I love excel tricks. Thanks!

Khamosh Pathak

17 Sep 2014

Google Spreadsheet (now called Sheets as part of the Google Drive productivity suite) is turning out to be a real MS Excel competitor. The amount of stuff Excel can do is truly mind-boggling. But Sheets is catching up. More importantly it is beating Excel by integrating add-ons and scripts in an easy to use manner. Plus there’s a long list of functions for quick calculations.

Using Google Sheets to do serious work means dealing with a serious amount of data. Data that’s often not sorted or organized. In such times you want an easy way to get rid of duplicate data entries. And let’s face it, searching for and deleting recurring data sets manually is inexact and time consuming.

Thanks to Sheet’s support for add-ons and functions, this process can be taken care of in mere seconds.