Replace duplicate row with new data in Google Sheets using Apps Script

Question

So, the situation goes like this. I have a sheet that is pulling some data from my WooCommerce store. It is fetching details of all orders hourly and saving them.

Now, when the status of order changes in my store, a new row will be added in the sheet with same order id and I want to replace the old row in the sheet with this new one. I dug around to look for removing the duplicates and found the following function. This is working but the problem is that it removes the new row and leaves the existing one. But I want to remove the existing one and keep the new one. I want to check for duplicate based on order id which is in 15th index of the sheet.

    function removeDuplicates() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Orders");
  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[15] == newData[j][15]){
             duplicate = true;
                 }
    }
    if (!duplicate) {
      newData.push(row);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Any help would be really appreciated. Thanks.

Answer 1

Explanation:

Your code starts scanning from the first row until it reaches the last row of the data. All unique items are stored in the newData array. When it comes across with the same item more than once it simply ignores it since it has already been added in the array.

Your goal is to keep the new records. The latter should be defined by a date column. Therefore, before you execute the for loop you can sort the array by date in descending order; that is keeping the new records (first occurrences) and ignoring all the other duplicates which you don't want. For example, if Column B is a date/datetime column indicating when the record was added, you can simply sort the data like that:

sheet.getDataRange().sort({column: 2, ascending: false}) // replace 2 with your date column

---

Solution:

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Orders");
  var data = sheet.getDataRange().getValues();
  sheet.getDataRange().sort({column: 2, ascending: false}) // replace 2 with your date column
  var newData = [];
  for (var i in data) {
    var row = data[i];
    var duplicate = false;
    for (var j in newData) {
      if(row[15] == newData[j][15]){
             duplicate = true;
                 }
    }
    if (!duplicate) {
      newData.push(row);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

---