Open package documentation · version 0.1.0

Orders to Google Sheets

Readiness: built. Local integration evidence does not mean production or customer-account verification. Purchases are currently disabled.

Setup and configuration

Supported profile: one self-hosted n8n 2.41.6 production process with N8N_CONCURRENCY_PRODUCTION_LIMIT=1, Node 24.21.0, standard WooCommerce 11.1.2 REST v3 resources on WordPress 7.1.2. Local integration used PHP 8.2.12 and HPOS. Cloud, queue/multiple workers, custom statuses, inventory extensions, subscriptions and multisite have not been verified. Test other versions before enabling them.

• Back up your n8n database and encryption key. Create a dedicated read-only WooCommerce REST key for one store. Store its consumer key/secret in an n8n HTTP Basic Auth credential; use HTTPS and never put it in a URL or Code node.
• Import workflow.json, state-bootstrap.json and health-check.json. They start inactive. Do not import duplicate schedules. Check that all nodes resolve to built-in types with no missing dependencies.
• Run Preview (sample only). It uses labelled synthetic data and performs no HTTP, SMTP, Google or persistent-state writes. Inspect Sample preview output.
• Run the separate Run setup once bootstrap in the n8n editor. It creates/reuses a product-specific Data Table with string columns key and value. Copy the table ID. Do not repeatedly create tables or use the workflow CLI for Data Table execution: the tested CLI did not initialize this module.
• In Production configuration edit only the JSON config values: a unique stable storeKey, HTTPS storeUrl without query/credentials, the table ID, your IANA timezone and product-specific rules. Keep storeKey stable across restarts and updates. Match Workflow Settings → Timezone to config.timezone; daily scheduling and report calendar must agree. Set your sender and one owner recipient for email workflows, then select SMTP credentials in Send owner email.
• Select the read-only Basic Auth credential in Read WooCommerce page. Configure the selected Google credential for WCA-004 as described below. Production configuration intentionally refuses a manual production run; manual runs are not protected by n8n's production queue. Use sample preview in the editor. For integration acceptance, use an isolated controlled test store and one active scheduled copy under the serialized production process. Inspect its first scheduled result, error paths and second ordinary run. Do not disable the manual gate on a customer's running instance.
• Enable productionEnabled only after values, credentials, bootstrap, volume and output have been checked. Activate exactly one schedule. Verify its first result before leaving it unattended. Run health-check.json manually with the same storeKey and table ID to inspect last confirmed success.

Default hard limits: maxEntities 5000 across fetched resources, maxRequests 200 including retries (configurable 1–1000); HTTP timeout 30 seconds; execution timeout 1800 seconds. These are stop limits, not a claim that 5000 entities were integration-tested. Actual fixture volumes are in QA.md. Estimate product, variation and order requests before activation. Exceeding a limit fails explicitly without a partial report or checkpoint.

Woo requests use 100-item pages and validate total/page headers, exact page lengths and duplicate IDs. A changed total during pagination aborts the run. Pagination is not a transactional store snapshot: a store that changes while fetching needs a later complete run. UTC API boundaries omit the Z suffix under dates_are_gmt=true to avoid a second WordPress timezone conversion; reports still use UTC instants and IANA calendar boundaries.

429, 5xx and transport failures receive at most four attempts (2/4/8-second base backoff), respecting numeric or HTTP-date Retry-After up to 120 seconds. Longer or malformed advice stops the run. 401/403 stop immediately. Never treat incomplete or inaccessible data as zero.

Use a dedicated operational recipient. Reports omit customer names, addresses, email, billing/shipping and payment metadata. Product names, order numbers and administrator links may still be confidential. Restrict n8n, its database/backups, the mailbox and the selected Sheet to the relevant team.

Orders to Google Sheets rules and template

This build has native n8n execution and mocked Google values-protocol checks, but real Google OAuth consent/refresh and the actual Google API are unverified. Keep production disabled until the selected account/Sheet has passed the acceptance checklist. No live Google Sheet was created or modified during package preparation.

