Test Apps Script code before it touches real data

Run the rules of a reminder script on your own computer in about a second, with no Google account, then run one short check inside Apps Script before the script creates a single Gmail draft. The example drafts renewal reminders from a Renewals sheet and records which rows it has handled. You will test its date math, row validation, and email text with Node.js, test its Sheets logic with fake objects, and test its edit trigger with a hand-built event.

This chapter builds on Develop Apps Script locally and uses its Node.js versions and, for clasp uploads, its account, target, and upload-review procedure.

Why a script that writes needs tests

A function that only logs can be rerun until it looks right. A function that sends email or overwrites cells cannot. A wrong date comparison sends a customer a reminder a month early, and a wrong column index writes a status over a column of email addresses. The editor's Run button has no preview for either.

Running the script against a copy of the spreadsheet only tests the rows that happen to be in the copy. Tests let you keep the awkward cases, such as a renewal exactly 14 days out or a row already handled, and rerun all of them after every change.

A test is code that calls your function with a known input and fails loudly when the output differs from the expected value. In this chapter, tests run in two places:

Neither place sends email. The only step that touches Gmail is the real draftRenewalReminders run, and it creates drafts that you review before anything is sent.

Split the code into rules and wrappers

Node.js does not have SpreadsheetApp or GmailApp, but code that only works with values (strings, numbers, dates, arrays, and objects) runs anywhere JavaScript runs. Put the decisions in pure functions, which compute a result only from their arguments and change nothing outside themselves, and keep the service calls in short wrapper functions that pass values in and act on the result.

The example project uses two Apps Script files:

• Reminders.gs holds the pure rules: the expected header row, row validation, calendar-day math, the reminder email text, the decision about which rows need a reminder, and the decision about which rows an edit invalidates. It never calls an Apps Script service.

• Code.gs holds the service wrappers: reading the sheet, creating Gmail drafts, writing the Reminder drafted date, the onEdit trigger, and the editor smoke test.

The split is a convention that the code follows; Apps Script does not enforce it. Apps Script loads every .gs file in a project into one global scope, as the V8 runtime guide describes, so Code.gs can call planReminders_ without an import.

// Pure renewal rules. Nothing in this file calls an Apps Script service,
// so the same source runs in Apps Script and in the local Node.js tests.

const RENEWAL_HEADERS_ = ['Customer', 'Email', 'Renewal date', 'Status', 'Reminder drafted'];
const RENEWAL_STATUSES_ = ['Active', 'Renewed', 'Cancelled'];
const REMINDER_WINDOW_DAYS_ = 14;
const RENEWAL_DATE_COLUMN_ = 3;
const REMINDER_DRAFTED_COLUMN_ = 5;

/**
 * @typedef {Object} RenewalRow
 * @property {string} customer
 * @property {string} email
 * @property {Date} renewalDate
 * @property {string} status
 * @property {boolean} reminderDrafted
 */

/**
 * @typedef {Object} ReminderEmail
 * @property {number} rowNumber
 * @property {string} to
 * @property {string} subject
 * @property {string} body
 */

/**
 * @typedef {Object} RowProblem
 * @property {number} rowNumber
 * @property {string} message
 */

/**
 * @param {*} value
 * @returns {boolean}
 */
function isValidDate_(value) {
  return Object.prototype.toString.call(value) === '[object Date]' && !isNaN(value.getTime());
}

/**
 * Returns an error message for a header row, or an empty string when it matches.
 *
 * @param {Array<*>} header
 * @returns {string}
 */
function headerProblem_(header) {
  const matches =
    header.length === RENEWAL_HEADERS_.length &&
    header.every(function (value, index) {
      return value === RENEWAL_HEADERS_[index];
    });
  return matches ? '' : 'Row 1 must be: ' + RENEWAL_HEADERS_.join(', ') + '.';
}

/**
 * Validates one sheet row. Returns either a parsed row or a problem message.
 *
 * @param {Array<*>} values
 * @param {number} rowNumber
 * @returns {{row: RenewalRow} | {problem: RowProblem}}
 */
function validateRenewalRow_(values, rowNumber) {
  const customer = typeof values[0] === 'string' ? values[0].trim() : '';
  const email = typeof values[1] === 'string' ? values[1].trim() : '';
  const renewalDate = values[2];
  const status = values[3];
  const drafted = values[4];

  /** @param {string} message */
  function fail(message) {
    return { problem: { rowNumber: rowNumber, message: message } };
  }

  if (customer === '') {
    return fail('Customer is empty.');
  }
  if (!/^[^\s@]+@[^\s@]+$/.test(email)) {
    return fail('Email must look like name@example.com.');
  }
  if (!isValidDate_(renewalDate)) {
    return fail('Renewal date must be a date cell.');
  }
  if (typeof status !== 'string' || RENEWAL_STATUSES_.indexOf(status) === -1) {
    return fail('Status must be one of: ' + RENEWAL_STATUSES_.join(', ') + '.');
  }
  if (drafted !== '' && !isValidDate_(drafted)) {
    return fail('Reminder drafted must be empty or a date.');
  }
  return {
    row: {
      customer: customer,
      email: email,
      renewalDate: renewalDate,
      status: status,
      reminderDrafted: drafted !== '',
    },
  };
}

