# Notion to Sheets Mirror — Code Explainer

Developer notes. Version 1.0.0 (2026-07-08).

## 1. Architecture at a glance

```
notion-to-sheets-mirror/
├── Code.gs     # the whole script (one file by design)
└── SETUP.md    # condensed setup notes shipped in the zip
```

Time-driven, gmail-to-sheets shape: no `doGet`/`doPost`, daily trigger + self-scheduled continuation triggers, LockService, Script Properties config. Read-only against Notion.

## 2. The core pipeline (`runMirror`)

1. Take the script lock (`tryLock(30000)`; a second concurrent run exits quietly).
2. Delete spent `continueMirror` one-shot triggers.
3. Load `MIRROR_STATE`. Absent → fresh pass: `_resolveDatabases()` builds the queue. Present → resume mid-pass (possibly mid-database via saved cursor).
4. For each queued database, `_mirrorDatabase()`: fetch schema, get-or-create the tab, clear + write header (fresh DB only), then query pages 100 at a time, flatten with `_rowForPage`/`_serializeProp`, append with `setValues`.
5. If `Date.now() - t0 > TIME_BUDGET_MS` between query pages: save state (queue + cursor), schedule `continueMirror` in 60s, exit. Rows already written persist; the resumed run appends from the saved cursor.
6. Queue empty → clear state, write `LAST_RUN_SUMMARY`, log `Pass complete`.

## 3. Responsibility map

| Concern | Function | Failure mode | Behavior |
|---|---|---|---|
| Discovery | `_resolveDatabases` | integration sees nothing | warn + no-op |
| Schema fetch | `_fetchDatabase` | 404/permission | warn + skip that DB |
| Page query | `_notionFetch` | 429/5xx | backoff retry ×5, then throw (run fails loudly, state not advanced) |
| Serialization | `_serializeProp` | unknown/odd shapes | JSON.stringify fallback; per-property try/catch → '' |
| Tab mgmt | `_getOrCreateTab` | title renamed in Notion | tab found by 8-char id fragment, renamed in place |
| Status | `_writeStatusRow` | — | upsert by database id |
| Continuation | `continueMirror` | — | thin wrapper so spent triggers are identifiable/deletable |

## 4. Schema model

Header = `page_id, page_url, created_time, last_edited_time` + every property name from the database schema, in schema order, rewritten every pass (NOT lazy-grow — a full rewrite makes stale columns disappear, unlike the webhook loggers). Serialization rules: title/rich_text → concatenated plain text; select/status → name; multi_select/people/files → comma-joined; date → `start -> end`; checkbox → TRUE/FALSE; relation → comma-joined page ids (`...` suffix when Notion reports `has_more`); rollup → typed value or ` | `-joined array; formula → typed value; unique_id → `PREFIX-n`; anything else → `JSON.stringify`. Cells truncate at `CELL_CHAR_LIMIT` with a `[TRUNCATED]` marker.

## 5. Settings storage

All config in Script Properties (see INSTALL doc table). Two managed keys: `MIRROR_STATE` (continuation JSON: `startedAt`, `queue`, `titles`, `cursor`, `rowsDone`, `dbsDone`) and `LAST_RUN_SUMMARY`. `setupConfig()` is idempotent (seed-only-if-unset) per PBJ convention, so it can host function-chaining.

## 6. Auth model

`NOTION_TOKEN` Script Property → `Authorization: Bearer`, `Notion-Version: 2022-06-28` (matches the other PBJ Notion scripts). No Web App, so no URL-token concern. Google-side auth is the standard first-run OAuth consent (Sheets + UrlFetch scopes auto-detected — no manifest edit needed since the People API pattern isn't used here).

## 7. Tab management

`_tabNameFor` = sanitized title (Sheets-forbidden chars `: \ / ? * [ ]` stripped, 80-char cap) + ` (first-8-of-id)`. `_getOrCreateTab` matches on the id fragment so Notion renames rename the tab rather than orphaning it. `_Mirror Status` is pinned at index 0. Any tab without an id fragment is never touched — safe for human notes.

## 8. Bootstrap helpers

- `setupConfig()` — idempotent seeding + required-property report
- `listDatabasesPreview()` — read-only discovery dump (the pre-flight check)
- `createDailyTrigger()` / `deleteTriggers()` — trigger management
- `runMirrorNow()` — clears stale continuation state, then full pass

## 9. Extension points

- **Diff-based sync-back** (the point of the meta columns): a sibling script can snapshot `last_edited_time` per `page_id`, compare passes, and push changed rows to Square/Google Contacts.
- Per-database property filtering: intersect `propNames` with an allowlist property.
- Incremental mode: add a `filter: {timestamp: 'last_edited_time', ...}` to the query payload and switch to upsert-by-page_id (trades simplicity for speed).
- Alerting: wrap `runMirror`'s throw path with `MailApp.sendEmail`.

## 10. Apps Script quotas

- Execution time ~6 min → `TIME_BUDGET_MS` 250s + continuation chaining
- UrlFetch 20k calls/day (consumer) — a 10-DB, 30k-row pass is ~350 calls
- Triggers: total trigger runtime 90 min/day — fine at daily cadence
- Properties 9KB/value — state JSON is tiny (ids only)

## 11. Known operational quirks

- Notion `/search` pagination can be slow on large workspaces; discovery happens once per pass.
- Notion rate limit ~3 req/s: `RATE_SLEEP_MS = 300` before every call + exponential backoff on 429/5xx (honors `Retry-After`).
- `relation` properties cap at 25 items in query responses (`has_more` → `...` suffix); full expansion would need per-page property requests.
- A pass that throws mid-database leaves the tab partially rewritten until the next successful pass (state is not advanced past the failure, so the retry redoes that database from its saved cursor or start).

## 12. File-by-file size

| File | Size |
|---|---|
| Code.gs | ~516 lines / ~20 KB |
| SETUP.md | ~1.5 KB |

## 13. Conventions met

- Single `Code.gs`, `// ===` section banners, scaffold order
- Bootstrap helpers plain-named; internals `_`-prefixed
- `props.getProperty(k) || ''` everywhere; nothing hard-coded
- LockService around the whole run, try/finally release
- Loud failures (throw) for retryable states; quiet skips documented in log
- `_logIssue` with ~300-char samples
- Idempotent bootstrap functions (chain-host convention, 2026-07-03)