javascript - Check for duplicate entry with Google Sheet for specific columns

Question

I have a Google Sheet that gets regularly updated thanks to a Zapier call. Every so often a new line is added to the sheet and i would like to check the rest of the sheet for any duplicates and remove the duplicate(s) if found. The possible duplicates will be only on some fields, for example:

1. an entry is added with: Title = "My super duper night run", Distance = "3.6", Time = "24.3"
2. there is already an entry with: Title = "night run", Distance = "3.6", Time = "24.3"
3. there is already an entry with: Title = "My super duper night run", Distance = "5.5", Time = "45.1"

In the above scenario number 1 would be kept, number 2 would be deleted, number 3 would be kept. Note: Ideally the macro/script would run after each update but could run every so often and just scour the whole list looking for duplicates, although would need to keep the latest entry and remove the older ones.

I have some experience with VBA and have built basic Sheets scripts but not sure how to build the code to loop through the sheet (bottom up) and then remove when duplicates are found.

Answer 1

Assuming that a match of columns 2 and 3 is a duplicate then this should work

function deleteDups() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const startrow=2;
  const rg=sh.getRange(startrow,1,sh.getLastRow()-startrow+1,sh.getLastColumn());
  const vs=rg.getDisplayValues();//this get the data as string
  let uA=[];//unique array
  let d=0;//delete counter
  vs.forEach(function(r,i){
    let p=r[1]+r[2];//adding two strings together is a string
    //uA is an array of strings
    if(uA.indexOf(p)==-1) {
      uA.push(p);//uA is made up of all the unique strings
    }else{
      sh.deleteRow(i+startrow-d++);//the delete counter makes up for the rows that get deleted because the index into the  data remains the same even though the row is now gone.
    }
  });
}