/**
 * Counts calendar days from today to the target date, ignoring the time of day.
 * Uses the runtime's local calendar date for both values.
 *
 * @param {Date} today
 * @param {Date} target
 * @returns {number}
 */
function daysUntil_(today, target) {
  const start = Date.UTC(today.getFullYear(), today.getMonth(), today.getDate());
  const end = Date.UTC(target.getFullYear(), target.getMonth(), target.getDate());
  return Math.round((end - start) / 86400000);
}

/**
 * @param {Date} date
 * @returns {string}
 */
function formatIsoDate_(date) {
  const month = String(date.getMonth() + 1).padStart(2, '0');
  const day = String(date.getDate()).padStart(2, '0');
  return date.getFullYear() + '-' + month + '-' + day;
}

/**
 * @param {RenewalRow} row
 * @param {number} daysLeft
 * @param {number} rowNumber
 * @returns {ReminderEmail}
 */
function buildReminderEmail_(row, daysLeft, rowNumber) {
  const when = daysLeft === 0 ? 'today' : daysLeft === 1 ? 'tomorrow' : 'in ' + daysLeft + ' days';
  return {
    rowNumber: rowNumber,
    to: row.email,
    subject: 'Your subscription renews ' + when,
    body: [
      'Hello ' + row.customer + ',',
      '',
      'Your subscription renews on ' + formatIsoDate_(row.renewalDate) + ' (' + when + ').',
      'Reply to this email if you want to change or cancel it.',
    ].join('\n'),
  };
}

/**
 * Decides which data rows need a reminder. Does not read or write anything.
 *
 * @param {Array<Array<*>>} dataRows Rows below the header, in sheet order.
 * @param {Date} today
 * @returns {{reminders: ReminderEmail[], problems: RowProblem[]}}
 */
function planReminders_(dataRows, today) {
  /** @type {ReminderEmail[]} */
  const reminders = [];
  /** @type {RowProblem[]} */
  const problems = [];

  dataRows.forEach(function (values, index) {
    const rowNumber = index + 2;
    if (values.every(function (value) { return value === ''; })) {
      return;
    }
    const result = validateRenewalRow_(values, rowNumber);
    if ('problem' in result) {
      problems.push(result.problem);
      return;
    }
    const row = result.row;
    if (row.status !== 'Active' || row.reminderDrafted) {
      return;
    }
    const daysLeft = daysUntil_(today, row.renewalDate);
    if (daysLeft >= 0 && daysLeft <= REMINDER_WINDOW_DAYS_) {
      reminders.push(buildReminderEmail_(row, daysLeft, rowNumber));
    }
  });

  return { reminders: reminders, problems: problems };
}

/**
 * Returns the data rows whose Reminder drafted cell must be cleared after an
 * edit, or null when the edit does not touch a renewal date below the header.
 *
 * @param {number} row First edited row.
 * @param {number} column First edited column.
 * @param {number} numRows
 * @param {number} numColumns
 * @returns {{firstRow: number, numRows: number} | null}
 */
function reminderRowsToReset_(row, column, numRows, numColumns) {
  const lastColumn = column + numColumns - 1;
  if (column > RENEWAL_DATE_COLUMN_ || lastColumn < RENEWAL_DATE_COLUMN_) {
    return null;
  }
  const firstRow = Math.max(row, 2);
  const lastRow = row + numRows - 1;
  if (lastRow < firstRow) {
    return null;
  }
  return { firstRow: firstRow, numRows: lastRow - firstRow + 1 };
}

Some details in this file exist so it behaves the same in both runtimes:

• isValidDate_ uses Object.prototype.toString instead of instanceof Date, which also recognizes a Date created in another JavaScript realm.

• daysUntil_ converts each local calendar day with Date.UTC before subtracting. Raw timestamps differ by 23 or 25 hours across a daylight saving change.

• formatIsoDate_ builds 2026-05-10 from getFullYear, getMonth, and getDate because Utilities.formatDate does not exist in Node.js.

• planReminders_ returns problems as data instead of throwing, so one mistyped address does not block every other reminder, and a test can assert on the exact problem list.

The wrapper file keeps each service call small and passes everything it can to those rules:

// Service wrappers. These functions touch SpreadsheetApp or GmailApp, and they
// pass the work they can delegate to the pure rules in Reminders.gs.

const RENEWAL_SHEET_NAME_ = 'Renewals';

/**
 * Creates one Gmail draft per Active renewal due within 14 days that has no
 * Reminder drafted date, then records today's date in that row.
 *
 * @returns {void}
 */
function draftRenewalReminders() {
  const sheet = getRenewalSheet_();
  const result = applyReminderPlan_(
    sheet,
    function (email) {
      GmailApp.createDraft(email.to, email.subject, email.body);
    },
    new Date(),
  );
  console.log(JSON.stringify(result));
}

/**
 * Reads the sheet, plans reminders, calls createDraft for each one, and marks
 * each row immediately after its draft exists.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @param {function(ReminderEmail): void} createDraft
 * @param {Date} today
 * @returns {{drafted: number[], problems: RowProblem[]}}
 */
