Formwork 🚀

A Google Workspace add-on for teams that run on Google Workspace and outgrew their intake forms — turn a Google Form into a guided intake wizard, capture the answers as durable records, project them into a real relational database, and generate the paperwork on submit.

Built entirely on Google Apps Script (V8). Google Sheets is the database, Google Docs is the output, CardService is the UI. No external services, no external API calls — all data stays inside the customer's own Workspace.

Origin note: Formwork began as the multi-tenant generalization of a single-tenant scheduling tool built for one security-operations team. This repository is the generalized line, and its history starts where the two diverged. Issue references in commits and design docs predating that split point to the original private tracker and are historical. See CLAUDE.md for the architecture rationale.

Development note: built with Claude Code doing a substantial share of the implementation, including a subset of commits (claude[bot]) from an autonomous issue-discovery/implementation loop governed by the rules in CLAUDE.md. Architecture, scope decisions, and review are mine.

---

What it does

FormCompiler — Google Form → CardService wizard. Paste a Form link; the add-on compiles it into a multi-page wizard rendered inside Gmail/Sheets. Beyond the Form's own fields you can insert synthetic steps the Form can't express: a live employee roster picker (EMPLOYEE_SELECTION), shift entry with per-shift assignment (SHIFT_ENTRY), a single appointment slot (APPOINTMENT_SLOT), and client/job/contact pickers (CLIENT_SELECTION, JOB_SELECTION, CONTACT_SELECTION). Ships with 24 packaged wizards across 5 functional categories and 7 industry tags (general, security, healthcare, events, field service, recruiting, education).

