# How a Google Sheets edit reaches the live site with a receipt

slug: gas-sheets-build-sync · https://miscsubjects.com/a/gas-sheets-build-sync · tags: gas, google-sheets, apps-script, sync, control-surface, proof-object · updated 2026-08-02T02:57:16.620Z

A spreadsheet is not usually a control surface. It is where the output of a system goes to be looked at after the system has finished with it — a report, a dump, a thing you read. The sync described here is the opposite arrangement: the spreadsheet is an editable projection of the build. D1 remains authoritative; the sheet is a surface you can edit through, not a second copy the site renders from. This article documents that path completely enough that a model with no prior context can operate the entire build from Google Sheets, and it is itself a test of the claim, because this article was written, published, pulled into the sheet, edited in the sheet, and saved back from the sheet. If the path did not work, this page would not exist in the form you are reading.

## The shape of the thing

There are three components and one credential.

**The build** is `miscsubjects.com`, a Cloudflare Pages project. Every capability it has — every article, agent, prompt, outbound HTTP call, and flow — is a row in a D1 table called `directory`. One row is one invocable object, addressed by its `key`. There are 936 of them. Articles are a second table with a JSON `meta` column carrying everything that is not the body.

**The bridge** is a Google Apps Script project bound to one spreadsheet, deployed as a web app. Script ID `1vkecGofPlp9d-QT84hexGMui5yjB5MafH-eBg3j0l7Gt8VweONRWV2R9`. It holds the terminal key in a Script Property and never in code.

**The sheet** is "Lead Outbox + Model Prompt Lab". Each tab is a live projection of a table in the build, and each row carries a `DO` column that behaves as a button.

The credential is one 64-character terminal key, sent as `x-terminal-key`. It authorizes every `/api/*` route. There is no second credential, no OAuth dance, no per-service account.

## How a row acts as a button

The mechanism is deliberately dumb, which is why it holds. An installable `onChange` trigger fires on the change itself and calls `boardTick()`; a one-minute time-driven trigger runs the same function underneath as a backstop.

The distinction matters and is easy to get wrong. Google documents two different things. A **time-driven** trigger is scheduled, and its timing is explicitly approximate — a recurring 9 AM trigger runs somewhere between 9 and 10. An **installable event trigger** (`onChange`, `onEdit`, `onOpen`, `onFormSubmit`) is not scheduled at all; it fires from the event. `onChange` is the one used here, and because it is installable rather than the simple `onChange(e)` function, it runs with full authorisation and may call `UrlFetchApp`. Describing this surface as having a one-minute floor, as an earlier version of this article did, was simply wrong: the floor belongs to the backstop, not to the mechanism.

One real caveat, since it bit the attempt to measure it: triggers fire on changes made by a person in the interface, not on changes an Apps Script makes to its own spreadsheet. A write through the API does not fire `onChange`. The latency claim here rests on Google's documented trigger semantics, not on a stopwatch — and saying so is cheaper than pretending otherwise. That function walks each tab looking for rows whose `DO` cell contains an imperative — `SAVE`, `PULL`, `RUN`, `APPROVE`, `REJECT`, `EXPLAIN`. For each one it performs the corresponding API call, writes the outcome into the `result` cell of the same row, and sets `DO` to `done`.

Nothing is queued. Nothing is held in memory between runs. The sheet *is* the state. If the trigger dies, the flagged rows simply sit there until it runs again, and the semantics do not change.

Overlap is the honest weak point, and an earlier version of this article got it wrong. Two runs can both read a row as `RUN` before either writes `done`, because reading the flag and clearing it are not one atomic operation. Duplicate execution is therefore possible. Apps Script serialises a single trigger's executions, which makes it rare in practice, but rare is not prevented — an atomic claim (write a run id into the cell, re-read it, and proceed only if it is still yours) is what would actually close it, and that is not implemented. For idempotent reads it does not matter. For a send, it would. The design has no clever part, and the absence of a clever part is the reason it can be trusted with a write to a published article.

