Approval workflows in Sheets often need the approved record to live somewhere separate from the working intake list, and this example handles that split automatically the instant an Approved checkbox is ticked on the Intake tab.
Without automation, someone has to remember to manually select the row, copy it, switch tabs, and paste it into an Approved Archive sheet - a step that gets skipped under deadline pressure and leaves the archive out of sync.
The onEditCopyApprovedRow function below watches column F specifically, reads the first five columns of the matching row, and appends them to an archive tab it creates on first use if one doesn't already exist.
By the end you'll have a trigger that keeps two sheets in sync automatically, plus a visible 'Copied' timestamp so nobody re-approves the same row twice by accident.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Column | Sheet | Purpose |
|---|---|---|
| A-E | Intake | Name, Email, Amount, Notes, SubmittedOn - the fields that get archived |
| F | Intake | Approved checkbox - the watched column that fires the copy |
| G | Intake | Copied timestamp written back after archiving |
| A-E | Approved Archive | Destination for the appended row, created automatically if missing |
What it does
The trigger fires on every edit to the Intake sheet but only acts when the edited cell is column F, the Approved checkbox. Once it confirms the checkbox is TRUE and the row hasn't already been copied, it appends a five-column snapshot of that row into Approved Archive.
- Ignores edits to any column other than the Approved checkbox
- Creates the Approved Archive tab with headers on its first run
- Stamps column G so a re-check of the same box doesn't duplicate the row
Prerequisites
The function assumes a specific column layout on Intake, so any reordering of columns A through F needs a matching update to statusColumnIndex and the getRange(row, 1, 1, 5) call.
- A sheet named exactly 'Intake' with Approved as a checkbox in column F
- Edit permissions for the script to create new sheets via insertSheet
- An installed onEdit trigger (simple triggers can't create sheets, so this needs an installable one)
Walkthrough
After confirming the sheet name and edited column, the function reads the row number from e.range.getRow() and immediately checks the checkbox's actual value rather than trusting e.value, since e.value can be undefined for some edit types like paste.
e.source.getSheetByName looks for Approved Archive; when it's missing, insertSheet creates it and appendRow writes a header row before the real data row is appended right after.
Edge cases
Unchecking and rechecking the box re-triggers the guard, but because copyFlagColumn already has a value, you'd want an extra check if you don't want a second archive row after an unapproval-reapproval cycle.
- Pasting a block of checkboxes fires onEdit once per affected cell, not once per paste
- Deleting a row shifts row numbers but doesn't fire onEdit for the rows below it
- Sorting the Intake sheet after archiving can make the G-column stamp point at the wrong logical row if row order changes
Testing
Tick the Approved checkbox on a test row and confirm both that the row appears in Approved Archive and that column G shows a 'Copied' stamp within a second or two.
- Try editing a non-F column to confirm nothing happens
- Untick and retick the same box to observe the duplicate-row behavior
- Delete the Approved Archive sheet and re-approve a row to confirm auto-creation still works
Hardening
Right now the function trusts that column F is always the checkbox, which breaks silently if someone inserts a column to the left of it.
- Look up the Approved column by header text instead of a hard-coded index
- Add a re-approval guard that checks copyFlagColumn before appending again
- Wrap appendRow in a try/catch so a locked or protected archive sheet doesn't throw an unhandled error on every edit
Variations
The same appendRow pattern works for splitting rows across multiple destination sheets based on a category column instead of a single Approved/not-approved boolean.
- Route rows to a sheet named after the value in a 'Region' column instead of one fixed Approved Archive tab
- Copy the row into a different spreadsheet entirely using SpreadsheetApp.openById
- Combine with generate-unique-id to stamp an ID on the row before it's archived
Full code: onEditCopyApprovedRow()
The function treats the checkbox column as the single source of truth for what counts as 'approved', reading its live value from the sheet rather than relying on the edit event's payload.
function onEditCopyApprovedRow(e) {
var sheet = e.range.getSheet();
if (sheet.getName() !== 'Intake') return;
var editedColumn = e.range.getColumn();
var statusColumnIndex = 6;
if (editedColumn !== statusColumnIndex) return;
var row = e.range.getRow();
if (row === 1) return;
var isApproved = sheet.getRange(row, statusColumnIndex).getValue();
if (isApproved !== true) return;
var rowValues = sheet.getRange(row, 1, 1, 5).getValues()[0];
var archive = e.source.getSheetByName('Approved Archive');
if (!archive) {
archive = e.source.insertSheet('Approved Archive');
archive.appendRow(['Name', 'Email', 'Amount', 'Notes', 'SubmittedOn']);
}
archive.appendRow(rowValues);
var copyFlagColumn = 7;
sheet.getRange(row, copyFlagColumn).setValue('Copied ' + new Date().toLocaleString());
}- Line 3: Restricts the entire function to edits on the Intake sheet so other tabs' checkboxes don't trigger a copy.
- Line 6: Hard-codes column F (6) as the Approved checkbox column that this trigger cares about.
- Line 10: Skips header-row edits since row 1 never holds real intake data.
- Line 13: Re-reads the checkbox value from the sheet instead of trusting e.value, which is safer for pasted or scripted edits.
- Line 16: Looks up the archive sheet by name, returning null if it hasn't been created yet.
- Line 22: Appends the five-column snapshot to the archive sheet, creating a new row at the bottom.
- Line 25: Writes a human-readable 'Copied' timestamp back into column G of the source row.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before relying on this copy trigger
- 1Approved checkbox confirmed to be in column F on Intake
- 2Approved Archive header row matches the five copied columns
- 3Installable onEdit trigger created (not just a simple trigger)
- 4Re-approval behavior tested and accepted or guarded against
- 5Script has permission to create new sheets
- 6Archive sheet protected from accidental manual edits
Frequently asked questions
e.value is only populated for simple, single-cell edits typed directly into the cell; pasted values or checkbox toggles from a script can leave it undefined, so re-reading the cell with getRange().getValue() is more reliable.
Yes - change the 1, 5 in the getRange call to however many columns you need, and update the header row written on sheet creation to match.
Add a sheet.deleteRow(row) call after archive.appendRow(rowValues), but be aware that deleting the row shifts every row below it up by one, so do this as the very last step.
Yes, installable onEdit triggers fire regardless of whether the edit came from the web app, mobile app, or the Sheets API, since they're tied to the spreadsheet's change events.
Check whether column G already has a value before appending, and return early if it does - the current version only guards against re-copying by leaving that check to be added during hardening.
No - the trigger exits immediately for any edit outside column F, so the overhead on unrelated edits is a single column comparison, not a full sheet scan.