How to remove duplicate rows in Google Sheets using script

Question

I currently have a column of data titled JobID. In this column, there are duplicates from an import that runs daily and grabs the latest data on the JobID's in question and appends them to the top of the sheet.

Therefore the most recent JobID rows are the ones with the data we need.

I'd like to know if there is a script that can be run on the sheet called 'History' to look up the column JobID, search every row below for duplicates and remove them, leaving the top, most recent JobID rows in the sheet.

I know that it is really easy to remove duplicates using the "Remove Duplicates" tool in Google Sheets... but I'm lazy and I'm trying to automate as much of this process as possible.

The script I have below runs without an error but is still not doing what I need it to. Wondering where I am going wrong here:

function removeDuplicates() {
//Get current active Spreadsheet
 var sheet = SpreadsheetApp.getActive();
 var history = sheet.getSheetByName("History");

//Get all values from the spreadsheet's rows
 var data = history.getDataRange().getValues();
//Create an array for non-duplicates
 var newData = [];
//Iterate through a row's cells
 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 not a duplicate, put in newData array
 if (!duplicate) {
  newData.push(row);
 }
}
//Delete the old Sheet and insert the newData array
 history.clearContents();
 history.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Hey Cooper! I appreciate your interest in giving me a hand here. For the purpose of helping me out, here is a link to the sheet I am working on: link — Matias Salinas

Cooper Cooper · Accepted Answer · 2019-10-16T02:11:25

Remove Duplicate JobIDs

This function will keep the ones nearest to the top of the list. If you want to go the other way then resort the list in reverse order.

function removeDuplicates() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("History");
  var vA=sh.getDataRange().getValues();
  var hA=vA[0];
  var hObj={};
  hA.forEach(function(e,i){hObj[e]=i;});//header title to index
  var uA=[];
  var d=0;
  for(var i=0;i<vA.length;i++) {
    if(uA.indexOf(vA[i][hObj['JobID']])==-1) {
      uA.push(vA[i][hObj['JobID']]);
    }else{
      sh.deleteRow(i+1-d++);
    }  
  }
}

How to remove duplicate rows in Google Sheets using script

1 Answers