// Sheets · Apps Script

Copy a row to another sheet in Google Sheets.

Read the source row as a plain value array with getRange(row,1,1,lastCol).getValues(), then appendRow it onto the target sheet. Avoids copyTo, which drags conditional formatting and silently breaks when the target has a different column count.

“I want to move or duplicate a row from one tab to another — triggered by a status change or a button — without dragging in formatting I didn't ask for.”

The script

copy · paste · trigger
copyRowToSheet.gs
Apps Script
// Copy a row from the source sheet to an archive sheet when
// column D flips to 'done'. Uses getValues/appendRow so no
// formatting, formulas, or merged cells transfer.
const ARCHIVE_SHEET_NAME = 'Archive';
const STATUS_COL = 4;  // column D

function onEdit(e) {
  if (e.range.getColumn() !== STATUS_COL) return;
  if (e.value !== 'done') return;

  const srcSheet = e.source.getActiveSheet();
  if (srcSheet.getName() === ARCHIVE_SHEET_NAME) return;

  const row = e.range.getRow();
  const lastCol = srcSheet.getLastColumn();
  const values = srcSheet.getRange(row, 1, 1, lastCol).getValues()[0];

  const destSheet = e.source.getSheetByName(ARCHIVE_SHEET_NAME);
  destSheet.appendRow(values);
}
Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why getValues and not copyTo

Range.copyTo is the obvious candidate, and it works — until it doesn't. It copies the cell's formatting, conditional-format rules, data-validation constraints, and any merged-cell geometry alongside the values. If the target sheet has a different column count, a different background color scheme, or a header row with its own formatting, copyTo contaminates it. Every time I have watched this bite people, the symptom is a growing blob of mismatched formatting that's annoying to undo and almost impossible to trace to the original copy operation.

The alternative is to call getRange(row, 1, 1, lastCol).getValues(), which returns a plain 2D array of the cell values, nothing else. Dates come back as JavaScript Date objects, numbers as numbers, empty cells as empty strings. You then pass that array's first (and only) row to appendRow on the destination sheet. No formatting, no formulas, no validation rules transfer. The destination sheet keeps its own formatting intact.

The [0] at the end of getValues() unwraps the outer array. getValues always returns a 2D array (rows x cols); since we're reading exactly one row, we get [[val, val, val]]. Grabbing [0] gives us [val, val, val], which is what appendRow expects.

How the status trigger fits in

The script uses a simple onEdit trigger — no install step, no Triggers panel visit needed. Simple triggers fire on every edit, but the first two guards (column check, value check) discard the 99% of edits that aren't a status flip to 'done'. The execution time for a discarded edit is under 10ms.

The third guard — skipping edits that originate on the Archive sheet itself — prevents an infinite loop if someone manually edits a status cell there. Without it, editing column D in Archive would attempt to copy that row into Archive again.

Simple triggers can't write to a different spreadsheet file. This script writes to a different sheet tab within the same workbook, so a simple trigger is fine. If you need to copy rows into a completely separate spreadsheet (different file ID), switch to an installable onEdit trigger via the Triggers panel — same function body works.

Deleting the source row after copying

Archiving usually means moving, not duplicating. To delete the source row after the copy, add srcSheet.deleteRow(row) after the appendRow call. One wrinkle: deleteRow shifts all subsequent rows up by one, which is fine here because the trigger already captured the row index and we're done with it.

If you want to keep the row but clear its values (leave the row in place as a placeholder), use srcSheet.getRange(row, 1, 1, lastCol).clearContent() instead of deleteRow. That preserves row height and any background color you've set as a visual cue.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions
Why does my formula copy as a static value instead of the formula itself?
getValues() returns computed cell values, not formula strings. That's usually what you want in an archive — a snapshot of the number at the time of archiving, not a live formula that recalculates in a new context. If you specifically need the formula text, use getFormulas() instead of getValues(). Be aware that relative references in the formula will be off unless the destination sheet has the same column layout.
Can I copy to a sheet in a different spreadsheet file?
Yes, but a simple onEdit trigger can't do it — simple triggers are sandboxed and can't open other files. Install an onEdit trigger via the Triggers panel (same function body), then replace e.source.getSheetByName() with SpreadsheetApp.openById('OTHER_FILE_ID').getSheetByName('Archive'). You'll be prompted for the additional Spreadsheet scope on first run.
How do I copy the row without triggering on every status value — only on specific ones?
Change the equality check to a set lookup: const COPY_STATUSES = ['done', 'approved', 'shipped']; and replace the e.value !== 'done' guard with !COPY_STATUSES.includes(e.value). Each matching value still sends the row to the same archive; if you need different destinations per status, use an object keyed by status value to map each one to a sheet name.
What happens if the Archive sheet doesn't exist yet?
getSheetByName returns null if the sheet isn't found, and the appendRow call on null throws a TypeError that surfaces in the Executions panel. Guard against it: after the getSheetByName call, add const destSheet = e.source.getSheetByName(ARCHIVE_SHEET_NAME) || e.source.insertSheet(ARCHIVE_SHEET_NAME);. That creates the tab on first use and proceeds normally.

Related scripts