google-sheet-reverse.md

CLAUDE.md

Goal

Reverse-engineer an existing Google Sheet into a structured spec, as the foundation for rewriting it into a SaaS. The Sheet's logic lives in two separate places — extract both into JSON:

1. Apps Script code (.gs / .html) — the real business logic, triggers, external integrations.
2. Spreadsheet formulas & structure — calculations and rules hidden inside cells.

The end deliverable of this repo is output/spec.json (or two files), not the SaaS itself. We're producing the blueprint.

---

Prerequisites (do once)

• Google Cloud project with Apps Script API and Google Sheets API enabled.
• In the Sheet: 擴充功能 → Apps Script → 專案設定 to find the Script ID.
• The Spreadsheet ID is in the sheet URL: .../spreadsheets/d/<THIS>/edit.
• For Apps Script extraction, enable API access at https://script.google.com/home/usersettings.
• Auth: OAuth (clasp handles it) or a service account with the Sheet shared to it.

Set these before running anything:

export SCRIPT_ID="..."
export SPREADSHEET_ID="..."

---

Track 1 — Apps Script code → JSON

Preferred (gives clean local files + native JSON):

npm install -g @google/clasp
clasp login
clasp clone "$SCRIPT_ID"          # pulls all .gs / .html locally

Or hit the API directly for raw JSON:

GET https://script.googleapis.com/v1/projects/{SCRIPT_ID}/content

Response: each files[] has name, type (SERVER_JS / HTML / JSON), source (full code string). Iterate files[], parse each source, list every function.

Track 2 — Spreadsheet formulas & structure → JSON

The key is valueRenderOption=FORMULA — returns =VLOOKUP(...) strings, NOT computed values:

GET https://sheets.googleapis.com/v4/spreadsheets/{SPREADSHEET_ID}?includeGridData=true&valueRenderOption=FORMULA

Capture from the response: per-cell formulas, namedRanges, dataValidation, conditionalFormats, charts.

---

Target output structure

output/
  apps_script.json   # files[], and a flattened function inventory
  sheets.json        # per-sheet formulas, named ranges, validation, formats
  spec.json          # merged + annotated (the deliverable)

When merging, build a function inventory: name, source file, params, what it touches, and whether it's user-facing logic vs. glue.

---

Gotchas — flag these explicitly during extraction

• Event/trigger logic (onEdit, onOpen, time-driven triggers) does NOT map 1:1 to a SaaS. Tag each one — they usually become webhooks or scheduled jobs.
• Business rules hide in cell formulas, not just in Apps Script. Don't skip Track 2 or you'll lose them.
• Cross-check the two tracks: a function and a formula may implement the same rule in two places.
• Note any third-party Apps Script libraries / external API calls — they're integration points to replace.

---

Working notes

• Pick one language for the extraction scripts (Node or Python) and keep it consistent.
• Treat output/*.json as generated artifacts; don't hand-edit apps_script.json / sheets.json.
• spec.json is the only file meant to be reviewed/annotated by humans.