function applyReminderPlan_(sheet, createDraft, today) {
  const values = sheet.getDataRange().getValues();
  const problem = headerProblem_(values[0]);
  if (problem !== '') {
    throw new Error(problem);
  }
  const plan = planReminders_(values.slice(1), today);
  /** @type {number[]} */
  const drafted = [];
  plan.reminders.forEach(function (email) {
    createDraft(email);
    sheet.getRange(email.rowNumber, REMINDER_DRAFTED_COLUMN_).setValue(today);
    drafted.push(email.rowNumber);
  });
  return { drafted: drafted, problems: plan.problems };
}

/**
 * Simple trigger: clears Reminder drafted when someone edits a Renewal date,
 * so the next run can draft a reminder for the new date.
 *
 * @param {{range: SpreadsheetApp.Range}} e The edit event; only its range is read.
 * @returns {void}
 */
function onEdit(e) {
  resetRemindersAfterEdit_(e, RENEWAL_SHEET_NAME_);
}

/**
 * @param {{range: SpreadsheetApp.Range}} e The edit event; only its range is read.
 * @param {string} sheetName The only sheet whose edits matter.
 * @returns {void}
 */
function resetRemindersAfterEdit_(e, sheetName) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() !== sheetName) {
    return;
  }
  const rows = reminderRowsToReset_(range.getRow(), range.getColumn(), range.getNumRows(), range.getNumColumns());
  if (rows) {
    sheet.getRange(rows.firstRow, REMINDER_DRAFTED_COLUMN_, rows.numRows, 1).clearContent();
  }
}

/**
 * @returns {SpreadsheetApp.Sheet}
 */
function getRenewalSheet_() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  if (!spreadsheet) {
    throw new Error('Run this from the Apps Script project bound to the test spreadsheet.');
  }
  const sheet = spreadsheet.getSheetByName(RENEWAL_SHEET_NAME_);
  if (!sheet) {
    throw new Error('Add a sheet named Renewals and import renewals.csv into it.');
  }
  return sheet;
}

/**
 * Editor smoke test. Writes fixture rows to a temporary sheet in the bound test
 * spreadsheet, runs the real Sheets code with a recording function in place of
 * Gmail, checks the results, and deletes the temporary sheet. Creates no drafts.
 *
 * @returns {void}
 */
function smokeTestReminders() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  if (!spreadsheet) {
    throw new Error('Run this from the Apps Script project bound to the test spreadsheet.');
  }
  const today = new Date(2026, 4, 1);
  const sheet = spreadsheet.insertSheet('Smoke test ' + Date.now());
  try {
    sheet.getRange(1, 1, 4, 5).setValues([
      RENEWAL_HEADERS_,
      ['Ada Studio', 'ada@example.com', new Date(2026, 4, 10), 'Active', ''],
      ['Lin Bakery', 'lin@example.com', new Date(2026, 5, 30), 'Active', ''],
      ['Kai Garage', 'not-an-email', new Date(2026, 4, 3), 'Active', ''],
    ]);

    /** @type {ReminderEmail[]} */
    const sent = [];
    const result = applyReminderPlan_(
      sheet,
      function (email) {
        sent.push(email);
      },
      today,
    );

    expectEqual_(result.drafted, [2], 'drafted rows');
    expectEqual_(result.problems.map(function (p) { return p.rowNumber; }), [4], 'problem rows');
    expectEqual_(sent.map(function (email) { return email.to; }), ['ada@example.com'], 'draft recipients');
    expectEqual_(sent[0].subject, 'Your subscription renews in 9 days', 'subject');
    const marked = sheet.getRange(2, REMINDER_DRAFTED_COLUMN_, 3, 1).getValues();
    expectEqual_(
      marked.map(function (row) { return row[0] instanceof Date ? formatIsoDate_(row[0]) : row[0]; }),
      ['2026-05-01', '', ''],
      'Reminder drafted column',
    );

    const secondRun = applyReminderPlan_(sheet, function () {}, today);
    expectEqual_(secondRun.drafted, [], 'second run drafts nothing');

    sheet.getRange('C2').setValue(new Date(2026, 4, 12));
    resetRemindersAfterEdit_({ range: sheet.getRange('C2') }, sheet.getName());
    expectEqual_(sheet.getRange('E2').getValue(), '', 'date edit clears Reminder drafted');

    console.log('Smoke test passed.');
  } finally {
    spreadsheet.deleteSheet(sheet);
  }
}

/**
 * @param {*} actual
 * @param {*} expected
 * @param {string} label
 * @returns {void}
 */
function expectEqual_(actual, expected, label) {
  const actualJson = JSON.stringify(actual);
  const expectedJson = JSON.stringify(expected);
  if (actualJson !== expectedJson) {
    throw new Error('Smoke test failed: ' + label + '. Expected ' + expectedJson + ', got ' + actualJson + '.');
  }
}

