# Square Webhook to Sheets — Code Explainer

Developer reference for whoever extends, debugs, or audits this script next. For the user-facing setup walkthrough, see `square-webhook-to-sheets-INSTALL-AND-USAGE.md`.

Current version: **v2.0.1** (friendly names; 2.0.1 adds Square's new customer `public` custom-attribute event family — 138 catalog types). Tabs and column headers are now short human-readable names (≤3 words) instead of raw event types / flattened JSON paths; the raw ↔ friendly mapping lives in a hidden `_Schema` tab. See section 4a. (v1.1.0's breaking change vs v1.0 was the flat-column row schema — section 4.)

## 1. Architecture at a glance

The whole project is one `.gs` file (Apps Script convention — `.gs` is JavaScript with Google extensions, no module system, all functions live in one global namespace by default).

```
square-webhook-to-sheets/
├── Code.gs                                # everything: doGet, doPost, helpers, event-types catalog
├── SETUP.md                               # condensed setup notes (lives with the source; users get the longer INSTALL-AND-USAGE.md alongside)
├── .url-token.txt                         # local copy of URL_TOKEN (gitignore; mode 600)
├── .deployment-url.txt                    # local copy of /exec URL
└── .square-notification-url.txt           # local copy of full URL with ?token=… for pasting into Square
```

The deliverable docs sit next to the folder, not inside it (matching the PBJ plugin convention):

```
Google Scripts/
├── square-webhook-to-sheets/              # the source folder above
├── square-webhook-to-sheets-advert.md     # marketing
├── square-webhook-to-sheets-INSTALL-AND-USAGE.md
├── square-webhook-to-sheets-CODE-EXPLAINER.md
├── square-webhook-to-sheets.zip           # canonical (unversioned) release artifact
└── square-webhook-to-sheets-1.0.0.zip     # versioned snapshot
```

## 2. The core request pipeline

Every Square POST flows through `doPost(e)` in this order. Each numbered step maps to a section comment in `Code.gs`.

