sheets-deployer

DDL provisioner for Google Sheets — treats spreadsheets as databases with tables, schemas, and migrations.

What it does

• Creates Google Drive folders and spreadsheets as database containers
• Provisions tables (sheets) with typed column headers from a schema definition
• Builds and pushes Google Apps Script code via clasp
• Per-project Google account credentials with environment isolation
• Configurable library bundling (logger, error handler, response helper)
• Schema drift detection and sync

Install

npm install -g sheets-deployer
# or
npx sheets-deployer <command>

Requirements

• Node.js >= 18
• @google/clasp >= 2.4.0

Quick start

# 1. Init a new project from a template
sheets-deployer init --db my-shop --template e-commerce

# 2. Set your GAS script ID in dbs/my-shop/db.config.json

# 3. Login (stores creds per project)
sheets-deployer login --db my-shop

# 4. Build + push to Google Apps Script
sheets-deployer push --db my-shop

# 5. Create Drive folder + spreadsheets
sheets-deployer create --db my-shop

# 6. Provision tables from schema
sheets-deployer provision --db my-shop

Commands

| Command | Description | |---------|-------------| | init | Initialize a new DB project from a template | | build | Build GAS output for a DB project | | push | Build + push to Google Apps Script | | deploy | Build + push + deploy as web app | | clean | Clean build output | | create | Create Drive folder + system/data spreadsheets | | provision | Provision tables from schema.ts | | show | List tables in data spreadsheet | | describe | Describe a table's columns | | drop | Drop a table | | sync | Detect drift between schema and live sheets | | status | Show tables with status info | | set-key | Set the DDL API key | | rotate-key | Generate + set a random API key | | refresh-cache | Refresh the __sys__tables__ cache | | setup-trigger | Set up time-based cache refresh trigger | | remove-trigger | Remove cache refresh trigger | | login | Login to Google for a DB project | | whoami | Check current Google account |

All commands accept --db <name> and most accept --env <env> (defaults to dev).

Templates

Start from a pre-built schema:

| Template | Tables | Use case | |----------|--------|----------| | blank | 1 | Minimal starter | | blog-cms | 8 | Content management (posts, categories, tags, media) | | crm | — | Customer relationship management | | e-commerce | — | Products, orders, customers | | inventory | — | Stock tracking |

sheets-deployer init --db my-app --template blog-cms
sheets-deployer init --db my-app --template blank --minimal   # core libs only
sheets-deployer init --db my-app --template crm --libs gaslogger,gaserror  # pick specific libs

Authentication

Each DB project can have its own Google account credentials, preventing accidental pushes to the wrong environment.

# Login stores creds in dbs/<name>/.clasprc.json
sheets-deployer login --db my-app

# Check who's logged in
sheets-deployer whoami --db my-app

# Use global ~/.clasprc.json instead
sheets-deployer push --db my-app --inherit

# Skip account validation
sheets-deployer push --db my-app --force

Account enforcement: if db.config.json has an account field per environment, push/deploy will block on mismatch.

Project structure

dbs/
  my-app/
    db.config.json     # Project config (scriptId, env settings, lib selection)
    schema.ts          # Table definitions
    .clasprc.json      # Per-project Google credentials (gitignored)
dist/
  my-app/              # Built GAS output (gitignored)

db.config.json

{
  "name": "my-app",
  "libs": ["gaslogger", "gaserror", "gas_response_helper", "spreadsheets_db", "db_ddl"],
  "settings": { "loggingVerbosity": 2 },
  "environments": {
    "dev": {
      "scriptId": "YOUR_SCRIPT_ID",
      "account": "[email protected]",
      "driveFolderId": "",
      "systemSpreadsheetId": "",
      "spreadsheetIds": [],
      "deploymentId": ""
    },
    "prod": { ... }
  },
  "activeEnv": "dev"
}

Libraries

The build bundles libraries into numbered .gs files for Google Apps Script.

Core (always required):

• spreadsheets_db — Database abstraction layer for Google Sheets
• db_ddl — Schema provisioning (DDL) operations

Optional (from gas-common-libs):

• gaslogger — Structured logging with verbosity levels
• gaserror — Error wrapping with context, trace IDs, and stack chains
• gas_response_helper — Standardized API response formatting

Use --minimal to skip optional libs, or --libs to pick specific ones.

Environment defaults

Create .env.dev or .env.prod at the project root for shared defaults:

[email protected]
DRIVE_ROOT_FOLDER_ID=1abc...
DEFAULT_LOGGING_VERBOSITY=2

These are used as fallbacks when initializing new DB projects.

Build pipeline

The build concatenates everything into numbered .gs files:

000-098  Library dependencies (from libs/ and common/)
099      Config template (env, scriptId, logging level)
100      Schema bundle (schema.ts → stripped of TS types)
101+     API source files (src/api/*.ts → stripped of TS + imports)

Plus appsscript.json and .clasp.json manifests.

License

MIT