Advanced · A5

Writing scripts that last

A script that works once on your test sheet is a start. A script other people rely on must be fast, stay inside Google's limits, fail with a clear message, cope with two users at once, and be easy to change next year. This chapter covers the habits that get you there.

The one rule: fewer calls to Google

JavaScript in memory is fast. Each call to a Google service (getValue, setBackground, sendEmail…) is a network trip. Speed comes from making fewer of them.

Code.gs

// Everything in one pass: one read, all work in memory, one write per column.
function gradeAll() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
  const n = sheet.getLastRow() - 1;
  if (n < 1) return;

  const values = sheet.getRange(2, 1, n, 3).getValues();          // 1 call
  const grades = values.map(([, , m]) => [m >= 80 ? 'A' : m >= 60 ? 'B' : m >= 50 ? 'C' : 'F']);
  const colours = grades.map(([g]) => [g === 'F' ? '#fde0dc' : null]);

  const out = sheet.getRange(2, 4, n, 1);
  out.setValues(grades);                                          // 1 call
  out.setBackgrounds(colours);                                    // 1 call
}

Slow	Fast
`getValue()` in a loop	One `getValues()` before the loop
`setValue()` in a loop	Build an array, one `setValues()` after
`setBackground()` per row	One `setBackgrounds()` with a 2D array
`appendRow()` 500 times	`getRange(last + 1, 1, 500, n).setValues(rows)`
The same API call every run	CacheService (A4)
Reading and writing in turn	All reads first, then all writes

Mixing reads and writes forces Sheets to save after each write so the next read is correct. Group them, and Sheets saves once.

Next: limits →

Quotas worth knowing

Limit	Personal account	Google Workspace
One execution	6 minutes	6 minutes
Custom function / simple trigger	30 seconds	30 seconds
Total trigger run time per day	90 minutes	6 hours
Email recipients per day	100	1,500
UrlFetch calls per day	20,000	100,000
Triggers per user per script	20	20
Properties	9 KB per value, 500 KB per store

These are Google's published figures at the time of writing; search "Apps Script quotas" for the current page.

When the job is bigger than 6 minutes

Work in chunks: stop before the limit, save where you got to, and schedule a one-off trigger to carry on.

Code.gs

// Processing 5,000 rows that each need an API call won't fit in 6 minutes.
// Do a chunk, remember where you stopped, and let a trigger carry on.
const CHUNK_TIME_MS = 4.5 * 60 * 1000;   // stop well before the 6-minute limit

function processInChunks() {
  const start = Date.now();
  const props = PropertiesService.getScriptProperties();
  let row = Number(props.getProperty('nextRow') || 2);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Big');
  const last = sheet.getLastRow();

  while (row <= last && Date.now() - start < CHUNK_TIME_MS) {
    // ... slow work for one row ...
    row++;
  }

  if (row <= last) {
    props.setProperty('nextRow', String(row));
    ScriptApp.newTrigger('processInChunks').timeBased().after(60 * 1000).create();  // again in 1 min
  } else {
    props.deleteProperty('nextRow');
    ScriptApp.getProjectTriggers()
      .filter((t) => t.getHandlerFunction() === 'processInChunks')
      .forEach((t) => ScriptApp.deleteTrigger(t));
    console.log('All done');
  }
}

Next: errors →

Fail clearly

Code.gs

function safeImport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  try {
    const sheet = ss.getSheetByName('Import');
    if (!sheet) throw new Error('There is no tab called "Import".');   // your own, clear error

    const rows = sheet.getDataRange().getValues();
    // ... process rows ...
    ss.toast(`Imported ${rows.length - 1} rows`, 'Done');
  } catch (err) {
    console.error(err);                                    // full detail in the Executions log
    SpreadsheetApp.getUi().alert('Import failed: ' + err.message);
  } finally {
    // runs whether it worked or not: a good place to release a lock
  }
}

Check early. Test for a missing tab, an empty sheet or bad input at the start and throw your own error with a message a person understands.
Catch at the edge. Put try/catch where a person or a trigger starts the script, and turn the error into an alert, a toast, an email or a log row.
Log detail for yourself. console.error(err) keeps the stack trace in Executions, while the user sees a short message.
Don't swallow errors. An empty catch {} hides problems until the data is wrong.

Debugging

Click a line number in the editor to set a breakpoint, then press Debug instead of Run. The script pauses there and the right-hand panel shows every variable. Step over lines one at a time and watch the values change. It is much faster than adding console.log everywhere.

Next: locks →

Two runs at once

A web app or form trigger can run many times at the same moment. If two runs read a counter, add 1 and write it back, both may read the same number. A lock lets one run in at a time:

Code.gs

// Two people submit at the same moment. Without a lock, both read
// "next number = 42" and both write 42.
function nextTicketNumber() {
  const lock = LockService.getScriptLock();
  lock.waitLock(10000);                       // wait up to 10 s for other runs to finish
  try {
    const props = PropertiesService.getScriptProperties();
    const n = Number(props.getProperty('ticket') || 0) + 1;
    props.setProperty('ticket', String(n));
    return n;
  } finally {
    lock.releaseLock();                       // always let the next run in
  }
}

Lock	Blocks
`getScriptLock()`	Every run of this project, for everyone
`getUserLock()`	Only runs by the same user
`getDocumentLock()`	Runs on the same bound file