Import sheet-template.xlsx into Google Sheets using File → Import, or import the CSV. Use the Orders tab (or set sheetTab to its exact name), keep row 1 exactly as supplied and ensure the grid has enough rows for the initial window and expected growth (up to maxEntities + header). The XLSX is blank; purple A:L are managed and green M:O are staff fields. Do not insert a title, rename/move headers, delete managed rows or use another writer for them while the schedule runs. The source code reads the whole selected tab so extra headers/columns are detected.

Managed headers: store_key, order_id, order_number, date_created, date_modified, status, currency, order_total, item_count, items_summary, order_admin_url, last_synced_at. Staff headers: staff_note, owner, follow_up. Dates are UTC ISO text; identifiers, totals and counts are RAW strings (decimal currency values remain exact). Names starting =, +, – or @ remain literal text under valueInputOption=RAW. There are no customer names/email/address or payment fields.

Create a Google Sheets OAuth2 credential in your own n8n. For this direct values-API workflow use Custom Scopes with https://www.googleapis.com/auth/spreadsheets, enable the Sheets API, configure the OAuth redirect shown by n8n and grant the account access to the selected Sheet. Do not copy the broad default Drive scopes without reviewing them. Scope authorizes spreadsheets the account can access; this workflow selects one sheet ID, but the credential is not technically restricted to a single file. Use a dedicated account where appropriate. Select this credential in both Read Sheet schema and keys and Write managed fields RAW; no token is embedded in the export.

Config: sheetId, sheetTab Orders, initialDays 30 (1–365), overlapMinutes 10 (1–60). First fetch uses the recent created-date window. Later fetches use modified-after watermark minus overlap through fixed fetchEnd. An old order modified recently can enter the sheet. Keys are store_key + order_id; duplicate/missing existing keys or a changed schema stop before writes. Existing keys update A:L at their fixed rows; new keys append A:L after the last occupied row. M:O never enter a write range. Do not sort, insert or remove rows while a run is active. The whole sheet, including rows from other stores, must fit maxEntities.

Checkpoint advances only after all expected rows are confirmed by totalUpdatedRows. Orders outside the initial created window that never change will not be backfilled. Deleted orders are not automatically removed from the Sheet. Order totals are an operations view, not accounting. Real acceptance must verify access revoked, quota, partial/uncertain write, modified old order, repeated run, two queued production runs, notes/formula-like names and a cold restart using the actual selected Sheet.

Stop, diagnose and recover

Deactivate the production workflow first. Keep its Data Table and credentials. Wait for running and queued executions to finish before restarting, changing rules or importing an update. A native Data Table lookup/upsert is not an atomic lock: serialization relies on the tested single production process and global concurrency limit of 1. Manual production and additional instances/copies are unsupported.

Inspect the failing node, credential permissions, complete page headers and limits. Revoke/rotate compromised credentials in their owner service and reconnect them in n8n. Use sample preview without effects while diagnosing transforms. Do not export credentials with a workflow.

State is written after a complete selection and a confirmed result. An ordinary successful quiet run also updates lastSuccess; no alert can be normal. Health check returns never_completed, fresh or stale using a configurable maxGapHours (twice the default interval plus 30 minutes). It is an inspector, not an independent uptime monitor, and sends no email. An unavailable n8n instance cannot run its own health check.

For email, SMTP acceptance is not proof of delivery to an inbox. A confirmed rejection leaves state unchanged. If SMTP accepted a message but state commit failed, or acceptance was uncertain, check transport logs/mailbox and state before a manual resend. This distributed boundary cannot guarantee exactly-once delivery. Automatic SMTP retry is deliberately absent. WCA-001 forceResend is an explicit controlled resend; revert it after use. Never erase the whole table to request one message.

For Sheets, validate schema, permissions and composite keys before retrying. Managed RAW writes retry identical fixed A:L ranges at most four attempts. A partially applied batch or unresolved timeout does not advance the watermark. Inspect the actual Sheet first; stop other writers, repair duplicates and retry under the same serialization. Never delete staff notes or reset watermark merely to hide an error. An overlap can legitimately pick up an old order changed recently.

Database restore requires the matching encryption key. After restore keep schedules inactive, inspect state and run one controlled acceptance cycle. Restoring an older checkpoint can resend/rewrite already accepted output; reconcile it before activation. Data Tables are retained when a schedule is disabled. Delete them only after export and an intentional decision to discard recovery history.