applyReminderPlan_ takes the sheet, a createDraft function, and today's date as arguments instead of calling SpreadsheetApp.getActiveSpreadsheet(), GmailApp.createDraft, and new Date() itself. This is called dependency injection: the caller supplies the objects the function depends on. The real entrypoint, draftRenewalReminders, passes the real sheet, a function that calls GmailApp.createDraft, and the current date. A test passes a fake sheet, a function that records what it was asked to draft, and a fixed date.

Inside the loop, the function writes the Reminder drafted date immediately after each draft exists. If Gmail fails on the third reminder, the first two rows are already marked, and the next run does not draft them again. A test below checks that behavior with a simulated failure.

The manifest requests access to the bound spreadsheet and to Gmail:

{
  "timeZone": "Etc/UTC",
  "runtimeVersion": "V8",
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://mail.google.com/"
  ]
}

Google lists https://mail.google.com/ as the scope for GmailApp.createDraft. That scope allows full access to the account's mail, so run this project with a test account. The timeZone field sets the script time zone; the setup steps below match it to the spreadsheet's time zone.

Load the same source in Node.js

The local tests must exercise the exact files you upload. A copy of the rules pasted into a test file would drift away from the uploaded code while its tests kept passing.

The module.exports guard and why this book does not use it

A common suggestion is to end the rule file with a guard that only runs in Node.js:

if (typeof module !== 'undefined') {
  module.exports = { planReminders_: planReminders_ };
}

In Apps Script, module is not defined, so the guard skips the export at runtime. The problem appears in type checking. When this book's checker found that assignment in Reminders.gs, it treated the file as a CommonJS module, and the functions declared in it stopped being globals. Every use in Code.gs then failed with errors such as Cannot find name 'planReminders_'. The guard trades a working Apps Script project view for a convenient Node.js import.

Evaluate the files with node:vm

The approach the example uses leaves the .gs files unchanged. The test file reads them as text, joins them in project order, and evaluates the result inside a function that returns the names the tests need:

const gs = vm.runInThisContext(
  '(function () {\n' + source + '\nreturn { planReminders_, onEdit };\n})',
  { filename: 'apps-script-project.gs' },
)();

The complete test file lists every function it tests. Each part has a reason:

• Joining the files reproduces Apps Script's shared global scope. Code.gs can call planReminders_ in Node.js for the same reason it can in Apps Script. Keep the files in the order Apps Script would load them; the V8 guide recommends defining functions and classes before other files use them at the top level.

• The surrounding function keeps const RENEWAL_HEADERS_ and every function declaration out of Node's global object. Only the returned object is visible to the tests.

• vm.runInThisContext compiles the code and runs it with the current global object, without access to the test file's local variables. Because the code runs in the same realm as the tests, arrays and objects it returns share the test file's Array.prototype and Object.prototype. vm.runInNewContext would create a separate realm, and assert.deepStrictEqual compares prototypes with ===, so an array from the other realm never equals an array literal in the test. Node.js reports that mismatch as Values have same structure but are not reference-equal.

• The filename option names the combined source in stack traces. Line numbers in those traces count from the combined text, which starts with one wrapper line followed by Reminders.gs and then Code.gs.

The vm documentation warns that the module is not a security mechanism, so use this loader only for your own project's code.

Loading Code.gs in Node.js works because its top level only declares a constant and functions, and the tests only call wrappers that receive their services as arguments. If you add top-level code that calls a service, such as const SHEET = SpreadsheetApp.getActiveSheet();, loading fails in Node.js with ReferenceError: SpreadsheetApp is not defined. That code is also discouraged in Apps Script, where it runs on every execution of every function.

Write tests with the built-in runner

Node.js 22 and later include a stable test runner in node:test and assertions in node:assert/strict. No test framework needs to be installed. In strict assertion mode, assert.deepEqual behaves like assert.deepStrictEqual and failure messages show a diff.

import { readFileSync } from 'node:fs';
import { test } from 'node:test';
import assert from 'node:assert/strict';
import vm from 'node:vm';

// Apps Script loads every .gs file into one shared global scope. Reproduce that
// by joining the files in project order and evaluating them inside a function,
// which returns the names the tests use. The files are never edited for Node.
const projectFiles = ['Reminders.gs', 'Code.gs'];
const source = projectFiles
  .map((file) => readFileSync(new URL('../' + file, import.meta.url), 'utf8'))
  .join('\n');
const gs = vm.runInThisContext(
  '(function () {\n' +
    source +
    '\nreturn { headerProblem_, validateRenewalRow_, daysUntil_, buildReminderEmail_,' +
    ' planReminders_, reminderRowsToReset_, applyReminderPlan_, onEdit };\n})',
  { filename: 'apps-script-project.gs' },
)();

const HEADER = ['Customer', 'Email', 'Renewal date', 'Status', 'Reminder drafted'];
// Month numbers start at 0: new Date(2026, 4, 1) is 1 May 2026, local time.
const TODAY = new Date(2026, 4, 1);

