1

Below is a Google Sheets script for deleting duplicate rows from a spreadsheet. While it does work in removing duplicate rows it also destroys in-cell formulas in the process. I currently have a series of sheets where all of the raw data is contained within Columns A:P and all of my formulas are relegated to Columns Q:T.

In my attempts to limit the following script to work only on Columns A:P I receive a missing argument error upon running the script. 

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange("A2:P").getValues();

TL:DR, I love the script, but I want to limit the range upon which it is ran. Any help? Thank you.

https://developers.google.com/apps-script/articles/removing_duplicates

function removeDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().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.clearContents();
sheet.getRange(1, 1, newData.length, 
newData[0].length).setValues(newData);
}

EDIT: I believe the following is above a valid question, but I would like to add a new observation that may have some impact on what's happening(??). When I initiate a find and replace from within sheets to remove all instances of the word 'null' then sheets removes null and leaves every other cell alone. However if I run a script to remove 'null' it reformats all of my cells changing dates & times to decimals. etc. Is there a means by which to run a script and avoid overall unintended actions such as sheet wide reformatting?

Kos
  • 4,890
  • 9
  • 38
  • 42
user3846962
  • 31
  • 1
  • 2
  • 7
  • I <3 you dudes! I was really struggling with my own script for deleting duplicates but a quick edit of this function to suit my spreadsheet and it works perfectly! You really should accept the answer and give Jack props if he solved your problem! – Aaron Irvine Aug 03 '19 at 10:28

2 Answers2

5

You will need to use getRange() function to specify the range on which you want to work with. 

var sheet = SpreadsheetApp.getActiveSheet();
var rng = sheet.getRange("A2:P")
var data = rng.getValues();

Also, you clear the entire sheet with this line sheet.clearContents(); instead use this

rng.clearContents()

That way you only clear contents in the range specified by rng
Your final code should look like this

function removeDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var rng = sheet.getRange("A2:P")
var data = rng.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);
}
}
rng.clearContents();
sheet.getRange(1, 1, newData.length, 
newData[0].length).setValues(newData);
}
Jack Brown
  • 5,802
  • 2
  • 12
  • 27
1

It's been a few years since the answer was provided. For some reason this wasn't working for me now.

I removed the "s," making rng.clearContents(); into rng.clearContent();

Also the range start from row 1 to row 2 making sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData); into sheet.getRange(2, 1, newData.length, newData[0].length).setValues(newData);

Worked for me. Thank you!

Michael Vatcher
  • 11
  • 1