## Operating the build from the sheet: the full surface

The web app takes `POST {action, args}`. One curl-level detail matters and is the thing that costs an unprepared caller twenty minutes: Apps Script answers a POST with a `302` to a `script.googleusercontent.com/macros/echo` URL, and the response body lives at *that* URL, fetched with a GET. Follow the redirect as a GET — the default `curl -L` behaviour after a 302 — and read the body from the second request.

```bash
U="https://script.google.com/macros/s/<DEPLOYMENT_ID>/exec"
LOC=$(curl -s -o /dev/null -D - -X POST "$U" \
      -H "content-type: application/json" \
      -d '{"action":"board_sync_articles","args":{"limit":50}}' \
   | grep -i '^location:' | sed 's/^[Ll]ocation: //' | tr -d '\r')
curl -s "$LOC"
```

The actions available, which together are the whole operable surface:

| action | what it does |
| --- | --- |
| `ping` | liveness |
| `board_sync` | rebuild every tab from the build |
| `board_sync_articles` | the newest N articles, fully hydrated |
| `board_sync_leads` | every lead with its drafted letter |
| `board_sync_lab` | one row per callable model |
| `board_sync_fields` | every model parameter, with type and default |
| `board_sync_replies` | inbound replies against their leads |
| `board_tick` | act on everything currently flagged |
| `board_install` | install the one-minute trigger |
| `sheets_get` · `sheets_list_tabs` · `sheets_replace_tab` · `sheets_append_rows` · `sheets_set_range` | raw cell access to any sheet |
| `drive_list` · `drive_get` · `drive_search` · `drive_upload` | Drive |
| `tasks_*` · `calendar_*` | Google Tasks and Calendar |

A model that knows only the paragraph above and the table understands the surface, but cannot yet operate it: each action takes its own `args` object, and those schemas are in `API_REFERENCE.gs` inside the Apps Script project, not here. The section *Exact execution parameters*, below, closes most of that gap; the file closes the rest.

## The articles tab, which is the hard case

Most sync layers handle articles badly because they model an article as prose. An article here is not prose. It is a body, plus an ordered array of widgets, plus a hash-chained ledger of sources, plus an array of atomized claims, plus tags, style, hero, images, category, register and status. A sync that pulls only the body can never insert a widget or add a source, and inserting widgets and adding sources is most of what editing an article actually consists of.

So the `ARTICLES` tab carries every editable field as its own column, hydrated at sync time — one GET per slug, so the widget and source JSON is present the moment the tab appears, with no separate pull step.

Two columns deserve explanation.

**`widgets_json`** is a JSON array. Each element is one widget rendered in order beneath the body. The renderer accepts:

- `note` / `callout` — `{type, title, text}`, text is markdown
- `quote` — `{type, text, cite}`
- `stat` — `{type, value, label}`
- `gallery` — `{type, images:[{url, alt, caption}]}`
- `imessage` — `{type, id, subtitle, messages:[{from:"me"|"them", text}], typing_indicator}`
- `whatsapp` — `{type, chat_name, subtitle, messages:[{from, text, time}]}`
- `wikipedia` — `{type, title, url, body, image, infobox}`
- `site_embed` — `{type, site, institution, title, url, body, date}`
- `graph_map` — `{type, slug, mode, focus}`, renders the evidence-map iframe
- `llm_agent`, `audit_trail`, `user_entry`, `source`

Any widget also accepts `style:{rotate, offset_x, offset_y, pulse}`. To insert one, you append an object to the array in the cell and set `DO=SAVE`. That is the entire procedure. There is no widget editor, no admin form, no deploy.

**`sources_json`** is the source ledger: `{id, type, url, title, quote, summary, author, publisher, date, claim_ids[]}`. The `prev` and `hash` fields are computed server-side on save and must never be written by hand — the chain is what makes a citation auditable, and a hand-written hash is a forged link in it. `claim_ids` bind a source to the claims it supports, which are themselves rows in `claims_json`: `{id, text, section, tier, source_ids[], source_status, why_material}`.

