Write testable Apps Script code

Apps Script has no built-in test runner, no mocking framework, and no way to “run just the logic” without touching live spreadsheets, inboxes and Drive files. That makes testing feel impossible — so most scripts are verified by running the whole thing against real data and eyeballing the result. That works until the script grows, and then every change is a gamble.

The way out is not a testing framework — it is structure. If you separate the code that talks to Google from the code that makes decisions, the decision-making code becomes ordinary JavaScript: deterministic, fast, and trivial to check. This guide shows that separation and how to test the part that matters.

The pattern

The core idea is to split every job into two kinds of code. I/O code fetches and writes data through Google services. Transformation code takes plain data in and returns plain data out, touching no service at all. Only the second kind can be tested cheaply — so make it as large a share of your logic as possible.

// Bad: hard to test — fetching, logic and writing are tangled together.
// Nothing here can run without a real spreadsheet, so the only way to
// check the logic is to execute the whole function against live data.
function processInvoices() {
  const sheet = SpreadsheetApp.openById(INVOICES).getSheets()[0];
  const data = sheet.getDataRange().getValues();
  // ... 50 lines of transformation ...
  sheet.getRange(...).setValues(...);
}

// Good: a thin orchestrator plus a pure, testable core.
// processInvoices only wires things together; the real work moves
// into transformInvoices, which knows nothing about Sheets.
function processInvoices() {
  const data = readSheet(INVOICES);          // I/O — talks to Sheets
  const out = transformInvoices(data);       // pure — just data in, data out
  writeSheet(INVOICES, out);                 // I/O — talks to Sheets
}

// Pure function: no SpreadsheetApp, no side effects, fully deterministic.
function transformInvoices(rows) {
  return rows
    .filter((r) => r.amount > 0)
    .map((r) => ({ ...r, status: r.paidAt ? 'paid' : 'open' }));
}

transformInvoices is the part with the actual business rules, and it is now just a function from an array to an array. The same input always gives the same output, so you can call it with whatever data you like and check the result — no spreadsheet required.

The split also makes the two responsibilities clear:

	I/O code	Transformation code
Talks to Google services	Yes	No
Deterministic	No — depends on live data	Yes — same input, same output
Testable in isolation	No	Yes
Where bugs usually hide	Rarely	Usually
How much you should write	As thin as possible	As much logic as possible

Test the pure function

Once the logic is a pure function, a “test” is just another function that calls it with known input and throws if the output is wrong. No framework needed:

function testTransform() {
  // Call the pure function with hand-built input — no sheet involved.
  const result = transformInvoices([
    { id: 1, amount: 100, paidAt: null },        // unpaid -> expect 'open'
    { id: 2, amount: 200, paidAt: new Date() },  // paid   -> expect 'paid'
  ]);

  // Assert each expectation; throw a clear message on failure.
  if (result[0].status !== 'open') throw new Error('Expected open');
  if (result[1].status !== 'paid') throw new Error('Expected paid');

  // Reached only if every assertion passed.
  console.log('Tests pass');
}

Run testTransform from the editor’s function menu. If it logs “Tests pass”, the logic is sound; if it throws, the message tells you which case broke. Because it touches no service, it runs in well under a second and you can run it after every change without thinking twice.

Good cases to cover are the ones that bite in production: empty input, the boundary value (here, an amount of exactly 0), and a deliberately malformed row. Each one is one more if in the test — cheap insurance against a regression.

Common mistakes

• Testing the orchestrator instead of the core. Calling processInvoices in a test still hits a real spreadsheet, so it is slow, non-deterministic, and mutates live data. Test transformInvoices; leave the orchestrator thin enough that it needs no test.
• Letting service calls leak into the pure function. One stray SpreadsheetApp or new Date() inside the transformation makes it non-deterministic again. Pass the current time in as an argument if the logic needs it.
• Writing one giant test. A single function asserting twenty things tells you only “something broke”. Several small, well-named test functions tell you what broke.
• Testing only the happy path. Real data has blank rows, missing fields and unexpected types. The cases you skip are the ones that fail in production.
• Skipping tests because there is no runner. A function that throws on a bad assertion is a test runner. The barrier is structure, not tooling — once the logic is pure, testing is a few lines away.