Skip to main content
Ask your AI

Sheets

Server Source code Package

Google Sheets store with zero runtime dependencies. It 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.

It is designed for demos and small-scale prototyping where the spreadsheet itself is the operator-facing UI for tweaking lookup data, and is 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: every read and write is an HTTP round trip to Google.
  • No internal cache: the package does not cache values; it keeps only the key-to-row index. Caching is the consumer's responsibility, see Wiring with the built-in 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.

Installation​

npm install @walkeros/server-store-sheets
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',
        },
      },
    },
  },
});

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​

PropertyTypeDescriptionMore
id*stringSpreadsheet ID, the segment between /d/ and /edit in the URL
sheetstringSheet (tab) name within the spreadsheet
keystringColumn letter for keys (the lookup column)
valuestringColumn letter for values (JSON-serialized blob)
headerRowsintegerNumber of header rows to skip when reading the key column
credentialsstring | objectService 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\" }")

When it fails

  • failed read: The request fails (no connection, a refused or forbidden read): get throws a StepError instead of answering a miss, and the collector reports it. Reported as failed with code transport.

Wiring with the built-in cache​

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

{
  "stores": {
    "crm": {
      "package": "@walkeros/server-store-sheets",
      "config": {
        "settings": {
          "id": "1AbCdEfGhIjKlMnOpQrStUvWxYz",
          "sheet": "Customers"
        }
      },
      "cache": { "rules": [{ "ttl": 60 }] }
    }
  },
  "transformers": {
    "crmLookup": {
      "code": {
        "type": "crm-lookup",
        "push": "$code:async (event, context) => { const userId = event.user?.id; if (!userId) return; const crm = await context.env.store.get(userId); if (crm) return { event: { ...event, data: { ...event.data, crm } } }; }"
      },
      "env": { "store": "$store.crm" }
    }
  }
}

The consumer here is an inline code transformer; any transformer or destination that reads its env.store works the same way. ttl is in seconds. Use a short ttl such as 5 for demos (fast iteration), 60 or higher for production-ish loads. Without a cache, every read is one Sheets request, so more than one read per second exceeds the 60 per minute quota.

The cache options (rules, ttl, multi-tier chains) are on Cache.

Provisioning​

The package ships an idempotent setup() lifecycle, invoked only by the explicit operator command, on a store with config.setup set (true or an object):

walkeros setup store.<id>

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

caution

Today the command stops before this setup runs, with Package @walkeros/server-store-sheets has no default export: walkeros setup reads setup from the package's default export, and this package's default export is its init function.

Setup options​

OptionTypeDefaultNotes
headersstring[](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: setup does not share the spreadsheet; that needs the Drive API and its own OAuth scope. Share the spreadsheet manually with the service account email before running setup.

Runtime hard-fail​

The first operation (get, set or delete) issues a single GET /spreadsheets/<spreadsheetId>?fields=spreadsheetId per process per spreadsheet ID. On 404 it throws with an actionable message:

Spreadsheet not found: <spreadsheetId>. Run "walkeros setup store.<storeId>" 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 $secret. reference. The store signs JWTs locally using node:crypto and exchanges them for access tokens via Google's OAuth2 endpoint.

"config": {
  "credentials": "$secret.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). When it is missing, the flow stops at startup with required secret "SHEETS_SA_KEY" is not set. A credentials value that is not valid JSON, or lacks client_email and private_key, is ignored without a log line, and the store uses ADC instead. 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 blank cell is a miss; a cell that does not parse fails the read with Sheets value could not be decoded.

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 not supported.
  • 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. The key column is read once per process, at the first operation. set then writes one cell, or appends a row for a key that index does not hold, so a row another process appended later is not seen.
  • Not a cache tier. The Sheets store is a key-value state store. Do not use it as a cache tier (a cache.store target): nothing rejects it, but every cache read and write then costs a Sheets request. A cache can still wrap it. Its cells hold structured JSON only and a binary value is rejected. Cache structured or binary HTTP responses in a filesystem, S3, GCS, or in-memory store instead.
💡 Need implementation support?
elbwalker offers hands-on support: setup review, measurement planning, destination mapping, and live troubleshooting. Book a 2-hour session (€399)