Would like to script on Google Sheets a way to remove certain rows

Question

The first row has a column heading: First Name, Last Name, Email, Grade, Date We have instances where people need to re-take tests and therefore will have another entry into the spreadsheet, what we are looking for, is a way that if someone re-takes the test and then adds their new data into the spreadsheet, we can automatically remove their old information

So, I have made some progress, this script removes duplicate rows, but exactly duplicate, so I need to somehow edit it to only look at the first 2 columns and then delete the oldest entry based on the Date column:

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

And how is data added into the spreadsheet? A Google Form? A Google Apps Script? Can you provide an example? — Cooper
I've never used Zapier and I have no script example from you regarding how duplicates are removed so I have no understandable point of reference to answer your question. — Cooper
When they complete a test on the platform, it inputs the user's data into the spreadsheet, with pass/fail, score and date — Craig Summers

Iamblichus Iamblichus · Accepted Answer · 2021-05-24T14:03:37

You only want to compare the first two columns in each row instead of the full row.

Therefore, you should just compare indexes 0 and 1, and not the full array (something which you are doing with join). You could just replace this line:

if (row.join() == newData[j].join()) {

With this one:

if (row[0] === newData[j][0] && row[1] === newData[j][1]) {

Edit:

If you want to keep the newest, and not the oldest, loop your 2D array in reverse instead. For example, you can use reverse when retrieving the source data:

var data = sheet.getDataRange().getValues().reverse();

Alternatively, use for to loop in reverse:

for (let i = data.length - 1; i >= 0; i--) {

More in general, consider using removeDuplicates(columnsToCompare).

Edit 2:

If you have headers, just use reverse again when setting the values in order to keep the original order:

sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData.reverse());

Would like to script on Google Sheets a way to remove certain rows

1 Answers

Edit:

Edit 2: