Copy a row when a checkbox is checked

1 · Prepare

Put these pieces in place

• Open your Google Sheet and create two tabs named Students and Tracking.
• On the Students tab, put headers in row 1: Name in column A, Email in column B, Active in column C, and Tracked in column D. Column C holds checkboxes, added with Insert, then Checkbox.
• On the Tracking tab, put headers in row 1: Name in column A, Email in column B, and Added At in column C.

2 · Review before running

What the script can access and change

3 · Copy

Paste this into the Apps Script editor

Create or open a script file, replace the example values marked above, then save the project.

const SOURCE_SHEET = 'Students'; // Tab where people check the Active checkbox.
const TRACKING_SHEET = 'Tracking'; // Tab where new tracking rows are appended.
const CHECKBOX_COLUMN = 3; // Column C holds the Active checkbox (true when checked).
const STATUS_COLUMN = 4; // Column D stores Tracked so re-checking does not duplicate the row.
const TRACKED_MARKER = 'Tracked'; // Value written to column D after the tracking row is created.

/** @param {Events.SheetsOnEdit} e */
function onEdit(e) {
  if (!e || !e.range) return;
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() !== SOURCE_SHEET) return;
  if (range.getNumRows() > 1 || range.getNumColumns() > 1) return;
  if (range.getColumn() !== CHECKBOX_COLUMN) return;

  const editedRow = range.getRow();
  if (editedRow < 2) return;
  if (e.value !== 'TRUE') return;

  const rowValues = sheet.getRange(editedRow, 1, 1, STATUS_COLUMN).getValues()[0];
  const name = rowValues[0];
  const email = rowValues[1];
  const currentStatus = String(rowValues[STATUS_COLUMN - 1] ?? '').trim();
  if (currentStatus === TRACKED_MARKER) return;

  const trackingSheet = e.source.getSheetByName(TRACKING_SHEET);
  if (!trackingSheet) {
    throw new Error('Create a tab named ' + TRACKING_SHEET + ' before checking boxes.');
  }

  const stamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm');
  trackingSheet.appendRow([name, email, stamp]);
  sheet.getRange(editedRow, STATUS_COLUMN).setValue(TRACKED_MARKER);
}

4 · Run

Follow these steps in order

1. Add one test row with a checkbox

   On the Students tab, enter a student name in A2 and an email in B2. Select C2 and choose Insert, then Checkbox. Leave D2 blank.

2. Paste the onEdit function and save

   Choose Extensions, then Apps Script. Replace the starter code with this script and save. A function named onEdit is a simple trigger, so it runs on every edit in this spreadsheet with no extra setup and no need to click Run.

3. Check the box in C2

   Return to the Students tab and check the box in C2. Within a second or two, a new row appears on the Tracking tab and D2 changes to Tracked.

4. Check that a second click adds nothing

   Uncheck C2 and check it again. Because D2 already says Tracked, the script skips the row and adds no second row to Tracking. Clear D2 only when you want the checkbox to add another row.

5 · Confirm

How to tell it worked

Checking the Active box in C2 on the Students tab adds one row to the Tracking tab and writes Tracked in D2.

If the result is different

Troubleshooting

Checking the box adds nothing to the Tracking tab.

Check that the tab names match Students and Tracking exactly, including capital letters, and that the cell in column C is a checkbox added with Insert, then Checkbox. A cell holding the typed word TRUE is not a checkbox.

Clicking Run in the editor does nothing, or shows "TypeError: Cannot read properties of undefined".

Test the script by checking a box in the spreadsheet instead. The Run button calls onEdit with no event, so the first line stops the function.

Checking the box a second time does not add another row.

Clear cell D2 on the Students tab if you want checking C2 to add another row. The script skips any row whose column D says Tracked.

Selecting several checkboxes and pressing Space copies none of the rows.

Check one box at a time. The script skips any edit that covers more than one cell, so each row is copied and marked only when its box is checked on its own.