test('daysUntil_ counts calendar days and ignores the time of day', () => {
  assert.equal(gs.daysUntil_(TODAY, new Date(2026, 4, 1, 23, 59)), 0);
  assert.equal(gs.daysUntil_(new Date(2026, 4, 1, 23, 59), new Date(2026, 4, 2, 0, 1)), 1);
  assert.equal(gs.daysUntil_(TODAY, new Date(2026, 3, 30)), -1);
  assert.equal(gs.daysUntil_(new Date(2026, 1, 27), new Date(2026, 2, 1)), 2);
  // Spans the 2026 daylight saving change in the United States and Europe.
  assert.equal(gs.daysUntil_(new Date(2026, 2, 7), new Date(2026, 2, 30)), 23);
});

test('validateRenewalRow_ reports the first problem with its row number', () => {
  const result = gs.validateRenewalRow_(['Ada Studio', 'ada@example', '2026-05-10', 'Active', ''], 7);
  assert.deepEqual(result, { problem: { rowNumber: 7, message: 'Renewal date must be a date cell.' } });

  const missingEmail = gs.validateRenewalRow_(['Ada Studio', '', TODAY, 'Active', ''], 3);
  assert.equal(missingEmail.problem.message, 'Email must look like name@example.com.');

  const badStatus = gs.validateRenewalRow_(['Ada Studio', 'ada@example.com', TODAY, 'active', ''], 4);
  assert.match(badStatus.problem.message, /^Status must be one of/);
});

test('buildReminderEmail_ builds the subject and body from one row', () => {
  const row = {
    customer: 'Ada Studio',
    email: 'ada@example.com',
    renewalDate: new Date(2026, 4, 10),
    status: 'Active',
    reminderDrafted: false,
  };
  assert.deepEqual(gs.buildReminderEmail_(row, 9, 2), {
    rowNumber: 2,
    to: 'ada@example.com',
    subject: 'Your subscription renews in 9 days',
    body:
      'Hello Ada Studio,\n\nYour subscription renews on 2026-05-10 (in 9 days).\n' +
      'Reply to this email if you want to change or cancel it.',
  });
  assert.equal(gs.buildReminderEmail_(row, 1, 2).subject, 'Your subscription renews tomorrow');
});

test('planReminders_ selects Active rows due within 14 days without a drafted date', () => {
  const rows = [
    ['Ada Studio', 'ada@example.com', new Date(2026, 4, 10), 'Active', ''],
    ['Lin Bakery', 'lin@example.com', new Date(2026, 4, 15), 'Active', ''],
    ['Kai Garage', 'kai@example.com', new Date(2026, 4, 16), 'Active', ''],
    ['Rio Books', 'rio@example.com', new Date(2026, 4, 5), 'Renewed', ''],
    ['Sol Cafe', 'sol@example.com', new Date(2026, 4, 5), 'Active', new Date(2026, 3, 28)],
    ['', '', '', '', ''],
    ['Max Print', 'max@example.com', 'next week', 'Active', ''],
  ];
  const plan = gs.planReminders_(rows, TODAY);
  assert.deepEqual(
    plan.reminders.map((/** @type {{rowNumber: number}} */ email) => email.rowNumber),
    [2, 3],
  );
  assert.deepEqual(plan.problems, [{ rowNumber: 8, message: 'Renewal date must be a date cell.' }]);
});

test('headerProblem_ rejects a renamed column', () => {
  assert.equal(gs.headerProblem_(HEADER), '');
  assert.match(gs.headerProblem_(['Customer', 'E-mail', 'Renewal date', 'Status', 'Reminder drafted']), /^Row 1 must be/);
});

test('reminderRowsToReset_ handles single cells, pastes, and the header row', () => {
  assert.deepEqual(gs.reminderRowsToReset_(4, 3, 1, 1), { firstRow: 4, numRows: 1 });
  assert.deepEqual(gs.reminderRowsToReset_(1, 1, 5, 5), { firstRow: 2, numRows: 4 });
  assert.equal(gs.reminderRowsToReset_(1, 3, 1, 1), null);
  assert.equal(gs.reminderRowsToReset_(4, 4, 1, 1), null);
});

/**
 * A fake Sheet with only the methods applyReminderPlan_ calls.
 *
 * @param {unknown[][]} values
 */
function fakeSheet(values) {
  /** @type {{row: number, column: number, value: unknown}[]} */
  const writes = [];
  return {
    writes,
    getDataRange: () => ({ getValues: () => values.map((row) => row.slice()) }),
    /** @param {number} row @param {number} column */
    getRange: (row, column) => ({
      /** @param {unknown} value */
      setValue: (value) => {
        writes.push({ row, column, value });
        values[row - 1][column - 1] = value;
      },
    }),
  };
}

test('applyReminderPlan_ drafts, then marks each row, and is safe to rerun', () => {
  const sheet = fakeSheet([
    HEADER,
    ['Ada Studio', 'ada@example.com', new Date(2026, 4, 10), 'Active', ''],
    ['Lin Bakery', 'lin@example.com', new Date(2026, 5, 30), 'Active', ''],
  ]);
  /** @type {string[]} */
  const drafts = [];
  const result = gs.applyReminderPlan_(sheet, (/** @type {{to: string}} */ email) => drafts.push(email.to), TODAY);

  assert.deepEqual(result, { drafted: [2], problems: [] });
  assert.deepEqual(drafts, ['ada@example.com']);
  assert.deepEqual(sheet.writes, [{ row: 2, column: 5, value: TODAY }]);

  const rerun = gs.applyReminderPlan_(sheet, () => assert.fail('no draft expected'), TODAY);
  assert.deepEqual(rerun.drafted, []);
});

