// Sheets · Apps Script

Move a row to another sheet on edit in Google Sheets.

Use Apps Script's onEdit trigger to automatically move a row to a different sheet when a status cell changes to "Done" — without duplicating data or copying the wrong row.

“I want to automatically move a row to another sheet the moment I mark it Done, without manually cutting and pasting or running a script by hand.”

The script

copy · paste · trigger

moveRowOnEdit.gs

Apps Script

// Move a row to 'Done' sheet when column C is set to 'Done'
// Trigger: onEdit (simple trigger, no OAuth required)
function onEdit(e) {
  var WATCH_COL = 3;       // column C
  var STATUS    = 'Done';
  var DEST_NAME = 'Done';

  var range = e.range;
  if (range.getColumn() !== WATCH_COL) return;
  if (e.value !== STATUS) return;

  var sheet = range.getSheet();
  if (sheet.getName() === DEST_NAME) return;

  var row     = range.getRow();
  var dest    = e.source.getSheetByName(DEST_NAME);
  var rowData = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues();

  dest.appendRow(rowData[0]);
  sheet.deleteRow(row);
}

Generate yours

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

Walkthrough

Why the delete must come after the append

The first time I hit this, I called deleteRow before appendRow and spent ten minutes wondering where my data went. When you delete row 5 first, every row below it shifts up by one. If your rowData variable already holds the values, you are safe — but if you were re-reading from the sheet after the delete, you would pull row 6's data thinking it was still row 5. Always append first, delete second. The script above captures rowData into a variable before either operation, so the order technically does not matter for correctness, but keeping append-then-delete is the readable convention and prevents accidents if you ever refactor.

The sheet.getLastColumn() call on line 17 is worth noting: it reads however many columns your source sheet actually uses, so you do not need to hardcode a column count. If you add a column next month, the script still copies it without edits.

Gating on the right cell without looping every row

Apps Script fires onEdit on every single keystroke that commits a value — every cell change in the entire spreadsheet. The first two guards (column check, value check) exit immediately if the edit is not the one you care about. This keeps the trigger fast; a full sheet scan on every edit is the most common reason people complain that their Sheets scripts make the spreadsheet feel sluggish.

e.range gives you the exact cell that changed. e.range.getColumn() returns a 1-based integer, so column A is 1, column B is 2, column C is 3. e.value is the new value as a string. If your status is a dropdown, the value you compare against must match the dropdown option exactly, including capitalisation. 'done' will not match 'Done'.

The third guard — checking that the active sheet is not already the destination — prevents an infinite loop if someone edits a cell in the Done sheet itself.

Installing the trigger and scoping limits

Open Extensions > Apps Script, paste the function, and save. Because onEdit is a reserved simple trigger name, Google wires it automatically. You do not need to set up a trigger manually in the Triggers panel, and you do not need to authorise any OAuth scopes — simple triggers run with the permissions of the user who is actively editing.

The tradeoff: simple triggers cannot do anything that requires elevated permissions (sending email, calling external URLs, writing to a different user's sheet). If you need those, convert to an installable trigger: go to the Triggers panel, add a new trigger, set it to onEdit (spreadsheet), and save. Installable triggers run under your account's permissions and prompt for OAuth once. The rest of the script stays identical.

One real gotcha I have watched bite people who share sheets with a team: simple triggers only fire for the user who is editing. If a collaborator changes the status column, the trigger fires under their session. That is usually fine. But if you convert to an installable trigger, it always fires under the account that created the trigger, regardless of who made the edit — which matters for permissions and audit logs.

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

The row disappears from the source sheet but never shows up in the destination sheet — what happened?: getSheetByName returns null if the sheet name does not match exactly, including spacing and capitalisation. appendRow on null throws a silent error and the delete still runs. Add a null check: if (!dest) { SpreadsheetApp.getUi().alert('Sheet not found: ' + DEST_NAME); return; } before the appendRow line while you are debugging.
Can I move the row to a sheet in a completely different spreadsheet?: Yes, but you need an installable trigger (not a simple onEdit) because opening a different spreadsheet requires the spreadsheets OAuth scope. Replace e.source.getSheetByName with SpreadsheetApp.openById('YOUR_SHEET_ID').getSheetByName('Done') and the rest of the logic is identical.
How do I watch multiple status values and route them to different sheets?: Replace the single if (e.value !== STATUS) return check with a lookup object: var routes = { 'Done': 'Done', 'Archived': 'Archive', 'Rejected': 'Rejected' }; var destName = routes[e.value]; if (!destName) return; Then use destName instead of the hardcoded DEST_NAME string. Each status routes to its own sheet.
The script moves the row but my ARRAYFORMULA in the source sheet breaks after rows are deleted — is there a fix?: ARRAYFORMULA recalculates from the top of its range after a deleteRow, which is expected behaviour. The usual fix is to put the formula in a column that is not part of the data range you copy, or to switch from ARRAYFORMULA to per-row formulas so each row carries its formula with it when it moves. Alternatively, copy the destination row values-only using getValues and setValues instead of appendRow, which strips formulas before writing.