# Build With Google Apps Script

People think Google Apps Script is for toy macros. Auto-format a sheet, send a reminder email, done. I have used it to run the operational spine of a real organization: thousands of form submissions, multi-round SMS campaigns, a self-serve status portal, and a feedback loop into paid advertising. All of it on a spreadsheet, for free, with no server to keep alive.

It works. But "it works" and "it does not rot" are different problems. Apps Script makes it very easy to build something that runs today and is unmaintainable in three months. This is how to get the first without the second.

**TL;DR**

- A spreadsheet is a real enough database for operations, if you treat the rows as state and never trust them blindly.
- Validate at the door. Forms collect garbage, and garbage in your state machine is the most expensive kind.
- Make every write idempotent. Stamp a thing once, keep an audit trail, and assume the function will run twice.
- Version the script in a changelog block at the top. The discipline is the maintenance.
- The highest-leverage move is closing a loop: click, to form, to sheet, to the ad platform, crediting the conversion that started it.

---

## Why a spreadsheet is a fine backend for ops

For a lot of real-world operations, a spreadsheet is the correct database. It is observable (a human can open it and see the truth), shareable, backed up, and free. Apps Script gives it triggers, an HTTP endpoint, and the ability to call any API.

The trade is that it is slow, weakly typed, and has hard quotas. So the rule is simple: the spreadsheet holds **state and audit trail**, not heavy logic and not high throughput. If you are processing tens of thousands of events per minute, this is the wrong tool. If you are running admissions, a small clinic, a community program, or a campaign, it is often the right one.

## The architecture

Everything flows through one shape: a form captures intent, the sheet is the single source of truth, triggers react to changes and to the clock, and the script reaches out to external services. The non-obvious part is the loop back to advertising.

![Form to Sheet to external services, with a conversion loop back to the ad platform](/img/apps-script-pipeline.svg)
*The sheet is the state. Triggers turn state changes into actions. One arrow closes the loop back to the ad platform.*

## Pattern 1: validate at the door

A public form is an open sewer. People type "Okay" into the phone field, paste their essay into the name, and leave the tracking parameter as the literal word that was in the placeholder. If that garbage reaches your state machine, every downstream step inherits it.

So the first thing `onFormSubmit` does is validate and normalize, before a single side effect runs.

```javascript
function handleFormSubmit(e) {
  const row = e.values;
  const phone = normalizePhone(row[PHONE]);   // strip spaces, fix +233 vs 0
  const gclid = row[GCLID];

  // A click ID is a long alphanumeric token, not "gclid", not "Okay", not empty.
  const validClick = /^[A-Za-z0-9_-]{20,}$/.test(gclid);

  if (!phone) { logSkip(e, "bad phone"); return; }   // fail loud, do not proceed
  // ... only now do the real work
}
```

Two ideas earn their keep here. **Normalize before you store** (one phone format, not six), and **fail loud, not silent.** A skipped row that logs why is debuggable. A row that quietly half-processed is a ghost you will chase for a week.

## Pattern 2: make every write idempotent

Triggers fire more than once. Edits replay. You will, eventually, run the same function twice on the same row. If "mark as enrolled" appends a row or sends an SMS every time it runs, you now have duplicate charges and a furious user.

The fix is to stamp state exactly once and treat the stamp as a guard.

```javascript
function markEnrolled(rowIndex) {
  const sheet = stateSheet();
  const already = sheet.getRange(rowIndex, ENROLLED_DATE).getValue();
  if (already) return;                          // idempotent: only the first run acts
  sheet.getRange(rowIndex, ENROLLED_DATE).setValue(new Date());
  sheet.getRange(rowIndex, ENROLLED_FIRST_SEEN).setValue(new Date());
  // side effects (receipt SMS, ad conversion) happen exactly once
}
```

Keep both a "confirmed" timestamp and a "first seen" timestamp. The pair is your audit trail, and audit trails are what let you trust a spreadsheet you cannot unit-test.

## Pattern 3: version it in a changelog block

Apps Script has no real deploy history you will look at. So the file itself is the changelog. Every version of mine opens with a dated block explaining what changed and, more importantly, **why**.

```javascript
/**
 * Admissions automation
 * v11.4.0 - add "Enrolled" status + date seam so cost-per-enrolled is computable
 * v11.3.0 - harden click-ID validation (junk was inflating offline conversions)
 * v11.2.0 - offline-conversion export tab + timezone-correct header
 * v11.1.0 - submit confirmation SMS + 3-stage drip
 */
```

This sounds like bureaucracy. It is the opposite. It is the only thing that lets you, or anyone, evolve a living system without breaking the parts you forgot existed. Every line is a small lesson dated to the day you learned it.

## Pattern 4: close the loop back to advertising

This is the pattern most people never build, and it is the one that pays for everything else.

When someone clicks a paid ad, they arrive with a click ID in the URL. If you capture that ID on the form and later tell the ad platform "this click became a real conversion," the platform's bidding gets dramatically smarter, because it is now optimizing for outcomes that happen days later and offline, not just for form fills.

The loop:

1. The landing page reads the click ID from the URL and writes it into a hidden form field.
2. On submit, the script validates it (Pattern 1) and appends it to an offline-conversions tab with a timestamp and a conversion name.
3. The ad platform re-imports that tab on a schedule and credits the original click.

Two traps will bite you. The export tab needs a **timezone-correct header**, or every conversion lands an hour off and some get rejected. And you must judge readiness **per campaign, not per account**, because the platform's smart bidding starves on a campaign with zero conversions even if the account total looks healthy.

## Pattern 5: drip on the clock

A time-driven trigger plus a "stage" column is a campaign engine. The trigger wakes up, finds rows that are due for the next message, sends it, and advances the stage. Because the stage is stored state, a missed run just resumes next time. Nothing double-sends, because advancing the stage is itself idempotent (Pattern 2).

## The gotchas that will actually hurt

- **The six-minute execution limit.** A single run cannot loop over 10,000 rows. Batch the work, store a cursor, and continue on the next trigger.
- **Timezones.** The script's timezone, the sheet's, and the ad platform's are three different settings. Pin them explicitly and test at the boundary.
- **Quotas.** Email, URL fetch, and trigger counts are capped per day. Design for the cap, and log when you are near it.
- **Concurrency.** Two triggers can touch the same row at once. Use `LockService` around anything that reads-then-writes.

## When not to use it

If you need sub-second responses, real transactions, tens of thousands of concurrent users, or strong data integrity guarantees, use a real database and a real backend. Apps Script is for the long tail of operations that would otherwise be done by hand: the admissions desk, the reminder, the reconciliation, the report. For those, it is a quiet superpower.

---

## The checklist

- [ ] Sheet holds state and audit trail, not heavy logic or high throughput.
- [ ] Every submission validated and normalized before any side effect.
- [ ] Every write idempotent, guarded by a "done" stamp, with a first-seen audit timestamp.
- [ ] A dated changelog block at the top of the file.
- [ ] If you advertise: capture the click ID, validate it, export conversions with a timezone-correct header, judge readiness per campaign.
- [ ] Drip campaigns driven by a stage column and a time trigger.
- [ ] Batching for the six-minute limit, `LockService` for shared rows, quotas respected.

You do not need a server to run a serious operation. You need state you can see, writes you can trust, and the discipline to date your lessons.

---

_Written from a production admissions system that grew across a dozen versions. The patterns generalize to any form-driven operation on Google Workspace._