test('applyReminderPlan_ stops before drafting when the header is wrong', () => {
  const sheet = fakeSheet([['Name', 'Email'], ['Ada Studio', 'ada@example.com']]);
  assert.throws(
    () => gs.applyReminderPlan_(sheet, () => assert.fail('no draft expected'), TODAY),
    /Row 1 must be/,
  );
  assert.deepEqual(sheet.writes, []);
});

test('applyReminderPlan_ keeps the mark for a draft that succeeded before a failure', () => {
  const sheet = fakeSheet([
    HEADER,
    ['Ada Studio', 'ada@example.com', new Date(2026, 4, 10), 'Active', ''],
    ['Lin Bakery', 'lin@example.com', new Date(2026, 4, 11), 'Active', ''],
  ]);
  let calls = 0;
  const failSecond = () => {
    calls += 1;
    if (calls === 2) {
      throw new Error('Simulated Gmail failure');
    }
  };
  assert.throws(() => gs.applyReminderPlan_(sheet, failSecond, TODAY), /Simulated Gmail failure/);
  assert.deepEqual(sheet.writes, [{ row: 2, column: 5, value: TODAY }]);
});

test('onEdit clears Reminder drafted when a hand-built event edits a renewal date', () => {
  /** @type {string[]} */
  const cleared = [];
  const sheet = {
    getName: () => 'Renewals',
    /** @param {number} row @param {number} column @param {number} numRows @param {number} numColumns */
    getRange: (row, column, numRows, numColumns) => ({
      clearContent: () => cleared.push(`row ${row}, column ${column}, ${numRows} x ${numColumns}`),
    }),
  };
  const event = {
    range: { getSheet: () => sheet, getRow: () => 3, getColumn: () => 3, getNumRows: () => 2, getNumColumns: () => 1 },
  };
  gs.onEdit(event);
  assert.deepEqual(cleared, ['row 3, column 5, 2 x 1']);

  cleared.length = 0;
  gs.onEdit({ range: { ...event.range, getSheet: () => ({ ...sheet, getName: () => 'Notes' }) } });
  assert.deepEqual(cleared, []);
});

Each test(name, fn) call registers one test, which passes if its function returns without throwing. A failed assertion throws an AssertionError; the runner reports that test as failed and continues with the next.

The tests build dates with new Date(2026, 4, 1). Month numbers start at 0, so that is 1 May 2026 at local midnight. Using local components rather than a string such as '2026-05-01' keeps the tests independent of the computer's time zone, because daysUntil_ also reads local components. During authoring, the suite passed with the process time zone set to UTC, America/Los_Angeles, Pacific/Auckland, and Europe/London.

The suite covers cases a manual run against a copy usually misses: a renewal exactly 14 days out (drafted) and 15 days out (skipped), a blank row, text in the date column, a row already marked, a second run, a wrong header, and a Gmail failure on the second reminder, which must leave only the first row marked.

Fake only what the code calls

fakeSheet stands in for a Sheet. It implements getDataRange().getValues() and getRange(row, column).setValue(value), the two calls applyReminderPlan_ makes, and records every write in a writes array. It does not implement the hundreds of other Sheet methods.

A small fake shows exactly which parts of the service the function depends on, and it fails clearly when that changes: if someone edits applyReminderPlan_ to call getRange(...).setValues(...), the test fails with TypeError: sheet.getRange(...).setValues is not a function. That failure tells you the wrapper's contract changed, so the fake needs the new method.

A fake is your description of the service, so it can be wrong. getValues() on a real range returns a two-dimensional array of Number, Boolean, Date, or String values, with '' for empty cells. The fake returns whatever arrays the test supplies. If a test feeds the fake values that a real sheet would never return, the test can pass while the real script fails. The editor smoke test in the next section catches that gap.

Test a trigger with a hand-built event

onEdit is a simple trigger: Apps Script calls it when a user edits the spreadsheet and passes an event object. For a Sheets edit, that object has fields such as range, source, value, oldValue, and authMode. The handler in this project reads only e.range, so its JSDoc type is {range: SpreadsheetApp.Range} and the test builds only that field:

const event = {
  range: { getSheet: () => sheet, getRow: () => 3, getColumn: () => 3, getNumRows: () => 2, getNumColumns: () => 1 },
};
gs.onEdit(event);

That event describes a two-row paste into C3:C4. The test expects the handler to clear E3:E4 and to ignore the same edit on a sheet named Notes. The decision about which rows to clear lives in the pure reminderRowsToReset_, which has its own test for single cells, multi-row pastes, and edits that include the header row.

You cannot test a trigger by editing a cell from code. Google's trigger guide states that script executions and API requests do not cause triggers to run; calling Range.setValue() does not run onEdit. Calling the handler with a hand-built event is the way to exercise it from a test, both in Node.js and in the editor.

Run the local tests

Use Node.js 22 (22.11.0 or newer) or Node.js 24, the versions Develop Apps Script locally uses. Save the project files in a new directory, keeping the test file inside test/:

