Paid Shopify orders → warehouse spreadsheets, with no manual entry

Normalization on the Python side

Two stores, two books, and the same product spelled differently in each one — years of accumulated habit. So the name maps are per-spreadsheet: each book has its own dictionary, editable without touching code. The same layer resolves the source warehouse and untangles the non-trivial states: a partial refund and a cancellation are separate states, and they look different in the sheet.

Advanced Sheets Service instead of SpreadsheetApp

The first receiver was written against the familiar SpreadsheetApp object model. On books this size it reliably ran out of memory. The receiver was rewritten on top of Advanced Sheets Service: batched Sheets API requests instead of walking cells. The receiver has since reached version 87 — nearly every release closed off another undocumented Google behaviour.

Mid-grid insertion and formula fill-down

An order has to land in its own place in the sheet — where the person expects it, according to the row order they've built up. Google only extends neighbouring formulas when you append at the end; insert in the middle and the row arrives bare. So the receiver fills formulas down itself, colours cancelled orders through conditional formatting, and grows the row reserve ahead of time so a run never hits the end of the grid.

The cancellation colouring has a story of its own. The conditional-formatting rule was deployed in the book and never once fired: the main module was never passing the cancellation flag through to the receiver, so there was nothing to colour. We sized the gap against the stock-ledger book — across 16,478 rows, the missing flag turned up on exactly one. The flag now arrives, the rule lights up, and the sentence above is true.

Four guards that keep the bot clear of human work

Getting data into a live book is half the job. The other half is making sure the bot leaves a person's own work untouched under every scenario. That's four independent guards, each closing off one way to do damage:

• Write only over an untouched formula. Shipping cost goes into a cell whose original formula is still intact. A cell where someone typed a value in by hand is left alone.
• Read the target rows before writing a batch. If anything at all is sitting there, the whole write is cancelled with a loud refusal that names the row numbers, and the orders catch up on the next run. The trigger was a live incident: a second operator typed a product into a row with no order number, and 13 minutes later the bot wrote its own into it.
• Anchor the batch at the end of the first unbroken run. One row that had slipped used to drag every following order down with it; the drift now stays local.
• Reject an unknown action name outright. It used to fall through into the append branch, so a typo would have written row numbers into the book as orders while the receiver answered "success". Before the guard went in, all 19 action values the main module sends were checked off against it.

The live check after the release took three calls: a typo in the action name comes back as an error, an empty append answers "0 added", and a real action runs normally.

The reverse direction: book-to-store reconciliation, read-only

The same stock ledger is now reconciled against the stores daily. The range export is strictly read-only, and two guard tests hold it there by scanning the module's own source for write functions — there are none. For a pipeline that writes into live working spreadsheets, that check is a direct extension of the write-safety story. Live run: 701 rows, divergences within threshold.

Collapsing overlapping validation rules

A separate discovery: inserting rows multiplies dropdown-list validation rules until they start overlapping. Left to accumulate, they eventually push the book into a state where it simply won't open. The receiver collapses overlapping rule ranges into one — that is the condition for the spreadsheet staying usable at all.

Operations layer: eight timers

The pipeline runs unattended, so it's wrapped in an operations layer built on systemd timers:

• Posting — every 30 minutes, new and changed orders
• Day-file export — the check grid is down to 5 minutes, and the wait for the file went from roughly 50 minutes to 15
• Inbound files from the fulfilment partner — every 15 minutes within a 07:00–22:00 MSK window, picked up from both the group topic and direct messages
• Name-catalogue rebuild — around 187 successful builds a day across two circuits
• Healthcheck — is the loop alive, are writes actually landing
• Daily reconciliation — row-by-row comparison of the book against Shopify
• Nightly backup — a snapshot of the books before the next day's writes
• Receiver test run — against the real environment

The day-file speed-up deserves a note. Alongside the five-minute grid there's a chat command that builds the snapshot on demand, and the read load was measured first: about 380 reads a day, which leaves plenty of headroom against the quotas. This project's whole loop lives on a server that currently runs 58 timers with a daily state check across all of them.

On top of that: Telegram notifications to the operator, retries around flaky Apps Script executions, and a dedicated test that fails when the business-rules reference drifts away from the code. That last one sounds minor, but it's exactly what keeps documentation from turning into fiction.

Self-healing that yields to the human

The lost-record recovery could put rows back into the book, and it put them back in cases where a person had deleted the row on purpose. Replaying it against live snapshots gave 67 orders out of 80 restores in a single day: in 84% of its firings the bot was arguing with a human.

There's now a registry of base order numbers the system has seen: seeded from 14 nightly snapshots, holding 7,696 numbers and topped up by every reconciliation run. Before restoring a row, the bot can tell a human-deleted row from one that was never written. An empty registry defaults to "haven't seen it", so a lost file leaves recovery switched on.

Replaying the same data with the registry in place: 67 restores would not have happened, and 13 genuine missed writes still would. The safety net is fully intact, with the human's decision taking precedence.

What it took to check the watchdogs

An operations loop is worth exactly as much as your proof that its signal reaches a human. Auditing the watchdogs turned up three findings.

• One of the two stores failing quietly pulled the count of over-promised stock down from 9 to 3. The number read as good news, and it's a number people buy stock against; anything listed only in the store that was down dropped out of the sample entirely.
• The stuck-order watchdog on the workstation was dead: there's no ssh agent under the system scheduler, so it reported "the source returned no orders, staying quiet" in the very same words a healthy watchdog uses when it has nothing to report. Run by hand, the same code found genuinely stuck orders, 5 and 11 days old. Cured by moving it to the server.
• The stock-ledger reconciliation monitor ran faithfully every four hours and wrote a file that not one person ever opened.

What a year in production showed

The loop is built, and it's alive. A year of running it produced three failures worth telling, because every one of them was silent.

The daily sync job reported failure on success. The deploy output was piped into a string search, and with pipefail on, the exit code comes from the last failing command in the chain. An empty search returns one at precisely the moment the system says "no changes": the quieter the day, the more reliably the alarm went off. It burned silently for days, until people stopped reading it. The fix: take the exit code from the deploy command itself, write the output to a file and parse it afterwards, print the "nothing changed" case in words and count it as success, and log the tail of the output when something really does fail.

The nightly backup failed every single night on the dump steps for a service that had been muted. A real backup failure would have drowned in that daily wall of red. Those steps are now skipped based on the containers being absent, and switching the service back on will need no edits.

Deployment by checksum manifest. For code that writes into production spreadsheets, a deploy target simply didn't exist: it was moved by hand, and a build from two weeks earlier sat unnoticed on the server, missing the machine-detection fix. There's a target now: rsync without deletes — the environment, daily files and manual backup copies live next to the code — its own manifest file, and 62 files verified by full checksum comparison. Before the target went in, the direction of drift was checked file by file: there are no lines anywhere that exist on the server and are missing from the repository — otherwise the deploy would have quietly wiped a manual edit. On a later deploy, the pre-upload comparison came back with 107 matching files.

Cutover as a "second circuit"

Letting a bot write into a spreadsheet two people depend on daily is a scary move — and it should be. The cutover ran as a parallel circuit: for a while the bot wrote into the production books simultaneously with reference copies, reconciliation caught the divergences, and rollback meant switching off a single timer. There was never a "big bang" moment.