1. **Token auth.** Read `URL_TOKEN` from Script Properties. Compare to `e.parameter.token` using `_constantTimeEquals` (length-safe, masked timing). If mismatched, log `auth_fail` and return 200 "forbidden" — returning 200 here means misconfigured or drive-by callers don't burn Square's 24-hour retry budget. Genuine Square calls always include the right token.
2. **`SHEET_ID` check.** Read `SHEET_ID` from Script Properties. If absent, `throw new Error(...)` — this gives Square a 5xx, which makes it retry, which buys you time to fix the misconfiguration before events are dropped.
3. **Parse payload.** Read `e.postData.contents`, JSON.parse. On failure, log `parse_error` and return 200 (bad JSON won't get better on retry).
4. **Optional proxy unwrap.** If `SIGNATURE_KEY` is set in Script Properties AND the payload has `__proxy_signature` + `__proxy_body` keys, run `_verifySquareSignature` against the body. This branch only fires when a Cloudflare Worker (or similar) is in front, re-emitting the Square POST wrapped with the signature.
5. **Acquire lock.** `LockService.getScriptLock().waitLock(20000)` — serializes concurrent writes so two simultaneous events don't tangle.
6. **Open spreadsheet, pick/create tab.** `SpreadsheetApp.openById(SHEET_ID)` → `_loadSchema(ss)` → `_getOrCreateSheet(ss, _safeTabName(_friendlyTabName(eventType)))` (v2: tabs carry friendly names, e.g. `invoice.created` → "Invoice Created"; the event type ↔ tab mapping is recorded in `_Schema`). Newly created tabs start empty; the header materializes lazily on first write.
7. **Flatten payload + append row.** `_flatten(payload)` recursively walks the JSON producing a flat `{key: value}` map with dotted keys. `_appendFlattened(sheet, receivedAt, flat, ss, schema, friendlyTab)` translates each raw key to its friendly column name via the per-tab schema map (minting + recording names for unseen keys), appends new columns as needed, and writes the row in header order.
8. **Release lock, return 200 "ok".**

Failures at step 6 or 7 re-throw — Square sees 5xx and retries (this is desirable; you don't want to silently drop write failures).

## 3. Per-step responsibility map

| Step | Function | Failure mode | What Square sees |
|---|---|---|---|
| Token check | `_constantTimeEquals` | Bad token | 200 "forbidden" (silent reject) |
| SHEET_ID check | inline | Property unset | 500 (retry) |
| Parse | inline `JSON.parse` | Malformed JSON | 200 "parse_error" (drop) |
| Sig verify (proxy mode) | `_verifySquareSignature` | HMAC mismatch | 200 "forbidden" |
| Lock | `LockService.waitLock` | Lock starvation | 500 (retry with backoff) |
| Sheet open + tab | `_getOrCreateSheet` | Permission/quota | 500 (retry) |
| Append | `sheet.appendRow` | Write conflict / quota | 500 (retry) |

## 4. Schema model (per-tab, dynamic)

**Breaking change from v1.0:** the script no longer writes a fixed 9-column row. It now:

1. Recursively flattens every field in the Square payload into dotted keys (`_flatten`).
2. For each tab, maintains a header row that grows as new keys appear.
3. Appends the row in header-column order, leaving cells blank when the current event doesn't include a field that earlier events had.

The only column the script adds on top of the flattened payload is "Received" in column A (`RECEIVED_AT_COL`; raw name `received_at` in v1.x).

## 4a. Friendly naming + the _Schema tab (v2.0.0)

Humans read the sheet; scripts need the raw keys. v2 gives each side its own layer:

- **Tabs**: `_friendlyTabName(eventType)` — ordered `TAB_PHRASE_SUBS` compress long families into hyphenated compound words (`custom_attribute_definition.owned` → `Attr-Def-Owned`), then split on `.`/`_`, title-case (with `NAME_ACRONYMS`: ID, URL, OAuth…), join. Verified offline: all 133 event types map to unique names of ≤3 space-separated words ("Invoice Created", "Gift-Card Customer Linked", "Booking Attr-Def-Owned Created").
- **Columns**: `_friendlyColName(raw, taken)` — envelope keys use the fixed `COL_TOP_LEVEL` map (`event_id` → "Event ID", `type` → "Event Type"…); payload keys strip the constant `data.object.<entity>.` prefix and use the last path segment (≤3 words), prepending earlier segments on collision, numeric suffix as last resort. `taken` (friendly → raw, per tab) makes minting collision-proof, including against human-added header columns.
- **`_Schema` tab** (hidden; `kind | tab | raw | friendly`): every minted tab/column mapping is appended here and never recomputed, so names are stable forever even though minting is insertion-order dependent. `_loadSchema(ss)` reads it into `{tabs: {rawType → friendlyTab}, cols: {friendlyTab → {raw → friendly}}}`; `_schemaAppendRows` appends. Downstream readers (square-events-to-notion v2) read the same tab to translate friendly headers back to raw keys.
- **Migration**: `migrateToFriendlyNames()` (bootstrap, idempotent) converts a v1.x sheet in place — renames each raw-event-type tab, rewrites its header row (row counts unchanged, so the router's row pointers stay valid), records everything in `_Schema`. If a raw tab and its friendly twin BOTH exist (an event landed between migration and web-app redeploy), the raw tab is skipped and logged for manual folding; re-run after.

### Flattener rules

| Input | Output | Why |
|---|---|---|
| `{a: 1}` | `{a: 1}` | Primitives kept as-is |
| `{a: {b: 1}}` | `{"a.b": 1}` | Nested objects flattened with dot |
| `{a: [1,2,3]}` | `{a: "[1,2,3]"}` | **Arrays stringified** as JSON — their length varies row to row, so fanning into per-index columns produces sparse explosions |
| `{a: []}` | `{a: ""}` | Empty array → empty cell |
| `{a: {}}` | `{a: ""}` | Empty object → empty cell |
| `{a: null}` | `{}` | Null skipped — cell stays blank in the row |
| `{a: undefined}` | `{}` | Same — undefined skipped |

### Schema-growth invariant

The first event of a type creates the tab with header `[received_at]`. Each subsequent event:
- Reads the existing header row.
- Computes flat keys for the payload.
- Finds keys NOT yet in the header.
- Writes those new keys as additional columns at the end of the header (bold, light grey).
- Writes the row in header-column order with blanks for any field this event doesn't have.

This means earlier rows are NOT backfilled when new columns appear. A column added by row 50 will be blank for rows 1-49. Downstream consumers should treat blanks as "not present in that row," not as zero/null.

### Why no `payload_json` safety-net column anymore

In v1.0 the entire raw JSON was archived in a last `payload_json` cell. v1.1 dropped it because every field is now its own column — there's nothing to recover that's not already in the row. If you need the full raw archive (e.g. for legal/audit purposes), the simplest add-back is to keep a separate `_raw` tab and write the raw `e.postData.contents` there. See section 10 ("Extension points").

## 5. Settings storage (Script Properties)

Apps Script's `PropertiesService.getScriptProperties()` is the persistence layer. Properties are key-value strings, scoped to the script (not the user, not the document). Quota: 500 KB total, 9 KB per value, plenty for our needs.

| Key | Required | Purpose |
|---|---|---|
| `SHEET_ID` | yes | Target Google Sheet ID |
| `URL_TOKEN` | yes | Shared secret; ≥ 24 chars |
| `SIGNATURE_KEY` | no | Square webhook signature key; only used with proxy |
| `NOTIFICATION_URL` | no | The URL Square POSTs to; only used with proxy |

There's no migration logic because there's no versioning yet. If a future version adds a property or changes the meaning of one, add a `_migrateProperties()` function that runs on a known-stale schema version and add a `SCHEMA_VERSION` property to track it.

## 6. Auth model

Apps Script Web Apps cannot read HTTP request headers in `doPost(e)`. The `e` object exposes `parameter`, `parameters`, `postData`, `queryString`, `contentLength`, and `contextPath` — never headers. This is a documented Apps Script limitation, not a bug.

Square's signature header (`x-square-hmacsha256-signature`) is therefore invisible to the script.

**Default auth:** long random URL token in a query parameter. Square always POSTs to the exact notification URL registered, so the token only travels inside Square's outbound TLS connection. This is the strongest auth available without a proxy.

**Optional proxy auth:** the script supports a "proxy mode" where a Cloudflare Worker (or similar) sits in front, verifies the Square HMAC against the raw body and signature key, and forwards the (already-verified) payload wrapped as:

```json
{
  "__proxy_signature": "<original sig>",
  "__proxy_body":      "<original raw body>"
}
```

The script then re-verifies the signature in `_verifySquareSignature` using `Utilities.computeHmacSha256Signature` + `Utilities.base64Encode`. The wrapper is unwrapped and the inner body is processed normally. See the Worker template in `square-webhook-to-sheets-INSTALL-AND-USAGE.md` section 9.

## 7. Tab management

`_getOrCreateSheet(ss, name)` is the only function that touches sheet structure:

- Returns existing sheet if `getSheetByName` finds it.
- Otherwise calls `ss.insertSheet(name)` and sets up: header row appended, row 1 frozen, headers bolded + light-grey background, last column width 600 px (for readable JSON).

`_safeTabName(eventType)` sanitizes event type strings into legal sheet names. Google Sheets disallows `[ ] * ? / \ :` and caps at 100 chars. Event type strings from Square (e.g. `booking.custom_attribute_definition.owned.created`) are well within both rules but the sanitizer is defensive.

## 8. The event-types catalog

`SQUARE_EVENT_TYPES` is a 138-element array at the bottom of `Code.gs`, grouped by API family with comment dividers. Pulled from Square's [Webhook Events Reference](https://developer.squareup.com/docs/webhooks/v2webhook-events-tech-ref) (originally as of API `2025-05-21`; the customer `custom_attribute[_definition].public.*` family was added 2026-07-22). Deprecated events (`dispute.state.changed`, `dispute.evidence.added`, `dispute.evidence.removed`, `labor.shift.*`, and the unscoped `customer.custom_attribute*` legacy names) are deliberately omitted.

This array is used by `precreateAllEventTabs()` (an optional manual run from the editor). The receiver doesn't depend on it — incoming events match by string, not against this list. So if Square adds a new event type tomorrow, the script handles it automatically; the only impact is the catalog being out of date for the pre-create function.

To refresh the catalog after a Square API release:
1. Fetch <https://developer.squareup.com/docs/webhooks/v2webhook-events-tech-ref>.
2. Scrape the H3 sections (each event type is an H3).
3. Replace the `SQUARE_EVENT_TYPES` array contents. Maintain the comment-divider grouping.
4. Note any deprecations in the comments.

## 9. Bootstrap helpers (run manually from the editor)

These functions exist for one-time setup and are not called by the request pipeline:

- `setupConfig()` — convenience installer for Script Properties. Hard-code your values in the function body and run it once. Equivalent to clicking through the Script Properties UI manually.
- `generateUrlToken()` — prints a 64-char hex token to the Logger. Use this if you don't have a password manager handy.
- `precreateAllEventTabs()` — iterates `SQUARE_EVENT_TYPES` and calls `_getOrCreateSheet` for each (v2: friendly names, registered in `_Schema`). Materializes all 133 tabs upfront. Purely cosmetic.
- `migrateToFriendlyNames()` — ONE-OFF v1.x → v2.0.0 sheet conversion (see section 4a). Idempotent; uses `SQUARE_EVENT_TYPES` to recognize raw-named tabs, so refresh the catalog first if Square added types since the last release.

## 10. Extension points

The script is deliberately small and unopinionated. The common extensions:

### Filter events before writing

Add early-return logic in `doPost` right after JSON parse:

```javascript
// Skip events we don't care about
const ignoredTypes = ['device.code.paired', 'oauth.authorization.revoked'];
if (ignoredTypes.includes(payload.type)) {
  return _ok('skipped');
}
```

### Send Slack/email alerts on specific events

Add after the `sheet.appendRow(row)` call:

```javascript
if (eventType === 'dispute.created') {
  UrlFetchApp.fetch(SLACK_WEBHOOK_URL, {
    method: 'post',
    payload: JSON.stringify({ text: 'New Square dispute: ' + payload.data.id }),
  });
}
```

(Note: this adds latency to the request — Square's SLA is generous but if Slack is slow your `doPost` may time out. For production, use `Utilities.sleep`-deferred or a separate trigger-driven function reading the sheet.)

### Write to BigQuery instead of (or in addition to) Sheets

Replace the `_getOrCreateSheet` + `appendRow` block with a `BigQuery.Tabledata.insertAll(...)` call. Requires enabling the BigQuery advanced service in the editor (Services → +) and writing a project-scoped service-account token.

### Dedupe on event_id

Add a `_seen` sheet that records every event_id seen, and check it before appending. PropertiesService is too small for high-volume dedup. A dedicated sheet works for up to ~100k events; beyond that, use Firestore or BigQuery.

## 11. Apps Script quotas (relevant ones)

| Quota | Personal | Workspace |
|---|---|---|
| URL Fetch calls / day | 20,000 | 100,000 |
| Spreadsheet writes / day | unlimited (rate-limited) | unlimited (rate-limited) |
| Total execution time / day | 90 min | 6 hours |
| Script runtime per call | 6 min | 6 min |
| `doPost` response SLA | 30 sec | 30 sec |

For a moderate Square seller (~100 events/day) none of these are close to binding. For a high-volume seller (10k+/day), the per-call execution time and the `doPost` SLA matter — the script averages 0.5s per call, well below limits, but cold starts can hit 4-5s on the first event after a quiet period.

## 12. File-by-file complexity

| File | Lines | Bytes | What it does |
|---|---|---|---|
| `Code.gs` | 516 | 17,041 | Everything |
| `SETUP.md` | ~200 | ~10,500 | Short setup notes that ship with the source |

The whole thing is one file by design. Apps Script supports multi-file projects (each `.gs` file is auto-loaded into the same global namespace), but splitting this would make the deploy flow more error-prone for marginal benefit.

## 13. Known operational quirks

**Square 504s on the `/exec` URL.** Apps Script Web Apps don't return content from `/exec` directly — they return a 302 redirect to `script.googleusercontent.com` for the actual response body. For browsers this is invisible. For Square's webhook client, the redirect chain occasionally pushes total round-trip past Square's ~10-second client timeout. Square then logs a 504 in its UI and queues a retry, even though Apps Script processed the request fine in ~3 seconds. Effect: the row lands on the first attempt, then Square retries 1, 2, 3+ more times, each appending a duplicate row. Dedup downstream by `event_id`.

Fix path: front the script with a Cloudflare Worker (template in `square-webhook-to-sheets-INSTALL-AND-USAGE.md` section 9). The Worker returns 2xx directly without a redirect — Square sees success immediately and doesn't retry. The Worker also gives you true HMAC signature verification for free.

**Cold-start latency.** First POST to `/exec` after a quiet period (~30 min idle) takes ~4-5 seconds inside `doPost`. Subsequent POSTs in the same warm window take ~1 second. This is normal Apps Script behavior — there's no warm-up trick that helps.

## 13. Where the Apps Script standards are met

- **All function names** that aren't HTTP entry points (`doGet`/`doPost`) or bootstrap helpers (`setupConfig`/`generateUrlToken`/`precreateAllEventTabs`) start with `_` to mark them as internal. Apps Script doesn't enforce visibility but the convention helps future readers.
- **All Script Property reads** go through `PropertiesService.getScriptProperties().getProperty(key) || ''` — never assume the property is set; coalesce to an empty string and let the check logic decide.
- **All locking** uses `LockService.getScriptLock()` and is wrapped in try/finally so the lock always releases.
- **All exceptions** that should trigger a Square retry use `throw new Error(...)` (caught by Apps Script's web app handler, which emits a 5xx). Exceptions we want to swallow use `return _ok(label)` to emit a 200 with a status string.
- **All logging** uses `console.warn` (visible in the Apps Script Executions log) via `_logIssue(label, msg, sample)`. The third argument is sliced to 300 chars to avoid log bloat.
- **No global mutable state** — every doPost reads its own properties, opens its own SpreadsheetApp handle, releases its own lock. Apps Script doesn't share state across executions anyway, but the discipline of not relying on globals makes the code testable in isolation.