Sheets
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
keyToRowindexes diverge.
Installation
npm install @walkeros/server-store-sheets- Integrated
- Bundled
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',
},
},
},
},
});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) |
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
{
"operation": "get",
"key": "alice"
}get("alice", "{ tier: \"gold\" }")Write with service account
Append or update a JSON value in the configured sheet using an explicit service account JSON
{
"operation": "set",
"key": "bob",
"value": {
"tier": "silver"
}
}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
failedwith codetransport.
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.
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
| 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=spreadsheetIdand throws an actionable error on 404. - Header write: when
setup.headersis provided, setup issuesPUT /values/<sheet>!A1:<lastCol>1?valueInputOption=RAWwith 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'); // voidEach 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.
setthen 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.storetarget): 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.