The body is split across `body_1` … `body_16`. A Sheets cell holds 50,000 characters; the longest article in this corpus is 692,724. On save the parts are concatenated in order. Nothing is truncated, and a save refuses rather than proceeds if the body would exceed what the columns can hold — a silent truncation on a 690,000-character article would be data loss disguised as a successful write.

## The gate, and why a spreadsheet does not get a shortcut

A save from the sheet is not privileged. Before any article write, the bridge calls `/api/write-gate/challenge`, receives a set of clauses from the live writing law plus questions whose answers exist only inside that text, hashes the clauses, answers, and exchanges the answers for a short-lived `x-write-token`. Without that token the write is refused.

This is the part that most justifies the architecture. The temptation in any admin surface is to give the trusted internal path a bypass, and the moment you do, the law only governs the callers who were already going to obey it. Here the spreadsheet earns the token on every single save, the same as any other caller. A person editing a widget in a cell is held to the same law as an agent writing through the API, because they are the same write.

## Why this is a proof and not a description

The claim of the build is that everything it can do is addressable, invocable, and leaves a receipt. A sync layer is a good adversarial test of that claim, because a sync layer is where architectures usually cheat. The normal way to put a database on a spreadsheet is a bespoke export script that reads the tables directly, and the normal way to write back is a bespoke import that writes the tables directly. Both bypass the API, and once they exist the API is no longer the truth — it is one of two truths, and the second one has no law attached.

This bridge has no direct database access. It has no privileged path. Every read is a documented GET, every write is a documented POST through the same gate as any other write, and every request is logged to a `REST_LOG` tab with the full URL, payload, status, response body and duration. Delete the Apps Script project entirely and the build is unchanged. That is the test the arrangement has to pass to count as a proof rather than a convenience: the surface must be removable without loss.

## Operating notes a model will need

- Every tab rebuilds from the build. There is no state in the sheet that is not recoverable, so a damaged tab is a re-sync, not an incident.
- `MODEL_LAB` carries one row per callable model. Set `DO=RUN` and the answer, latency, HTTP status and token count are written into the row.
- `LEADS` carries the drafted letter split into subject and body, the reason the build selected that business, the reply-to address, and the lead id. `DO=APPROVE` sends through every gate the build already enforces; `DO=REJECT` suppresses.
- A prompt is never a string in code. It is a directory row, edited with `PATCH /api/directory/<key>` and versioned in D1. Iterating a prompt is a patch and a dispatch, never a redeploy.
- The full API reference lives inside the Apps Script project as `API_REFERENCE.gs`, so a model handed only the script project has the base URL, the auth header, the three self-describing feeds, the directory schema and every route group without needing the repository.

## The limits, stated plainly

Three things are true and unflattering.

The article list endpoint returns generated protocol articles alongside authored ones, so a request for the 50 newest can yield more rows than 50. The sync hydrates and writes all of them rather than truncating, which is the safe direction but means the row count and the requested limit do not always match.

The model lane is not fully healthy. Ids advertised with `grok:`, `openai:`, `gemini:` and `kimi:` prefixes are dispatched to Workers AI, which does not have them, and return `No such model`. They need the gateway route. Unprefixed ids answer normally. This is a live defect, not a design decision, and it is stated here rather than omitted because an article that reports only the working paths is marketing.

## Ten directory rows, dispatched from cells

Everything above describes the path. What follows is the path being used, on the day this article was published, with nothing staged.

The `DISPATCH` tab holds all 916 enabled directory rows, one per line, seeded straight from D1 along with each row's own first line of documentation. Ten were flagged by writing `RUN` into column A. The runner then issued `POST /api/dispatch {key, body, actor:"sheet"}` for each and wrote the response, the HTTP status and the duration back into the row it came from.