DocTemplate — wizard → Google Doc. Author a Doc with merge tokens ({{ client_name }}, {{#each shifts}}…{{/each}}); on submit the captured answers fill a fresh copy. 20 ready-made Doc blueprints are generated in-code, so no Drive scope is ever needed.

Submissions as the system of record. Every wizard run always writes a durable DB_Submissions row — the raw capture, never lost. From that parent, the projector optionally derives Job / Client / Contact records, each granted by an explicit step rather than inferred from free text. Submissions are searchable, editable (re-open and re-submit), carry a status lifecycle (SUBMITTED → IN_REVIEW → APPROVED → ARCHIVED), and index their choice-field answers as facets for the dashboard.

Rosters & compliance. Agent roster with license tracking; client roster with archive and merge (for cleaning up duplicate intakes); contact roster.

Scheduled email alerts. Two time-based triggers, both per-tenant and installed from the Settings → Alerts card. Recipients live in the team's DB_Settings sheet, not in Script Properties — per team, not per deployment.

Alert	Cadence	Sends	Dedup
Pre-shift (AlertService)	Hourly trigger, fires ~12h before start	One digest of every job whose earliest assigned shift starts in the window — agent, time, site, date span	shiftId → shift start date map, written only after the send succeeds; a rescheduled shift naturally re-alerts
License expiry (LicenseAlertService)	Daily trigger	Agent licenses crossing configurable thresholds (e.g. 90/60/30 days), each bucketed into the smallest threshold it qualifies for	Per-license JSON on LINK_AgentLicenses — one alert per threshold crossed — plus a once-per-day per-tenant guard

The hourly trigger is installed per user, so a team with several installers runs concurrent executions against the same settings row; the decide → send → persist sequence therefore runs inside a single LockUtils.withScriptLock hold. The obvious optimization — reserve under the lock, send outside it — is deliberately not taken, because it trades "duplicate email" for "silently dropped alert" without compensating rollback.

Dashboard. KPI card plus a generated spreadsheet export — submission funnel by status, volume by wizard, and answer-value breakdowns.

---

Screenshots

Home — saved wizards by category	Wizard step — CLIENT_SELECTION account picker
Wizard step — SHIFT_ENTRY	Dashboard — submission funnel at a glance
Agent roster

---

Architecture

Layer	Contents
0_constants/	GlobalConstants, SettingsStore, Config, DatabaseConstants, UiConstants, FormCardConstants, DocTemplateConstants, WizardJobConstants
1_domain/	0_enums/, 1_model/, 2_aggregates/ — pure, no Google services
3_utils/	Dates, UUIDv7 ids, phone, locking, logging
4_infrastructure/	Repositories (the only DB seam), init/, migrations/, maintenance/
5_application/	FormCompiler, DocTemplate, FormCardProjector, alert services, mappers & intake services
6_ui/	CardService components/ and actions/
7_tests/	Mirrors src/; run via TestRunner.runAll()

Numeric prefixes control load order — Apps Script loads every file into one global scope. Diagrams live in docs/ (.mermaid); design docs in docs/plans/.

Conventions worth knowing before you write code — repository-only DB access, transactional writes, UUIDv7 ids, JSDoc on every function — are in CLAUDE.md, which is the authoritative contributor guide.

On the contributor guide

CLAUDE.md is written for both human and AI contributors, and doubles as the project's engineering-process record. It carries the architecture rationale, the why behind retired subsystems (so nobody reintroduces them), and an explicit set of hard stops that an unattended run may never take on its own — schema and migration changes, rollback logic, tenancy migration steps. Design decisions are proposed as docs/plans/*.md documents and reviewed before any implementation code is written. If you want to see how the codebase got to its current shape, read that file and docs/plans/ rather than the commit log.

---

Setup

Who this is for: v1 is self-deploy, not Marketplace-listed — there is no install button. Getting from "cloned the repo" to "working add-on" means creating an Apps Script project, pushing source with clasp, deploying it as an add-on, installing it, and running the setup card. That's achievable in well under an hour, but it's developer-flavoured. The intended audience is a team with one technically comfortable person for about 30 minutes — someone who can run a CLI and click through an OAuth consent screen.

The add-on is multi-tenant: a team's spreadsheet is the tenant. There are no Script Properties to set — first run is driven from the UI.

From a fresh clone

There is no npm build step; the source is the deployment. You need clasp and a Google account.

1. Install clasp — npm install -g @google/clasp.

2. Clone the repo:

   git clone https://github.com/vaporwavre/formwork.git
   cd formwork

3. Create the Apps Script project at script.google.com → New project. This is deliberately not clasp create — creating it in the browser lets you name and place it yourself before any source lands in it.

4. Point clasp at it. Open the new project's Project Settings and copy its Script ID. Copy .clasp.json.example to .clasp.json and paste the id in as scriptId:

   cp .clasp.json.example .clasp.json

   .clasp.json is gitignored on purpose — see Local development below.

5. Log in and push with the same Google account that owns the project:

   clasp login
   clasp push

   First push may prompt you to enable the Google Apps Script API for that account at script.google.com/home/usersettings — enable it and re-run clasp push.

6. Verify the push. Open the project at script.google.com — the src/ file tree should now be visible in the editor.

7. Deploy as an add-on: in the editor, Deploy → Test deployments → Install.

8. Open it. The Formwork logo now appears in the side panel in Gmail and Sheets. Click it, then Grant permission and step through the OAuth consent screen (see OAuth scopes below for what each permission is for).

9. Authorizing lands you on the setup card — continue with First run below.

Team access

The person who ran the steps above owns the Apps Script project and (if they chose Create new in the setup card) the database spreadsheet. Neither is shared with the rest of the team by default — the owner must explicitly share both with Edit permission before anyone else can use the add-on:

• The Apps Script project — share it (Editor) with every teammate who will install the add-on. Apps Script add-ons installed via Test deployments are only usable by accounts with at least Editor access to the underlying project; there is no separate "install for viewers" tier at this stage.
• The database spreadsheet — share it (Editor) with the same team. Every repository in 4_infrastructure reads and writes this sheet directly under the installing user's own permissions; a teammate with only Viewer (or no) access will authorize successfully and still fail on every read/write once they reach the setup card or homepage.

Both are ordinary Google Drive shares (Share button, top right, in the Apps Script editor and in Sheets respectively) — nothing Formwork-specific. Do this once, before pointing a second teammate at Connect existing in the setup card below.

Upgrading

The add-on does not update itself. A deployment stays on the revision it was pushed from — there is no channel to ship you a fix. Check Settings → About for the version you are running, then follow CHANGELOG.md → How to upgrade, which records per release whether a migration must be run and whether the OAuth scopes changed (a scope change forces every user to re-authorize).

First run

1. Install/deploy the add-on and open it (Gmail or Sheets sidebar).
2. Unconfigured installs land on the setup card, which offers two paths:
   • Connect existing — paste your team spreadsheet's link or ID.
   • Create new — the add-on creates a fresh spreadsheet for you.
3. Either path writes a per-user pointer, runs initializeCompleteDatabase() to create every sheet and seed the REF/enum data, then drops you on the configured homepage.

Where settings actually live:

Setting	Backing store	Why
Spreadsheet id	Per-user UserProperties pointer (formwork.spreadsheet_id)	Bootstrap — it cannot live in the sheet it locates
Environment (DEV / TEST / PROD) and all other team settings	DB_Settings sheet inside the team spreadsheet	Per-team, not per-deployment

Route all team-shared settings through SettingsStore — never read these keys directly.

Headless setup (Apps Script editor)

Useful for pointing a second blank spreadsheet at the add-on without touching the UI:

setupTenant("<spreadsheetId>", "DEV");   // connect an existing spreadsheet
setupTenantNewSpreadsheet("My Team", "DEV");  // create + connect a new one
Config.validate();                        // verify: pointer, access, environment, timezone

Config.validate() returns a status object with an errors[] array rather than throwing.

Local development

Local editing via clasp from VS Code.

clasp push

.clasp.json is gitignored and absent from a fresh checkout — you must supply your own. Point it at a DEV script id; that file, not the git branch, is the real boundary against writing to the production deployment. Treat it as a credential.

---

Testing

Tests live in src/7_tests/ and mirror src/. Run from the Apps Script editor:

TestRunner.runAll();

Assertions: assertEquals / assertTrue / assertDeepEqual / assertThrows. Register new suites in TestRunner.runAll().

Pure, service-free logic (compilers, mappers, planners, view-models) is unit-tested. Anything touching SpreadsheetApp / DocumentApp / GmailApp is verified with the DEV_* smoke functions plus a manual click-through of the card flow.

---

OAuth scopes

Deliberately kept to sensitive, not restricted scopes — restricted scopes trigger Google's CASA security assessment, which is expensive and recurring. No drive.readonly, no gmail.readonly. This is why Forms, Docs, and spreadsheets are all selected by pasted link/ID rather than a Drive picker, and why Doc blueprints are generated with DocumentApp.create() instead of DriveApp.makeCopy(). Adding a scope re-triggers OAuth verification — treat it as a release-level decision.

Every scope below is declared in appsscript.json and is exactly what a deployer's consent screen will list when they authorize the add-on:

Scope	What it's for	Where it's used
gmail.addons.execute	Lets the add-on run as a Gmail add-on surface (the Gmail sidebar homepage card)	addOns.gmail.homepageTrigger in appsscript.json
gmail.send	Sends the per-agent schedule email	(v1-remaining "operate" feature — see Roadmap)
script.scriptapp	Creates/manages the hourly and daily time-based triggers behind Alerts	AlertService, LicenseAlertService
spreadsheets	Read/write access to the team's spreadsheet — this is the database	every repository in 4_infrastructure
documents	Creates and fills output Docs from DocTemplate blueprints	DocTemplateRenderer, DocTemplateBlueprints
forms	Reads a Form's structure to compile it into a wizard	FormCardCompiler
script.storage	Backs PropertiesService (Script/User Properties, e.g. SettingsStore, tenant config) and CacheService (in-flight wizard state, FormWizardState)	SettingsStore, FormWizardState, CacheKeysRegistry

None of these are restricted scopes, so a self-deployer authorizing their own Apps Script project does not trigger CASA — that only applies to a public Marketplace listing.

---

Roadmap

Milestone detail lives in docs/plans/marketplace-release-roadmap.md; the v1 bar is defined in docs/plans/v1-done-scope.md.

Done — FormCompiler · DocTemplate · packaged wizards & Doc blueprints · generic form→entity decomposition · submission lifecycle, search & edit · multi-tenancy + first-run onboarding · rosters · scheduled alerts · dashboard.

v1 remaining — the operate half: a schedule you can see, change, trust, and send (per-agent email), plus bulk agent import and Marketplace submission mechanics.

Deferred — AI-assisted intake (LLM entity extraction from email bodies) is not built, and is held back deliberately: it would add a restricted OAuth scope.

---

License

MIT. The add-on makes no external API calls — all data stays inside the Workspace of whoever installs it.