dup-data

Remove Duplicates Google Sheets With App Script

Firstly In this article, you can learn how you Remove Duplicate Google Sheets with the help of App script. So, this article explains how you can write a simple script in google sheets to remove duplicate entries.
Therefore, large spreadsheets sometimes may have duplicate entries. Similarly, these duplicates can cause lots of problems in your data. Therefore, before using this data it makes sense to remove all duplicate entries.

Hence, there are two ways to remove duplicate entries in google sheets.

  • Firstly, using the built-in function: UNIQUE()
  • Secondly, using Google App Script.

Remove Duplicate in Google Sheets

Using Unique Function

Many people install add-ons to remove duplicates from sheets. But actually, Google sheet has built-in formula function which can remove duplicates. So, you can remove duplicates in google sheets using function Unique().

Note:

The unique function actually does not remove duplicates from the original data range. It just filters out unique items from selected range and populates to cells where the formula is applied.
So, you can replace original data with unique values populated by copy and paste as values

Example: Let’s say you want to get unique items from column A only, row number 2 to 30 and populate this in I column from 2nd row.

In cell I2 you will write formula as: =UNIQUE(A2:A30)

Remove Duplicate

So, you can Also read: Managing Files in Google Drive App

Likewise, if you want to have unique rows for entire range A2: G30. Therefore, you need to pass this range as a parameter in the Unique function.

Using App Script

Here, you can also use App Script to remove duplicates from sheets. Because App script is basically javascript. We need to write a javascript in script editor of the sheet.
Furthermore, you can go to the script editor by clicking Tools->Script editor

So, following function get unique items from range A2: A30 on active sheet and populates from cell I2

function removeDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getRange('A2:G30').getValues()
var newData = new Array();
for(i in data){
var row = data[i];
var duplicate = false;
for(j in newData){
if(row.join() == newData[j].join()){
duplicate = true;
}
}
if(!duplicate){
newData.push(row);
}
}

sheet.getRange(2, 9, newData.length, newData[0].length).setValues(newData);
}

But, if you want to replace original data with unique rows you need to use this code.

function removeDuplicates(){ 
 var sheet = SpreadsheetApp.getActiveSheet(); 
 var data = sheet.getRange('A2:G30').getValues();
 var newData = new Array(); 
 for(i in data){
 var row = data[i]; var duplicate = false; 
 for(j in newData){ 
  if(row.join() == newData[j].join()){ 
   duplicate = true; 
  } 
 } 
 if(!duplicate){ 
  newData.push(row); 
 } 
 } 
 sheet.getRange('A2:G30').clearContent() 
 sheet.getRange(2, 1, newData.length, newData[0].length).setValues(newData);
} 

42 Responses

Add a Comment

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