Answers / Question 11

Move a row to another tab when its checkbox is ticked (only that row)

An onEdit script that moves exactly the row you ticked to another tab, instead of sweeping several rows, with safe checks for pastes and unticks.

The problem

Ticking a checkbox should send that one row to a different tab and remove it from the list. Instead, the script you have moves more than one row, or rows you didn't touch.

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

Why extra rows move

Scripts that loop over the whole sheet on every edit will move every row that happens to be ticked, including ones ticked earlier or by someone else. React only to the cell in the edit event and the problem disappears.

The fix

Paste into Extensions → Apps Script, set the two tab names and the checkbox column number, and save:

Code.gsApps Script

// Move exactly one row to another tab when its checkbox is ticked.
// Reacts only to the cell that was edited, so it never sweeps up other rows.
const MOVE = {
  fromSheet: 'Open',
  toSheet: 'Done',
  checkboxCol: 17           // column Q
};

function onEdit(e) {
  const r = e.range, sh = r.getSheet();
  if (sh.getName() !== MOVE.fromSheet || r.getColumn() !== MOVE.checkboxCol || r.getRow() < 2) return;
  if (r.getNumRows() !== 1 || r.getNumColumns() !== 1 || e.value !== 'TRUE') return;
  const target = e.source.getSheetByName(MOVE.toSheet) || e.source.insertSheet(MOVE.toSheet);
  const row = sh.getRange(r.getRow(), 1, 1, sh.getLastColumn()).getValues()[0];
  target.appendRow(row);
  sh.deleteRow(r.getRow());
}

Good to know

A plain onEdit is enough here because nothing needs authorization. Don't also add an installable trigger for it, or each tick runs twice and moves two rows.
If the target tab doesn't exist yet, the script creates it.
Rows are copied as values. If the row contains formulas you need to keep, copy with copyTo instead.

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