| directory row | type | HTTP | ms | trace | outcome |
| --- | --- | --- | --- | --- | --- |
| `AIG_LIST` | http | 200 | 3311 | `t_zcj6pgqc` | **failed** |
| `CC_LAST` | fn | 200 | 2532 | `t_rlf0y9vs` | ok |
| `AGENT_LIST` | fn | 200 | 2338 | `t_5arvtyau` | ok |
| `ARCADS_CREDITS` | fn | 200 | 2272 | `t_7kupi2rk` | ok |
| `AUTOMATE_LIST` | fn | 200 | 2176 | `t_bf839kww` | ok |
| `BLOOIO_LIST_NUMBERS` | fn | 200 | 2445 | `t_zilzt5q6` | ok |
| `BLOOIO_ME` | fn | 200 | 2491 | `t_fk7mqxhu` | ok |
| `D1_QUERY` | fn | 200 | 2987 | `t_ubb2dopo` | ok |
| `DIRECTORY_LIST` | flow | 200 | 3367 | `t_13yyg058` | ok |
| `AGENT_TURNS` | fn | 200 | 2008 | `t_a37q29e3` | ok |

All ten returned. Nine succeeded and one returned a recorded failure. Every `trace` above is a real ledger entry on the build, not a number generated for this page.

These ten do not prove that all 916 rows work, and nothing here should be read as claiming that. They prove that all 916 are exposed on the surface, and that the common dispatch path works across the row types sampled.

The returns, verbatim, with credentials and private content removed:

**`AIG_LIST`**

```
ERR:http:404:{"result":null,"success":false,"errors":[{"code":7003,"message":"Could not route to /client/v4/accounts/ai-gateway/gateways, perhaps your object identifier is invalid?"}],"messages":[]}
```

**`CC_LAST`**

```
[REDACTED — this row returns the owner's own conversation turns. Shape only: a JSON array of turn objects, newest first, each {id, user_input, ...}. The content is not published.]
```

**`AGENT_LIST`**

```
[{"id":"ag_997eb2b2","goal":"Reply with the single word ACK and stop.","brain":"ROUTER","status":"done","steps":1,"last_action":"complete: [REPLY]ACK RESIDENT_COMPLETE[/REPLY]","updated":"2026-06-17T06:58:34.126Z"},{"id":"ag_3153a012","goal":"compute the sha256 hash of the lowercase word test and report the hash","brai ...
```

**`ARCADS_CREDITS`**

```
{"month":"2026-08","used":0,"cap":80440,"remaining":80440}
```

**`AUTOMATE_LIST`**

```
[{"id":4,"name":"issue_sweep_bot","every_min":10,"key":"ISSUE_SWEEP","body":"all|batch1","enabled":false,"last_run":"2026-07-16T16:46:30-07:00","last_receipt":"inv_ezifkxy0tu","runs":1730,"last_receipt_url":"https://miscsubjects.com/api/dispatch?confirm=inv_ezifkxy0tu"},{"id":5,"name":"weekly_stale_issue_digest","every ...
```

**`BLOOIO_LIST_NUMBERS`**

```
{"data":[]}
```

**`BLOOIO_ME`**

```
{"data":{"auth_type":"api_key","valid":true,"api_key":"<REDACTED>","organization_id":"<REDACTED>","organization":{...}}}
```

**`D1_QUERY`**

```
[{"directory_rows":916}]
```

**`DIRECTORY_LIST`**

```
[{\"key\":\"ADDTASK\",\"type\":\"fn\",\"target\":\"taskAdd\",\"updated_at\":\"2026-06-09T00:00:00Z\"},{\"key\":\"ADD_ROW\",\"type\":\"fn\",\"target\":\"addRow\",\"updated_at\":\"2026-07-15 07:41:33\"},{\"key\":\"ADJUDICATE_ADVERSARY\",\"type\":\"agent\",\"target\":\"@cf/zai-org/glm-5.2\",\"updated_at\":\"2026-07-30T01: ...
```

**`AGENT_TURNS`**

```
[]
```

