Sheets

Server Source code Package

Google Sheets store with zero runtime dependencies. Uses raw fetch against the Sheets v4 REST API with built-in auth: Application Default Credentials (ADC) on Cloud Run / GKE, or explicit service account JWT for non-GCP environments.

Designed for demos and small-scale prototyping where the spreadsheet itself is the operator-facing UI for tweaking lookup data. Not a production CRM substitute.

Caveats and quotas (read first)

The Sheets API is rate-limited and slow. Wiring this store directly into a high-throughput pipeline burns quota in seconds.

• Quota: 60 read requests / minute / user / project, 60 write requests / minute / user / project.
• Latency: 200 to 800 ms per HTTP round-trip.
• No internal cache: the package does NOT cache reads. Caching is the consumer's responsibility, see "Wiring with the core cache" below.
• Concurrency: last writer wins on the same cell. There is no transactional getAndSet.
• Single-writer model: if two pipeline instances both write to the same sheet, their keyToRow indexes diverge.
• Demo and small-prototype grade only. Not a production CRM substitute.

Installation

npm install @walkeros/server-store-sheets

Integrated

import { startFlow } from '@walkeros/collector';
import { storeSheetsInit } from '@walkeros/server-store-sheets';

await startFlow({
stores: {
  crm: {
    code: storeSheetsInit,
    config: {
      // Omit config.credentials for ADC on Cloud Run/GKE
      settings: {
        id: '1AbCdEfGhIjKlMnOpQrStUvWxYz',
        sheet: 'Customers',
      },
    },
  },
},
});

Bundled

Add to your flow.json:

"stores": {
"crm": {
  "package": "@walkeros/server-store-sheets",
  "config": {
    "settings": {
      "id": "1AbCdEfGhIjKlMnOpQrStUvWxYz",
      "sheet": "Customers"
    }
  }
}
}

Configuration

This store uses the standard store config wrapper (consent, data, env, id, ...). For the shared fields see store configuration. Package-specific fields live under config.settings and are listed below.

Settings

Property	Type	Description	More
id*	string	Spreadsheet ID, the segment between /d/ and /edit in the URL
sheet	string	Sheet (tab) name within the spreadsheet
key	string	Column letter for keys (the lookup column)
value	string	Column letter for values (JSON-serialized blob)
headerRows	integer	Number of header rows to skip when reading the key column
credentials	string | object	Service account JSON (string or object). Omit for ADC on Cloud Run/GKE (deprecated: use config.credentials)

* Required fields

Mapping

This package does not define custom rule-level settings. For the standard rule fields (consent, condition, data, batch, name, policy) see mapping.

Examples

Read with ADC

Read a value from the Sheets store using ADC, no credentials field needed on Cloud Run or GKE

Event

{
  "operation": "get",
  "key": "alice"
}

Out

get("alice", "{ tier: \"gold\" }")

Write with service account

Append or update a JSON value in the configured sheet using an explicit service account JSON

Event

{
  "operation": "set",
  "key": "bob",
  "value": {
    "tier": "silver"
  }
}

Out

set("bob", "{ tier: \"silver\" }")

Wiring with the built-in cache

To absorb the Sheets quota, wire the store with the built-in cache via Flow.Store.cache. The cache is on by default with an in-memory tier and requires no extra store declaration:

{
"stores": {
  "crm": {
    "package": "@walkeros/server-store-sheets",
    "config": {
      "settings": {
        "id": "1AbCdEfGhIjKlMnOpQrStUvWxYz",
        "sheet": "Customers"
      }
    },
    "cache": { "rules": [{ "ttl": 60 }] }
  }
},
"transformers": {
  "enrich": {
    "package": "@walkeros/transformer-enrich",
    "env": { "store": "$store.crm" }
  }
}
}

Use ttl: 5000 for demos (fast iteration), ttl: 60000 or higher for production-ish loads. Without a cache, every event hits Sheets directly and trips the 60 req/min quota in under one second.

The cache config lives at packages/core/src/types/cache.ts. See the understanding-stores skill for the broader store/cache pattern.

Provisioning

The package ships an idempotent setup() lifecycle, invoked only by the explicit operator command:

walkeros setup store.<id>

It never runs automatically. It verifies the spreadsheet exists and (if configured) writes the setup.headers row.

Setup options

Option	Type	Default	Notes
headers	string[]	(none)	Header values written to row 1 of the configured sheet. Idempotent overwrite, no drift detect.

id is taken from settings.id and is NOT duplicated under setup.

Behavior

• Existence probe: setup issues GET /spreadsheets/<id>?fields=spreadsheetId and throws an actionable error on 404.
• Header write: when setup.headers is provided, setup issues PUT /values/<sheet>!A1:<lastCol>1?valueInputOption=RAW with the headers as the row values. Re-running with the same headers is a no-op overwrite.
• No shareWith: Drive API integration is intentionally out of scope in this version (it requires a separate OAuth scope). Share the spreadsheet manually with the service account email before running setup.

Runtime hard-fail

The first call to init() issues a single GET /spreadsheets/<id>?fields=spreadsheetId per process per spreadsheet ID. On 404 it throws with an actionable message:

Spreadsheet not found: <id>. Run "walkeros setup store.<id>" to ensure the sheet exists and is shared with the service account.

Operators see the error pointing at the exact command to fix it. Subsequent calls in the same process skip the check via an in-memory cache.

Authentication

Cloud Run / GKE (ADC)

When running on GCP infrastructure, omit credentials. The store fetches access tokens from the metadata server automatically. Required OAuth scope: https://www.googleapis.com/auth/spreadsheets.

Non-GCP (service account)

Pass a service account JSON via config.credentials with a $env. reference. The store signs JWTs locally using node:crypto and exchanges them for access tokens via Google's OAuth2 endpoint.

"config": {
"credentials": "$env.SHEETS_SA_KEY",
"settings": { "id": "...", "sheet": "Customers" }
}

The SHEETS_SA_KEY environment variable should contain the full service account JSON (with client_email and private_key fields). Share the spreadsheet with the service account email before running setup.

Structured only

The Sheets store is structured-only. Cells hold structured StoreValue JSON; there is no byte-native mode. Setting file: true is a hard error at init (use fs, S3, or GCS for byte-exact serving), and a value containing a binary (Uint8Array) leaf is rejected, since a spreadsheet cell cannot hold raw bytes.

API

const value = await store.get('alice');           // StoreValue | undefined (parsed JSON)
await store.set('bob', { tier: 'silver' });        // void
await store.delete('charlie');                     // void

Each value is JSON-stringified into one cell (the value column). Reads JSON-parse the cell back. A non-parseable cell logs a debug line and returns undefined.

set() for an unknown key appends a new row, capturing the row index from the API response. set() for a known key updates the existing value cell. delete() blanks the value cell, the row stays in place to keep keyToRow indexes stable.

Limitations

• Single-cell value shape. Multi-column structured rows are out of scope, ship a richer schema in a later phase if customers ask.
• No drift detection on header content. If an operator manually edits row 1, the next walkeros setup store.<id> overwrites it without warning.
• No transactional updates. set is two HTTP calls (read index, write cell). Concurrent writers can interleave.
• Not a request-cache backend. The Sheets store is a key-value state store, not a request-cache backend. It stores a single JSON cell per key and cannot persist the encoded request-cache value, so wiring it as a request cache throws. Cache structured or binary HTTP responses in a filesystem, S3, GCS, or in-memory store instead.