the below code is awesome and remove the duplicates but there is one thing i want to change for example if i have column A and it contain duplicate values and column B contains unique values in this case the function don't remove the duplicates from Column A because some how it Join Column A & B together then it removes the duplicate.
what i need is to remove the duplicates based on Column A only whatever Column B is Unique Value or not
here is a sample sheet with dummy data
function removeDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var newData = [];
for (var i in data) {
var row = data[i];
var duplicate = false;
for (var 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);
}
the below code is awesome and remove the duplicates but there is one thing i want to change for example if i have column A and it contain duplicate values and column B contains unique values in this case the function don't remove the duplicates from Column A because some how it Join Column A & B together then it removes the duplicate.
what i need is to remove the duplicates based on Column A only whatever Column B is Unique Value or not
here is a sample sheet with dummy data https://docs.google./spreadsheets/d/13ViFiwoA_29oo-nz2LUK3CD7DiRqDRTW1blJRE6XHm4/edit?usp=sharing
function removeDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var newData = [];
for (var i in data) {
var row = data[i];
var duplicate = false;
for (var 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);
}
Share
Improve this question
edited Aug 4, 2019 at 21:57
Wicket
38.5k9 gold badges78 silver badges193 bronze badges
asked Aug 4, 2019 at 21:44
user11881817user11881817
2 Answers
Reset to default 7You want to remove the duplicated rows.
- As a sample, you want to achieve as follows.
From
aa 123 bb 12345 cc 1235 dd 167 aa 1234 <--- You want to delete this row bb 12 <--- You want to delete this row ff 89
To
aa 123 bb 12345 cc 1235 dd 167 ff 89
If my understanding is correct, how about using removeDuplicates()
? This method was added at July 26, 2019. I think that using this method, your script will bee more simple. Please think of this as just one of several answers.
Modified script 1:
function removeDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(1, 1, sheet.getLastRow(), 2); // Column A and B
range.removeDuplicates([1]);
}
Modified script 2:
If your script is modified, how about the following modification?
From:if (row.join() == newData[j].join()) {
To:
if (row[0] == newData[j][0]) {
Reference:
- removeDuplicates(columnsToCompare)
If I misunderstood your question and this was not the result you want, I apologize.
This function removes rows where there are duplicates in column A.
function removeDuplicates() {
var sh=SpreadsheetApp.getActiveSheet();
var dt=sh.getDataRange().getValues();
var uA=[];
var d=0;
for(var i=0;i<dt.length;i++) {
if(uA.indexOf(dt[i][0])==-1) {
uA.push(dt[i][0]);
}else{
sh.deleteRow(i+1-d++);
}
}
}