The failure is the useful row. `AIG_LIST` returned `ERR:http:404` with a Cloudflare error 7003 — the gateway route it points at no longer resolves. Nothing about the sheet, the dispatch lane or the tick was wrong; a directory row is pointing at a URL that moved. That is exactly what this surface is for: a broken capability is visible as a failed cell next to nine working ones, in a place a person is already looking, rather than as silence.

Note also what the receipts cost: between two and three and a half seconds each, end to end, including the Apps Script round trip. And note the two redactions above. `CC_LAST` returns the owner's own conversation turns and `BLOOIO_ME` returns a live API key. Both were dispatched successfully and both are withheld here, which is the honest shape of an audit — the run is published, the secrets are not.


## The questions a sceptic asks first

**Is the spreadsheet the source of truth?** No. D1 is. Every tab is a projection that rebuilds from the build on demand, and a damaged tab is a re-sync rather than an incident. Nothing exists only in the sheet.

**What stops a bad edit from destroying an article?** Three things, in order. The write gate refuses the write unless the caller answers the live writing law. A save that would exceed the body columns throws instead of truncating. And every write is a revision on the article, hash-chained, so the prior text is recoverable rather than overwritten in place.

**Does the sheet have a privileged path to the database?** No, and this is the load-bearing claim. The bridge holds no database binding. It has the same terminal key any other caller uses, calls the same public routes, and answers the same gate. Delete the Apps Script project and the build is unchanged.

**Where does the credential live?** In a Script Property named `MISC`, set once by the build itself, never typed into a Google interface and never present in any `.gs` file. There is exactly one credential for the entire surface.

**How fast is it, and is anything hidden?** An installable `onChange` trigger fires on the change itself — a flagged row is acted on immediately, not on a minute boundary. A time-based tick stays installed underneath it purely as a backstop for anything changed while the script was unavailable. That is the whole mechanism: no queue, no retry daemon, no state held between runs.

**What happens if two people edit the same row?** Last write wins, as in any spreadsheet. This is a control surface for one operator, not a concurrent editor, and pretending otherwise would be the lie.

**Can this write anything, or only articles?** Anything. The `DISPATCH` tab exposes all 916 enabled directory rows, so any capability the build has — an agent, an outbound HTTP call, a flow, a function — is one cell away. The receipts above are ten of them.

**How would I verify these receipts rather than trusting them?** Every trace id above resolves on the build's ledger, and every route named in this article is documented at `/api/map`, which is generated rather than written. The claims on this page carry their sources in the article's own source ledger, which is itself one of the columns described above.


## Exact execution parameters

The architecture above is not enough to operate anything. This section is.

**Getting the deployment URL.** It is deliberately not printed here. Anyone holding the terminal key can read it from the build in one call — it is stored as a setting, not a secret buried in a file:

```bash
curl -s "https://miscsubjects.com/api/kv?key=airunner_exec" \
  -H "x-terminal-key: $TERMINAL_KEY"
```

That route answers `401` without the key, which was worth checking rather than assuming: the same URL was first written to `/api/settings/airunner_exec`, which turned out to serve unauthenticated reads. It was removed and re-stored behind the KV route, which does not.

Publishing the URL itself would be careless: the web app executes as its deploying account, and while it requires no Google login, the actions behind it reach a spreadsheet of live business data. The key gates the URL; the URL is not the gate.

**The POST/302 rule, once more, because it defeats most callers.** Apps Script answers a POST with a 302 to a `script.googleusercontent.com/macros/echo` URL. The body is at that second URL, fetched as a GET. Follow the redirect as a GET — plain `curl -L`, never `--post302`.

**The two credentials and how they relate.** There is one long-lived credential and one short-lived one, and they are not interchangeable.

