Google Scripts delete duplicates from select range (not whole sheet)

Question

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?

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
– Aaron Irvine, Commented Aug 3, 2019 at 10:28

Jack Brown · Accepted Answer · 2018-02-07 07:38:29Z

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);
}

Michael Vatcher · Accepted Answer · 2022-08-11 21:43:26Z

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!

answered Aug 11, 2022 at 21:43

Michael Vatcher

111 bronze badge

Collectives™ on Stack Overflow

Google Scripts delete duplicates from select range (not whole sheet)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related