Google Sheet Script: How to remove duplicate but only apply to the older entries?

Question

I have a spreadsheet that pulls in data from a 3rd party source. The new data is added and appended to the same sheet everyday. I have set up a trigger onChange with the script below:

function removeDuplicates() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange("A:Z");
range.removeDuplicates([1,2]);
}

E.g. column A is date, column B is Name.

For example, my sheet looks like this when new data is added.

Name	Date	Value
John	3-Feb-21	100
Tom	3-Feb-21	200
Sam	3-Feb-21	300
John	4-Feb-21	100
Tom	4-Feb-21	200
Sam	4-Feb-21	300
Tom	4-Feb-21	201
Sam	4-Feb-21	301

The last two rows are the new entries, which will deleted if I run my current script. Is there a way to make sure that it only removes the older entries? e.g.

Name	Date	Value
Tom	4-Feb-21	200
Sam	4-Feb-21	300

The desired result should be:

Name	Date	Value
John	3-Feb-21	100
Tom	3-Feb-21	200
Sam	3-Feb-21	300
John	4-Feb-21	100
Tom	4-Feb-21	201
Sam	4-Feb-21	301

Answer 1

Explanation:

• removeDuplicates starts from the top so it will remove the most recent values, but you want to keep the recent values and delete the older ones.

• Therefore I used TextFinder to find the last rows at which each name appears.

• You mentioned that you have an onChange trigger installed for this function. Please keep in mind that onChange trigger responds only to user changes. Namely, if a script makes changes to the file, the onChange trigger will never fire. Therefore, I would advice you to put the code of this answer to your current script that fetches data, so after the update of the data, the code will remove the duplicates.

Solution:

I think your desired result should be this :

If that is the case, then this code will work for you:

function removeDups(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheets()[0];
  const names = sh.getRange('A2:A'+sh.getLastRow()).getValues().flat()
  const unames = [...new Set(names)];
  const fdata = [];     
  unames.forEach(name=>{
        let dataFinder = sh.createTextFinder(name);         
        let res = dataFinder.findAll(); 
        if(res.length>0){
          let matched = sh.getRange(res[res.length-1].getRow(), 1, 1, 3).getValues();
          fdata.push(matched.flat());
        };        
   });
   sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).clearContent();
   sh.getRange(2,1,fdata.length,fdata[0].length).setValues(fdata);
}

Updated Solution:

function removeDups(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheets()[0];
  const rng = sh.getRange("A2:C"+sh.getLastRow());
  const raw_data = rng.getValues().reverse();
  rng.setValues(raw_data);
  rng.removeDuplicates([1,2]);
  const sorted_rng= sh.getRange("A2:C"+sh.getLastRow());
  const sorted_data = sorted_rng.getValues().reverse();
  sorted_rng.setValues(sorted_data);  
}