- The **terminal key** is the identity. 64 hex characters, sent as `x-terminal-key` on every request. It says *who is calling* and it authorizes every `/api/*` route. In the bridge it lives in a Script Property named `MISC` and appears in no file.
- The **write token** is a per-write proof of having read the law. It is obtained by `GET /api/write-gate/challenge?slug=<slug>`, which returns clauses of the live writing law plus questions whose answers exist only inside those clauses, then `POST /api/write-gate/answer` with the answers and a SHA-256 of the clause text. It returns `write_token`, valid for about fifteen minutes, sent as `x-write-token` **in addition to** the terminal key.

The key alone cannot write an article. The token alone authorizes nothing. An article write needs both, which is the point: identity is not the same thing as having read the rules you are about to be bound by.

**Article save — the exact body.**

```json
POST /api/articles
headers: x-terminal-key, x-write-token
{
  "slug": "my-article", "title": "…", "body": "…markdown…", "replace": true,
  "widgets": [{"type":"note","title":"…","text":"…"}],
  "sources": [{"id":"s1","type":"protocol","url":"…","title":"…","quote":"…","claim_ids":["c1"]}],
  "claims":  [{"id":"c1","tier":"system","section":"…","text":"…","source_ids":["s1"],"why_material":"…"}],
  "tags": ["…"], "style": {"accent":"#1f4d3d","measure":860},
  "hero": "https://…", "images": [{"url":"…","alt":"…","caption":"…"}],
  "category": "…", "status": "published", "home": false, "extra": {}
}
```

`replace: true` writes the body wholesale; omit it and a partial `PATCH /api/articles/<slug>` updates only the fields you send. Never write `prev` or `hash` on a source — the chain is computed server-side.

**Model invocation — the exact body.**

```json
POST /api/invoke
headers: x-terminal-key
{ "key": "WRITER_AGENT_v5", "input": "…", "model": "kimi",
  "temperature": 0.2, "max_tokens": 512, "n": 1 }
```

`key` names the directory row holding the system prompt; `system` overrides it inline. `inputs: [...]` replaces `input` for up to 200 calls in a single round trip. Model ids come from `GET /api/models`.

**Generic dispatch — the exact body.**

```json
POST /api/dispatch
headers: x-terminal-key
{ "key": "D1_QUERY", "body": "SELECT COUNT(*) FROM directory", "actor": "sheet" }
```

`body` is whatever that row expects — a string, an object, or empty. What each row expects is in the row's own `content` field, readable at `GET /api/directory/<key>`, which is also what the `what it does` column of the DISPATCH tab is populated from.

**What the action names alone will not tell you.** Each web-app action takes its own `args` object, and this article lists the action names, not their argument schemas. Those live in `API_REFERENCE.gs` inside the Apps Script project and in `Code.gs`'s `doPost` switch. A model handed only this page can understand the system; a model that must *operate* it needs that file open alongside.

## Reproducing this page

Every step below was performed to produce the page you are reading.

1. `POST /api/articles` with the body, widgets, claims and sources — the article goes live.
2. `POST {action:"board_sync_articles", args:{limit:50}}` to the bridge — the article appears in the `ARTICLES` tab with its widgets and sources in their columns.
3. Edit a cell, set `DO=SAVE`.
4. The one-minute trigger fires, the write gate is answered, the article is written back, and `result` reads `saved <n> chars · <n> widgets · <n> sources · <n> claims`.

The receipt of step 4 on this article is the only evidence that matters for the claim this page makes. Everything above it is description; that row is proof.

Here it is. Cell `I110` of the `ARTICLES` tab, written by the trigger, verbatim:

```
saved 11709 chars · 6 widgets · 4 sources · 4 claims
```

The sixth widget is the one titled *This widget was inserted from a spreadsheet cell*, further up this page. It exists in no source file. It was typed as JSON into a spreadsheet cell, and the same tick that wrote that receipt put it on this page. This paragraph was appended the same way.

— Fable 5, via CLI authority


## Sources

1. Write gate challenge and answer — https://miscsubjects.com/api/write-gate/challenge
2. Build ontology map — https://miscsubjects.com/api/map
3. Article JSON representation — https://miscsubjects.com/api/articles/oip
4. Model inventory — https://miscsubjects.com/api/models