testing-apps-script/
  Code.gs
  Reminders.gs
  appsscript.json
  package.json
  renewals.csv
  test/
    reminders.test.mjs

{
  "name": "book-testing-apps-script",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "engines": {
    "node": ">=22.11.0"
  },
  "scripts": {
    "test": "node --test"
  }
}

The package has no dependencies, so no npm install is required. "type": "module" lets the test file use import. The test script runs node --test with no file arguments, which makes the runner search the directory for files matching its default patterns, including **/*.test.mjs and anything under a test directory. Each test file runs in its own child process by default.

From the project directory:

node --version
npm test

On Node.js 22.22.0, during authoring, the run ended with:

# tests 10
# suites 0
# pass 10
# fail 0
# cancelled 0
# skipped 0
# todo 0

The output above uses the TAP format that the runner prints when its output is not a terminal. In an interactive terminal the default reporter prints a check mark beside each test name instead. Run node --test --test-reporter=spec for the same readable format anywhere.

Watch one test fail

A test suite you have never seen fail has not shown that it can catch anything. In Reminders.gs, change daysLeft <= REMINDER_WINDOW_DAYS_ to daysLeft < REMINDER_WINDOW_DAYS_ and run npm test again. The planReminders_ test fails, and the report includes:

not ok 4 - planReminders_ selects Active rows due within 14 days without a drafted date
  error: |-
    Expected values to be strictly deep-equal:
    + actual - expected

      [
        2,
    -   3
      ]

Row 3, due in exactly 14 days, no longer gets a reminder. The - 3 line is the expected value the actual array is missing. Restore <= and confirm all 10 tests pass before continuing.

Smoke-test the real services in the editor

The local tests prove the rules and prove that applyReminderPlan_ calls its fakes correctly. They cannot prove that a real Sheet behaves like the fake. A smoke test is a short check that runs the real code path against real services and fails loudly if a basic expectation is wrong.

smokeTestReminders in Code.gs does the following:

1. Inserts a sheet named Smoke test plus a timestamp in the bound spreadsheet.

2. Writes a header and three rows with real Date values through setValues.

3. Calls applyReminderPlan_ with the real sheet and a function that records drafts in an array. No Gmail call happens.

4. Compares the drafted rows, the problem rows, the recipient, the subject, and the dates written to column E, using expectEqual_, which throws an Error naming the check, the expected value, and the actual value.

5. Runs the plan again and expects no drafts, then changes C2, calls the edit handler with a hand-built event, and expects E2 to be empty.

6. Deletes the temporary sheet in a finally block, which runs whether the checks passed or threw.

The smoke test uses a fixed date, 1 May 2026, so its expectations stay the same every day. It calls resetRemindersAfterEdit_ with the temporary sheet's name rather than onEdit, because onEdit only acts on the Renewals sheet.

Prepare a disposable spreadsheet

Use a test account and a new spreadsheet that has no other scripts, triggers, or shared users. This lesson needs your approval for creating that spreadsheet, granting the Gmail and spreadsheet scopes, and creating one Gmail draft.

1. Create a blank spreadsheet named Renewal reminders - test.

2. Choose File > Settings and note the Time zone. Set the manifest's timeZone to the same zone, using its region ID such as Europe/London. The tests cannot check this setting. If the two zones differ, the script's calendar day for "today" or for a date cell can differ from the day the spreadsheet shows, which moves the 14-day window.

3. Choose File > Import > Upload and select renewals.csv. Choose Replace current sheet, keep Convert text to numbers, dates, and formulas selected, and click Import data.

4. Rename the sheet Renewals.

5. The date column holds =TODAY() formulas so the fixture is always near the current date. Select C2:C5, copy, then choose Edit > Paste special > Values only on the same cells so the dates stop moving. Choose Format > Number > Date and confirm that four dates appear.

Customer,Email,Renewal date,Status,Reminder drafted
Ada Studio,ada@example.com,=TODAY()+9,Active,
Lin Bakery,lin@example.com,=TODAY()+40,Active,
Kai Garage,kai.example.com,=TODAY()+3,Active,
Rio Books,rio@example.com,=TODAY()+5,Renewed,

Ada Studio is due in 9 days. Lin Bakery is due in 40 days. Kai Garage has an invalid email address. Rio Books has already renewed. All addresses use the reserved example.com domain.

Add the code and run the smoke test

From the spreadsheet, open Extensions > Apps Script. Replace the default Code.gs, add a script file named Reminders, and paste in Reminders.gs. In Project Settings, enable Show "appsscript.json" manifest file in editor, then replace the manifest. Do not add the test file or package.json; Apps Script cannot run them.

If you prefer to upload with clasp, follow the account, target, snapshot, and push procedure in Develop Apps Script locally. Place Code.gs, Reminders.gs, and appsscript.json in src, and add a !Reminders.gs line to its .claspignore allowlist, or clasp will not upload the new file. Keep test/ and package.json outside src, and change '../' + file in the test file to '../src/' + file so the tests read the files you upload. Run npm test before every push; a push uploads whatever is in src, tested or not.

Select smokeTestReminders and click Run. Review the authorization request with the test account; it lists the Gmail and current-spreadsheet scopes from the manifest. Expect Smoke test passed. in the execution log and no leftover Smoke test sheet. If a check fails, the log shows the thrown message, such as Smoke test failed: drafted rows. Expected [2], got [], and the temporary sheet is still deleted.

Make the first real run

Only after the smoke test passes, select draftRenewalReminders and click Run. Expect this log line:

{"drafted":[2],"problems":[{"rowNumber":4,"message":"Email must look like name@example.com."}]}

Open Gmail's Drafts folder and inspect the one draft to ada@example.com. The script does not send it. Column E of row 2 now shows the run date. Run draftRenewalReminders a second time and expect "drafted":[] and no new draft.

Then edit C2 by hand to a different date. The onEdit simple trigger runs for your edit and clears E2. The next run of draftRenewalReminders drafts a new reminder if the new date is within 14 days.

These are expected results; the chapter's Apps Script code was type-checked but has not been run in Google's runtime during authoring.

Use clasp run-function only with its requirements

clasp 3.4.1 can call a function remotely with clasp run-function, which uses the Apps Script API's scripts.run method. Its setup is heavier than the rest of this chapter. According to the clasp 3.4.1 README and Google's execution guide, it requires:

• logging in to clasp with your own OAuth client credentials (clasp login --creds creds.json);

• switching the script to a standard Google Cloud project shared with that OAuth client;

• deploying the script as an API executable and adding an executionApi section to the manifest;

• an OAuth token that covers every scope the script uses, including scopes the called function does not need.

The API cannot return Apps Script objects such as a Sheet, cannot create triggers, and does not run scripts that need no scopes. With devMode set, an owner runs the most recently saved code instead of the deployed version. For this project, the larger problem is context. smokeTestReminders relies on getActiveSpreadsheet(), one of the bound-script special methods, and as Develop Apps Script locally notes, an Apps Script API execution does not supply the same bound container as an editor run. Run the smoke test from the editor. Consider run-function only for a standalone script that opens its spreadsheet by ID, after the extra project configuration has been reviewed.

What tests cannot prove

Passing tests show that the code does what the tests describe. They do not show that Google will let it run, or that the services behave the way your fakes assume.

• Permissions. Neither Node.js nor the fakes check OAuth scopes. A missing scope in the manifest, an administrator policy that blocks Gmail access, or a user who declines authorization only appears in a real run.

• Trigger mode. Calling onEdit from a test or from the editor runs it with full authorization. A real simple trigger runs in a restricted mode: the trigger guide says simple triggers cannot use services that require authorization, such as Gmail, cannot open other files, and cannot run longer than 30 seconds. If someone adds a GmailApp call to onEdit, both the Node.js test and an editor run can pass while the real trigger fails.

• Quotas. Apps Script quotas, such as daily email recipients and the six-minute execution limit, apply per user and can change. A local test uses none of them. A long run can stop at the time limit after some rows are marked, which applyReminderPlan_ tolerates because it marks each row as it goes.

• Real data shapes. Fakes return what you give them. Merged cells, a date typed as text, a filter that hides rows, or a header changed by a colleague only appear in the real sheet. The header check and row validation turn some of these into clear errors, but only for cases the code anticipates.

• Time zones. The local tests prove the day count for dates built from local components. They cannot see the spreadsheet's time zone setting. Matching the manifest and spreadsheet time zones is a setup step, and the smoke test checks only the dates it writes itself.

• Email content in a real client. A test can compare the body string. It cannot show you how Gmail renders it. Read the first real draft before sending it.

Use Debug executions and recover from limits for failures that only appear in real executions, and Install time-driven triggers safely before adding an installable time-driven trigger that calls draftRenewalReminders without you watching.

Exercise: change the window and predict the failures

1. Change REMINDER_WINDOW_DAYS_ in Reminders.gs from 14 to 7. Before running anything, write down which local tests should fail and why. Then run npm test. Three tests fail: the planReminders_ test, because its rows due in 9 and 14 days fall outside the new window, and the two applyReminderPlan_ tests that expect a draft for rows due in 9 or 10 days. The other seven pass because they test rules the window does not affect.

2. Predict what smokeTestReminders would report with the same change. Its Ada Studio row is due in 9 days, so the first check fails with Smoke test failed: drafted rows. Expected [2], got []. If you run it, confirm that no Smoke test sheet remains afterward.

3. Restore 14. Then add a status Paused that is valid but never gets a reminder. Start with the test: add ['Pia Florist', 'pia@example.com', new Date(2026, 4, 4), 'Paused', ''] to the end of the rows in the planReminders_ test and run the suite. It fails, because Paused is not yet a valid status and the row appears in problems. Now add 'Paused' to RENEWAL_STATUSES_ and run again. The test passes without changing its expected reminders, because planReminders_ only drafts for Active rows.

Common failures

Clean up

Delete the reminder draft from Gmail's Drafts folder without sending it. Delete any Smoke test sheet left by an interrupted run. When you no longer need the project, trash the Renewal reminders - test spreadsheet. The project installs no installable triggers and creates no deployments, and the local test files have no effects to undo.