Archive rows, don’t delete them

TL;DR — Archive Google Sheet rows instead of deleting them: move to an Archive tab under a lock, keep headers intact, and leave an audit trail you can actually use.

Delete feels decisive until a client asks where order 1842 went. Soft-delete flags help; a dedicated Archive tab is clearer for operators who live in the spreadsheet. Here's the pattern we paste into bootstrapper workbooks: copy the row, append under matching headers, delete from the live sheet — under a lock, with a trail you can filter later.

TL;DR

Prefer move to Archive over hard delete for operational data.
Same headers on live and Archive (ensure once; don't drift).
Copy values → append → delete source (never delete first).
When batching, delete from the bottom up so indexes don't shift.
Lock the job; log who/what/when when you can.

Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.

Why is delete usually the wrong default?

Hard delete is fine for true junk (test rows, obvious spam). For anything a human might ask about later — orders, leads, tasks, tickets — you want:

A place to look ("it's on Archive, filtered to last week")
Undo-ish recovery without Apps Script version archaeology
Proof you didn't invent a disappearance

Sheets aren't a database with ON DELETE CASCADE. Treat destructive actions like they leave fingerprints.

What should the Archive tab look like?

Start identical to the live tab's headers. Optional extras if you outgrow the basics:

…live columns… | archivedAt | archivedBy | archiveReason

Keep it boring. If Archive becomes a dumping ground with different schemas per year, future-you will hate present-you.

function ensureArchiveSheet_(ss, liveSheet, archiveName) {
  archiveName = archiveName || 'Archive';
  var archive = ss.getSheetByName(archiveName);
  var liveHeaders = liveSheet.getRange(1, 1, 1, liveSheet.getLastColumn()).getValues()[0];

  if (!archive) {
    archive = ss.insertSheet(archiveName);
    archive.appendRow(liveHeaders.concat(['archivedAt', 'archivedBy', 'archiveReason']));
    archive.setFrozenRows(1);
    return archive;
  }

  ensureHeaders(archive, liveHeaders); // throws if required live cols missing
  return archive;
}

How do you move one row safely?

Read → append → delete. Refuse to touch the header row.

function moveRowToSheet(sourceSheet, rowNumber, targetSheet) {
  if (rowNumber < 2) {
    throw new Error('moveRowToSheet: refuse to move header row');
  }
  var lastCol = sourceSheet.getLastColumn();
  if (lastCol < 1) throw new Error('moveRowToSheet: source sheet has no columns');

  var values = sourceSheet.getRange(rowNumber, 1, 1, lastCol).getValues()[0];
  targetSheet.appendRow(values);
  sourceSheet.deleteRow(rowNumber);
  return targetSheet.getLastRow();
}

Enrich before append if you track metadata:

function archiveRow(liveSheet, rowNumber, reason) {
  var ss = liveSheet.getParent();
  var archive = ensureArchiveSheet_(ss, liveSheet, 'Archive');
  var lastCol = liveSheet.getLastColumn();
  var values = liveSheet.getRange(rowNumber, 1, 1, lastCol).getValues()[0];
  var email = Session.getActiveUser().getEmail() || 'unknown';

  return withDocumentLock(function () {
    // Re-read under lock if you're paranoid about concurrent edits
    values = liveSheet.getRange(rowNumber, 1, 1, lastCol).getValues()[0];
    archive.appendRow(values.concat([new Date(), email, reason || '']));
    liveSheet.deleteRow(rowNumber);
    return archive.getLastRow();
  });
}

How do you archive many rows without index chaos?

deleteRow shifts everything below upward. If you collect row numbers [2, 5, 9] and delete 2 first, 5 and 9 are no longer those rows.

Rule: sort descending; delete highest row number first.

function archiveRowsByNumbers(liveSheet, rowNumbers, reason) {
  var unique = rowNumbers
    .map(Number)
    .filter(function (n) { return n >= 2; });
  unique = unique.filter(function (n, i, arr) { return arr.indexOf(n) === i; });
  unique.sort(function (a, b) { return b - a; }); // descending

  var archived = 0;
  unique.forEach(function (rowNumber) {
    archiveRow(liveSheet, rowNumber, reason);
    archived++;
  });
  return archived;
}

For huge batches, prefer: filter rows into a values array, setValues onto Archive in one block, then delete the live rows bottom-up (or clear+rewrite the live sheet from remaining rows — often faster than thousands of deleteRow calls).

When is a status column better than moving?

Soft-delete (status = archived) keeps formulas, charts, and FILTER views simple — one tab. Move-to-Archive keeps the live tab short and operator-friendly.

Hybrid that works well:

Set status = archived (and archivedAt)
Nightly job moves status = archived rows to Archive and clears them from live

Operators get immediate feedback; the sheet stays lean overnight.

function nightlySweepArchived() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var live = ss.getSheetByName('Tasks');
  var data = live.getDataRange().getValues();
  var headers = data[0];
  var statusCol = headers.indexOf('status');
  if (statusCol === -1) throw new Error('missing status column');

  var toMove = [];
  for (var r = 1; r < data.length; r++) {
    if (String(data[r][statusCol]).toLowerCase() === 'archived') {
      toMove.push(r + 1); // 1-based sheet row
    }
  }
  return archiveRowsByNumbers(live, toMove, 'nightly-sweep');
}

What about permissions and "can we undo?"

Editors who can edit live can usually edit Archive — hide the tab if you only want power users poking it (hiding is not security).
True undelete: copy the Archive row back with an unarchiveRow helper (append to live, delete from Archive). Same copy-then-delete discipline.
Don't put secrets in archived cells either — Archive gets shared whenever the file does.

Minimal test plan

Archive one row → appears on Archive with same values; gone from live; headers untouched
Refuse row 1 (header) with a clear error
Batch [2,3,4] with descending deletes → all three land once, none skipped
Overlapping archive runs under lock → no half-moved rows
Optional metadata columns populate (archivedAt / archivedBy)

Soft Co-Pilot note

If you're dropping this into another client file, NitroGAS keeps moveRowToSheet, ensureHeaders, and lock helpers as snippets — Co-Pilot helps wire a custom menu "Archive selected row" when you want click-ops. Free extension; Co-Pilot optional. The move-don't-delete habit matters more than the tooling.

Closing checklist

Archive tab exists with aligned headers (+ optional metadata)
Single-row helper uses copy → append → delete
Batch path deletes bottom-up
Document lock around multi-step moves
Menu or nightly sweep documented for operators
No silent hard-delete left on the happy path

Happy Coding!