Answers / Question 2

Delete duplicate rows but keep the latest one, with Apps Script

A fast Apps Script that keeps one row per key, the newest by date or by position, and rewrites the sheet in one go instead of deleting rows one by one.

The problem

A sheet keeps collecting rows, and the same ID or email appears again whenever something is updated. You want a script that cleans the tab in place so only the most recent row per ID is left, and you want it to be quick on thousands of rows.

Inspired by a public question on Stack Overflow. The problem above is described in our own words and the solution below is ours.

The fix

Read all rows once, decide which to keep in memory, then clear the range and write the survivors back in one call. Calling deleteRow inside a loop is slow and shifts row numbers under you, which is the usual cause of the wrong rows disappearing.

Make a copy of the sheet. The script overwrites the tab.
Open Extensions → Apps Script and paste the code.
Set keyCol (column number of the ID) and, if you have a timestamp, dateCol. With dateCol: 0, the row lowest in the sheet wins.
Run keepLatest and approve the permission prompt.

Code.gsApps Script

// Keep only the latest row for each key (e.g. one row per order ID).
// "Latest" = the newest date in dateCol if you set it, otherwise the row lowest in the sheet.
const KEEP_LATEST = {
  sheetName: 'Sheet1',
  keyCol: 3,      // column C holds the ID / email you de-duplicate on
  dateCol: 0,     // e.g. 1 for a timestamp in column A; 0 = use row position
  headerRows: 1
};

function latestPerKey(rows, keyCol, dateCol) {
  const best = new Map();
  const blanks = [];
  rows.forEach(function (row, i) {
    const key = String(row[keyCol - 1]).trim().toLowerCase();
    if (key === '') { blanks.push({ i: i, row: row }); return; }   // rows without a key are kept
    const prev = best.get(key);
    const newer = !prev || !dateCol ||
      new Date(row[dateCol - 1]).getTime() >= new Date(prev.row[dateCol - 1]).getTime();
    if (newer) best.set(key, { i: i, row: row });
  });
  return blanks.concat(Array.from(best.values()))
    .sort(function (a, b) { return a.i - b.i; })
    .map(function (x) { return x.row; });
}

function keepLatest() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName(KEEP_LATEST.sheetName);
  const n = sh.getLastRow() - KEEP_LATEST.headerRows;
  if (n < 1) return;
  const width = sh.getLastColumn();
  const range = sh.getRange(KEEP_LATEST.headerRows + 1, 1, n, width);
  const keep = latestPerKey(range.getValues(), KEEP_LATEST.keyCol, KEEP_LATEST.dateCol);
  range.clearContent();                                    // one clear + one write: fast, no shifting rows
  if (keep.length) sh.getRange(KEEP_LATEST.headerRows + 1, 1, keep.length, width).setValues(keep);
  ss.toast('Removed ' + (n - keep.length) + ' older rows');
}

Good to know

Keys are compared ignoring case and surrounding spaces. Rows with an empty key are always kept.
Values are written back as plain values, so formulas inside the cleaned range become static. Keep formula columns on another tab.
Using a timestamp column is safer than position if people ever sort the sheet.

The script's logic is unit-tested against a simulated spreadsheet before publishing. Always try new automations on a copy of your sheet first.