waitLock(ms) waits and throws if it times out. tryLock(ms) returns false instead, so you can say "busy, try again". Keep the locked part short: read, change, write, release.

Next: organising code →

Code someone else can change

Code.gs

// Settings in one place, at the top, instead of magic values everywhere.
const CONFIG = {
  sheetName: 'Marks',
  passMark: 50,
  teacherEmail: 'teacher@school.example',
  columns: { name: 1, room: 2, mark: 3, grade: 4 },
};

function failing() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.sheetName);
  const rows = sheet.getDataRange().getValues().slice(1);
  return rows.filter((r) => r[CONFIG.columns.mark - 1] < CONFIG.passMark);
}

One CONFIG object for sheet names, column numbers, email addresses and limits. Next year you change one line, not twenty.
Several files. All .gs files in a project share one global scope, so split by job: Config.gs, Menu.gs, Email.gs, Sheets.gs.
Small functions with names that say what they do: readStudents(), gradeFor(mark), sendResult(student).
Private helpers end in _ so they don't clutter the Run menu and can't be called from a web page.
Comment why, not what. // stop at 4.5 min: the limit is 6 helps; // add 1 doesn't.

The manifest: appsscript.json

⚙ Project settings → tick Show "appsscript.json" manifest file in editor. It holds project settings, including the permissions (scopes) the script asks for:

appsscript.json

{
  "timeZone": "Asia/Bangkok",
  "runtimeVersion": "V8",
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/script.send_mail",
    "https://www.googleapis.com/auth/script.container.ui"
  ]
}

Normally Apps Script works out scopes from your code, and often asks for more than it needs. Listing them yourself lets you choose narrower ones, like spreadsheets.currentonly (only the file the script is bound to) instead of every spreadsheet. Users trust a script that asks for less.

Next: libraries and clasp →

Versions and libraries

Versions: Deploy → Manage deployments creates numbered snapshots. Web apps and libraries point at a version, so you can work on the code without breaking users.
Libraries: share one project's functions with many projects. In the shared project note its Script ID; in another project click + next to Libraries, paste the ID, pick a version, and call it as MyLib.someFunction().
Advanced services: + next to Services adds full Google APIs (Drive API, Sheets API, Gmail API, Calendar API) for things the simple services can't do.

clasp: work on your own computer

For bigger projects, Google's command-line tool clasp syncs a project with a folder, so you can use VS Code, Git and code review:

Terminal

# One-time setup (needs Node.js)
npm install -g @google/clasp
clasp login

# Copy an existing project to your computer, using its Script ID (Project settings)
clasp clone 1AbCdEf...xyz

# Edit in VS Code, then:
clasp push        # upload your files to the Apps Script project
clasp pull        # download changes made in the online editor

# Command names for opening and deploying changed in clasp 3: run clasp --help

Turn on the Apps Script API first, at script.google.com → ⚙ Settings. In a clasp project, .gs files appear as .js, and you can keep everything in Git.

Before you hand a script to anyone

It has a menu or button, so nobody needs the editor.
It checks its input and fails with a clear message.
It can be run twice without doing anything twice (Sent / ID columns).
Settings are in one CONFIG block, secrets in Properties.
It stays far below the time and email limits on real data.
A short note on the first tab says what it does and who to ask.

Next: practice →

Practice

1. Measure it Easy

Time 100 setValue calls against one setValues call with Date.now(), and log both.

Show a solution

Code.gs

function timeIt() {
  const sheet = SpreadsheetApp.getActiveSheet();
  let t = Date.now();
  for (let r = 1; r <= 100; r++) sheet.getRange(r, 1).setValue(r);
  console.log(`100 setValue calls: ${Date.now() - t} ms`);

  t = Date.now();
  sheet.getRange(1, 2, 100, 1).setValues(Array.from({ length: 100 }, (_, i) => [i + 1]));
  console.log(`1 setValues call: ${Date.now() - t} ms`);
}

2. Validate before you process Medium

Write checkMarks() that lists every row with no name, a mark that isn't a number, or a mark outside 0–100, and throws one error listing them all.

Show a solution

Code.gs

function checkMarks() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
  const rows = sheet.getDataRange().getValues().slice(1);
  const problems = [];
  rows.forEach(([name, room, mark], i) => {
    const row = i + 2;
    if (!name) problems.push(`Row ${row}: no name`);
    if (typeof mark !== 'number') problems.push(`Row ${row}: mark is not a number`);
    else if (mark < 0 || mark > 100) problems.push(`Row ${row}: mark ${mark} is outside 0–100`);
  });
  if (problems.length) throw new Error('Fix these first:\n' + problems.join('\n'));
  return true;
}

3. Refactor Challenge

Take your mail merge from I4. Move settings into CONFIG, split it into readStudents_, buildMessage_ and sendAll, add the quota check and a try/catch that records failed rows in an "Error" column, and use a lock so two clicks can't send twice.

End of the Advanced level

You have every tool the Capstone needs. Next: choose a project, plan it, build it, and present it.

Next: quiz →

Check yourself

1. A normal script run (from the editor, a menu or a trigger) is stopped after…

2. Two students submit a form at the same instant and both get ticket number 42. What fixes it?

3. Why release the lock in a finally block?

4. A job needs 20 minutes. What is the usual pattern?

5. What does setting oauthScopes in appsscript.json do?

6. What is clasp?