CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user11881817
user11881817

Reputation:

Google app script remove duplicate based on one Column

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.com/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);
}

Upvotes: 2

Views: 4160

Answers (2)

Cooper
Cooper

Reputation: 64140

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

Upvotes: 2

Tanaike
Tanaike

Reputation: 201703

  • You 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 become 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:

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 7

Related Questions