Google Sheets Add-on — apps/addon-sheets

Architecture & audit documentation · ExcelRewind monorepo · generated from source read of apps/addon-sheets/src, findings_googlesheets.md, and root CLAUDE.md

1. Overview

apps/addon-sheets is the Google Sheets port of the ExcelRewind recorder: a sidebar add-on that watches a spreadsheet during a work session and produces a .rewind companion file — the same container format the Excel task pane writes, replayed by the same zero-backend web viewer. It ships as a Google Workspace Editor Add-on (a bound or standalone Apps Script project), not a web app with its own server.

The module has two runtime halves that live in one dist/ folder pushed by clasp:

• Server (src/server/Code.js) — plain, unbundled Apps Script (V8 runtime). Two read-only RPC entry points (rewindBaseline, rewindPoll) plus the onOpen/onInstall menu hooks that show the sidebar. It never writes to the spreadsheet and registers no triggers.
• Sidebar (src/sidebar/*.tsx) — a React 18 + TypeScript app built by Vite (vite-plugin-singlefile) into one self-contained dist/sidebar.html, because HtmlService can only serve a single file. It runs inside a sandboxed HtmlService iframe (300px task-pane width) and talks to the server exclusively via google.script.run.

Recording is poll-diff, not event-driven: Apps Script has no push channel into an open sidebar and no full/lossless edit-trigger, so the sidebar calls rewindPoll() every ~2s, diffs the returned grid against a client-held shadow map (src/engine/diff.ts), and turns differences into the same CellChangeEvent shape the Excel add-in's worksheets.onChanged handler produces. This is a deliberate, verified architectural choice — see findings_googlesheets.md for why onEdit triggers were rejected (2-event queue cap, no old formula, extra OAuth scope, 90 min/day quota).

Build/deploy is npm run build (bundles sidebar + copies Code.js/appsscript.json into dist/) then clasp push (uploads dist/ as the Apps Script project, per developer's own .clasp.json — not committed, each dev copies .clasp.json.example and fills in a Script ID). There is no CI/CD pipeline in-repo for this app yet; distribution is manual clasp push to an unlisted/private Marketplace deployment for beta testers. OAuth scope is declared in appsscript.json as spreadsheets.currentonly only — the lightest Sheets scope tier, chosen specifically to avoid Marketplace OAuth-verification review.

The module reuses @excelrewind/format (container writer, types, A1 utilities) and @excelrewind/branding (all display strings, BUILD_TAG) unchanged from the Excel add-in, and keeps an intentionally separate copy (not a shared package) of the Supabase auth/access/telemetry client code (auth.ts, access.ts, telemetry.ts) — documented in-code as a deliberate precedent, not an oversight.

2. Architecture diagram

flowchart TB
  subgraph GOOGLE["Google Sheets host (Apps Script project)"]
    direction TB
    subgraph SERVER["src/server/Code.js (plain Apps Script, V8, unbundled)"]
      onOpen["onOpen() / onInstall()
adds 'ExcelRewind > Record session' menu"]
      showSidebar["showSidebar()
HtmlService.createHtmlOutputFromFile('sidebar')"]
      rewindReadSheet["rewindReadSheet_(sheet)
getDataRange + cap at 100000 cells"]
      rewindBaseline["rewindBaseline()
every sheet's grid, once"]
      rewindPoll["rewindPoll()
active sheet grid + active range + sheet names"]
      onOpen --> showSidebar
      rewindBaseline --> rewindReadSheet
      rewindPoll --> rewindReadSheet
    end

    subgraph SIDEBAR["src/sidebar (React, built to one dist/sidebar.html)"]
      direction TB
      mainTsx["main.tsx
createRoot -> App"]
      App["App.tsx
record/stop/save state machine"]
      SignIn["SignIn.tsx
email OTP form"]
      gas["gas.ts
typed google.script.run bridge
+ DEV fake grid"]
      auth["auth.ts
Supabase client, OTP, checkAccess()"]
      access["access.ts
pure evaluateAccess() / offlineAccess()"]
      telemetry["telemetry.ts
track() -> usage_events"]
      download["download.ts
Blob + a.download"]
      diff["engine/diff.ts
seedShadow / diffPoll / selectEvent
(pure, platform-agnostic)"]

      mainTsx --> App
      App -->|"signedout"| SignIn
      App --> gas
      App --> auth
      App --> diff
      App --> download
      auth --> access
      App --> telemetry
      SignIn --> auth
      telemetry -->|"imports supabase client"| auth
    end
  end

  subgraph FORMAT["@excelrewind/format (shared package, imported unchanged)"]
    writeRewind["writeRewind(doc)
deflate + MessagePack + ZIP + SHA-256"]
    toA1["toA1(row,col)"]
  end

  subgraph BRANDING["@excelrewind/branding (shared package)"]
    strings["PRODUCT_NAME, RECORDER_NAME,
BUILD_TAG, FILE_EXTENSION, FILE_MIME"]
  end

  gas -.->|"google.script.run.rewindBaseline()
google.script.run.rewindPoll()"| rewindBaseline
  gas -.-> rewindPoll
  diff -->|"toA1"| toA1
  App -->|"writeRewind(RewindDocument)"| writeRewind
  App --> strings

3. Data-flow diagrams

How a cell edit becomes a .rewind file on disk — nothing in this module ever leaves the user's browser + Google's own infrastructure; there is no ExcelRewind backend call for the core record path.

flowchart LR
  edit["User edits a cell
in Google Sheets grid"]
  poll["rewindPoll() server call
every ~2000ms
(google.script.run round trip)"]
  grid["ServerPoll JSON:
{activeSheet, activeRange,
grid: {values[][], formulas[][]},
sheetNames[]}"]
  shadow["Shadow (Map)
sheet -> A1 -> {v, f}
held in a React ref, in-memory only"]
  diffFn["diffPoll(shadow, frame, t)"]
  events["CellChangeEvent[]
{t, sheet, cell, from, to,
formula_from, formula_to, type}"]
  live["Live (React ref)
sessionId, title, tag, startedAt,
baseline, events[], annotations[]"]
  doc["RewindDocument
{meta, timeline, annotations, baseline}"]
  bytes["writeRewind(doc)
-> Uint8Array (ZIP: deflate+MessagePack, SHA-256)"]
  blob["Blob + ObjectURL + a.download
(HtmlService sidebar allows downloads)"]
  file[("*.rewind file
on the user's local disk")]
  viewer["ExcelRewind web viewer
(apps/viewer) — opened separately,
replays timeline.json"]

  edit --> poll --> grid --> diffFn
  shadow <-->|"read prev, mutate in place"| diffFn
  diffFn --> events --> live
  live -->|"Save Rewind button"| doc
  doc --> bytes --> blob --> file
  file -.->|"user opens file manually"| viewer

flowchart LR
  session["signInWithOtp(email)
verifyOtp(email, code)"]
  supa[("Supabase Auth
(shared project w/ addin-excel)")]
  profiles[("public.profiles table
status, tier, telemetry, email")]
  appcfg[("public.app_config table
id=1: enabled, message")]
  usage[("public.usage_events table
RLS: auth.uid() = user_id")]
  cache["localStorage
'rewind.access-check'
{at, email}"]

  session --> supa
  App2["auth.ts checkAccess()
(called from App.tsx)"] -->|"select .eq(id, session.user.id)"| profiles
  App2 -->|"select .eq(id,1)"| appcfg
  App2 -->|"write on success"| cache
  App2 -->|"read on network failure
(7-day offline grace)"| cache
  App2 -->|"rpc set_telemetry_level(level)"| profiles
  telemetryFn["telemetry.ts track()"] -->|"insert {user_id, event, props}
fire-and-forget"| usage

4. Process / sequence diagrams

4.1 Sidebar launch & access gate

sequenceDiagram
  participant User
  participant Menu as "Sheets menu"
  participant CodeJs as "Code.js"
  participant Sidebar as "App.tsx"
  participant Auth as "auth.ts"
  participant Supa as "Supabase"

  User->>Menu: "ExcelRewind -> Record session"
  Menu->>CodeJs: showSidebar()
  CodeJs-->>User: HtmlService iframe (dist/sidebar.html)
  Sidebar->>Sidebar: mount, useState(status: "loading")
  Sidebar->>Auth: refreshAuth() -> getSession()
  alt no VITE_SUPABASE_* env (local dev)
    Auth-->>Sidebar: authEnabled=false, status="ok" (ungated)
  else no session
    Auth-->>Sidebar: status="signedout"
    Sidebar-->>User: renders SignIn card
  else session exists
    Auth->>Supa: select app_config id=1 + profiles id=uid (parallel)
    Supa-->>Auth: {enabled,message} + {status,tier,email}
    Auth->>Auth: evaluateAccess(cfg, profile)
    alt allowed
      Auth-->>Sidebar: status="ok", cache last-good check
      Sidebar->>Sidebar: initTelemetry(level, uid), track('pane_open'), track('sign_in')
    else blocked (disabled tier / inactive profile / global kill)
      Auth-->>Sidebar: status="blocked", reason
      Sidebar-->>User: "Access unavailable" card + Re-check/Sign out
    end
  end

4.2 Record -> poll-diff loop -> save (core path)

sequenceDiagram
  participant User
  participant App as "App.tsx"
  participant Gas as "gas.ts"
  participant Server as "Code.js"
  participant Diff as "engine/diff.ts"
  participant Fmt as "@excelrewind/format"

  User->>App: click "Start recording"
  App->>App: checkAccess() re-verify (revoked-mid-session guard)
  App->>Gas: readBaseline()
  Gas->>Server: google.script.run.rewindBaseline()
  Server->>Server: every sheet: getDataRange().getValues()/getFormulas(), cap 100000 cells
  Server-->>Gas: {sheets: SheetGrid[]}
  Gas-->>App: BaselineFrame
  App->>Diff: seedShadow(baseline) -> Shadow map
  App->>App: liveRef = {sessionId, title, tag, baseline, events:[]}
  App->>App: track('record_start', {tag})

  loop every 2000ms while phase==="recording"
    App->>Gas: readPoll()
    Gas->>Server: google.script.run.rewindPoll()
    Server->>Server: active sheet only: getDataRange + getActiveRange()
    Server-->>Gas: {activeSheet, activeRange, grid, sheetNames}
    Gas-->>App: PollFrame
    App->>Diff: diffPoll(shadow, frame, t=(Date.now()-startMs)/1000)
    Diff->>Diff: walk grid vs shadow: emit value/formula events,
walk shadow-only cells: emit to:null (cleared)
    Diff-->>App: CellChangeEvent[] (mutates shadow in place)
    App->>Diff: selectEvent(lastRange, frame, t)
    App->>App: events.push(...batch), update UI counts
  end

  User->>App: click "Stop"
  App->>App: setPhase('stopped') (interval clears)
  App->>Gas: readBaseline() — FINAL full re-scan, all sheets
  App->>Diff: diffPoll per sheet grid (catches background-sheet edits + last <2s)
  App->>App: track('record_stop', {duration_s, event_count, note_count, audio:false})

  User->>App: click "Save Rewind"
  App->>App: build RewindDocument {meta, timeline:events, annotations, baseline}
  App->>Fmt: writeRewind(doc)
  Fmt-->>App: Uint8Array (ZIP bytes, SHA-256 integrity)
  App->>App: downloadBytes(bytes, sessionFileName(...)) -> Blob + a.download
  App->>App: track('save_file', {size_kb})
  App-->>User: "Saved <name>.rewind"

4.3 Failure paths handled in code

sequenceDiagram
  participant App as "App.tsx"
  participant Gas as "gas.ts"
  participant Server as "Code.js"
  participant Auth as "auth.ts"

  Note over App,Server: Poll failure (network blip, edit-mode lock, quota)
  App->>Gas: readPoll()
  Gas->>Server: google.script.run.rewindPoll()
  Server--xGas: withFailureHandler(reject)
  Gas--xApp: Promise rejects
  App->>App: catch -> setError('Polling failed: ...')
pollingRef cleared in finally (no overlap next tick)

  Note over App,Auth: checkAccess network failure at launch or record-start
  App->>Auth: checkAccess()
  Auth->>Auth: Promise.all(app_config, profiles) throws
  Auth->>Auth: read localStorage('rewind.access-check')
  Auth->>Auth: offlineAccess(cached, now): allowed if <7 days old
  Auth-->>App: {allowed:true} (grace) or {allowed:false, reason:'Could not verify...'}

  Note over App: stop() must never fail
  App->>App: try { final re-scan } catch {} — best-effort,
worst case keeps pre-flush event list

5. Interconnections

flowchart LR
  addon["apps/addon-sheets"]
  gsheets["Google Sheets host
(SpreadsheetApp API)"]
  gscript["google.script.run bridge"]
  supaAuth["Supabase Auth
(OTP)"]
  supaDb["Supabase Postgres
profiles / app_config / usage_events"]
  viewerOrigin["ExcelRewind viewer origin
(privacy.html link + manual file open)"]
  marketplace["Google Workspace Marketplace
(clasp deploy target)"]
  fmt["@excelrewind/format"]
  brand["@excelrewind/branding"]

  addon -->|"google.script.run"| gscript --> gsheets
  addon -->|"HTTPS / supabase-js"| supaAuth
  addon -->|"HTTPS / PostgREST"| supaDb
  addon -.->|"link only, no API"| viewerOrigin
  addon -->|"in-process import"| fmt
  addon -->|"in-process import"| brand
  addon -.->|"clasp push"| marketplace

6. Key functions & files

7. Invariants & gotchas

Platform quirks this code works around (see findings_googlesheets.md for the full "do not relearn" list):

• No push channel exists. Apps Script cannot message an open sidebar; google.script.run is strictly request/response. This is why the architecture is poll-diff rather than any kind of event subscription — there is no alternative on this platform.
• Simple onEdit triggers queue at most 2 events and drop the rest under fast typing; they also carry no old formula and no oldValue on paste. The poll-diff shadow map is deliberately the source of truth for from/formula_from instead — a case where this platform gives better from-data than Excel's own onChanged, per the comment at the top of diff.ts.
• The sidebar iframe blocks getUserMedia (sandboxed HtmlService permissions policy) — audio recording is not implemented in this phase; track('record_stop', ...) hardcodes audio: false. A future audio path would need a viewer-origin popup bridge, not in-sidebar capture.
• The sidebar cannot do the Google system-browser OAuth handoff that the Excel add-in uses for Google sign-in — auth here is email OTP only, fully in-iframe, no navigation.
• Timestamps must be wall-clock deltas ((Date.now() - live.startMs) / 1000) computed at diff time, never a poll-tick counter — background-tab JS timer throttling would otherwise skew elapsed time. This is called out explicitly in the top-of-file comment in diff.ts.
• Only the active sheet is polled at full rate. Background-sheet edits are captured only when that sheet becomes active, or at the final full re-scan on Stop — a deliberate cost/completeness trade-off, not a bug, but a real gap between an edit happening and it landing in the timeline if the user never revisits that sheet before stopping.
• Sheets' getFormulas() returns '' for non-formula cells (not null) — asFormula() in diff.ts treats only strings starting with '=' as a formula, everything else normalizes to null.
• Multi-editor noise is recorded as-is. If another collaborator edits the sheet while a session is being recorded, their changes appear in the diff exactly like the recording user's own edits — documented as a known v1-port limitation, not filtered.
• Scope minimalism is a Marketplace-review lever, not just a security nicety. spreadsheets.currentonly is the lightest tier; anything needing drive/drive.readonly triggers OAuth verification, and broader drive* scopes trigger an annual CASA security assessment — this is why snapshot/xlsx export is deferred pending a scope spike (findings_googlesheets.md risk 1), not implemented speculatively.
• No crash-recovery buffer yet. Unlike the Excel add-in's IndexedDB crash-draft (with at-rest encryption, per CHANGELOG.md), an in-progress Sheets recording lives only in a React ref — closing the sidebar mid-session loses it. Explicitly flagged as deferred in the README, not yet ported.
• auth.ts/access.ts/telemetry.ts are intentional copies, not shared code with apps/addin-excel's equivalents — inline comments state this is precedent, so a future refactor to share them is a deliberate choice, not an obvious cleanup.