Remove duplicates based on one column and keep latest entry in google sheets

user_18141792 0
Viewed 465

I was working on some automation and wanted to remove the duplicate rows in my google sheet by comparing it on basis of 3rd column. I found one code which is working flawlessly but it does not remove the old entry in sheet, it removes the latest one. I wanted to keep the latest one from the duplicates.

This is the code which I found for appscript by Cooper:

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][2])==-1) {
      uA.push(dt[i][2]);
    }else{
      sh.deleteRow(i+1-d++);
    }
  }
}

Can anyone help me with the code which does the same work "removing duplicate rows (Keeps latest entry removes the old entry) based on column" ?

google-apps-script google-sheets scripting automation
2 Answers

From I wanted to keep the latest one from the duplicates., when the latest one is the last row, in your script, how about the following modification?

Modified script:

function removeDuplicates() {
  var sh = SpreadsheetApp.getActiveSheet();
  var dt = sh.getDataRange().getValues();
  var uA = [];
  for (var i = dt.length - 1; i >= 0; i--) {
    if (uA.indexOf(dt[i][2]) == -1) {
      uA.push(dt[i][2]);
    } else {
      sh.deleteRow(i + 1);
    }
  }
}
user_71086530

My suggestion:

function myFunction() {
  var sh = SpreadsheetApp.getActiveSheet();

  // get values of column C
  var col = sh.getDataRange().getValues().map(x => x[2]);

  // get indexes of duplicated values in the column
  var duplicates = col.map((x,i) => 
    col.slice(i+1).includes(x) ? i+1 : '').filter(String); 

  // remove rows by the indexes
  duplicates.reverse().forEach(x => sh.deleteRow(x));
}

Before:

enter image description here

After:

enter image description here

Update

If there will some glitches it makes sense to add the command flush() after every deleteRow(). The last line of the code should be like this:

  // remove rows by the indexes
  duplicates.reverse().forEach(x => { sh.deleteRow(x); SpreadsheetApp.flush() });
user_142654690
Related