Daily sent markers and resolved exception records expire after 30 days during successful processing. Stock keeps the latest complete filtered catalog snapshot. The Sheets watermark persists until explicitly replaced. These are workflow-state policies, not a promise that all external logs/emails/backups are deleted. Exported workflows save no successful/error/manual execution data; configure your instance defaults and pruning separately (the local profile uses 168 hours). Fixture QA copies recorded synthetic executions and are not release workflows.

Updates: deactivate, back up database/key and export the current workflow; compare CHANGELOG.md and schema version; import without creating a second active schedule; reselect credentials/table ID, retain stable storeKey and inspect sample/controlled tests. This version accepts state schema 1. A future schema change needs a documented migration; no silent reset is supported.

Acceptance before enabling a store

• Verify archive checksum, built-in nodes and inactive clean import on the intended version.
• Run the sample branch and confirm no network/output/state mutations.
• Bootstrap explicitly; select dedicated credentials, stable store key, IANA timezone and one serialized production runner.
• Inspect a controlled scheduled first result and ordinary repeat; confirm expected pages and volume without truncation.
• Revoke access or inject a controlled second-page failure; confirm no output/checkpoint and recovery after a complete retry.
• Test product-specific boundaries/rules from CONFIGURATION.md (including recovery/reopened cases or schema/human columns).
• Stop/restart the actual runtime with the same database and encryption key; confirm persistent state and no repeat alert/duplicate row.
• Check lastSuccess/health on a quiet successful run, safe stop, backup/restore and credential revocation procedure.
• For email verify acceptance and actual inbox delivery with your chosen SMTP provider. For Sheets verify the actual Google account/API, not only a protocol fixture.

Record versions, account/transport scope, volume, timezones, result and unresolved failures. Do not activate on unresolved data-loss, duplicate-output or access failures. Do not claim this checklist has run on a customer's environment merely because local QA passed.

Actual QA and boundaries · WCA-004

Readiness: built. Tested locally on n8n 2.41.6 / Node 24.21.0, WooCommerce 11.1.2 / WordPress 7.1.2 / PHP 8.2.12, HPOS, store timezone Europe/Kyiv and report timezone Europe/London. Native imports and synthetic previews used built-in nodes; Data Tables executed through the running server. Pure transform tests cover 300 orders, 250 stock entities, DST 23/25-hour days, currency/refund semantics, state cycles, escaping and RAW mapping. Pure pagination/retry tests are fixtures, not live API evidence.

Native integration used actual loopback WooCommerce HTTPS REST, read-only credentials, native n8n Data Tables and a synthetic loopback SMTP sink. The fixture contained 309 owned synthetic orders and 250 stock entities (plus the five catalog products). Daily report fetched four pages and included the midnight start/excluded the next; stock fetched 13 pages and reported 125 initial low entities; order review fetched active/failed/tracked records and physical metadata. The real SMTP test covered accepted/rejected transport responses, repeat suppression and recovery. It does not demonstrate an external inbox. Two simultaneous local production webhook QA runs under actual n8n concurrency=1 produced one daily email. Stock recovery and order resolution/reopening were executed against actual owned fixture records. Cold process restart preserved native state without repeated owner emails.

WCA-004 additionally ran native nodes against actual Woo data and an explicitly mocked Google values protocol: exact schema, OAuth bearer transport with a synthetic token, RAW managed ranges, human columns, duplicate/schema/access failures, 429 retry, unconfirmed row counts and checkpoint retention. No real Google consent, token refresh, Google quota service, selected account permissions or live Sheets race/restart was verified. WCA-004 remains built; no saleable compatibility claim is made for it.

Production deployment, external SMTP/inbox, gateway transactions, live Google, customer runtimes, other versions, custom plugins and 5000-entity workload have not been tested. JSON evidence in qa-evidence/ distinguishes pure fixtures, native local integration, deliberate fault proxies and mocked Google. No production-tested claim is made. Instance pruning is configured; seven-day elapsed retention was not simulated.

Discuss your setup