Shopify orders land in the warehouse sheets by themselves, with formulas and manual edits intact

Python brings product names into line

Two stores, two sheets, and the same product is named differently in each, a habit built up over years. So the product name lists are kept separately for each sheet. Each sheet has its own dictionary, and it can be edited without changing the code. The same step works out the shipping warehouse and sorts out the tricky statuses: a partial refund and a cancellation are different states, and they look different in the sheet.

Moving from SpreadsheetApp to Advanced Sheets Service

The first receiver was written with the familiar SpreadsheetApp, the standard way to work with a sheet from Apps Script. On sheets this size it kept running out of memory. I rewrote the receiver on Advanced Sheets Service: it now sends batched requests to the Sheets API and doesn't go through cells one by one. The receiver has reached version 87, and almost every new version dealt with another quirk of Google Sheets.

Inserting rows mid-sheet and copying formulas down

An order has to land in its own place, where the person expects it according to the row order they've built up. When a row is inserted in the middle, it arrives without formulas. The receiver copies the formulas down itself and marks cancelled orders with a colour through conditional formatting. It also adds a reserve of empty rows in advance, so the next run never hits the end of the sheet.

The cancellation colouring has a story of its own. The conditional formatting rule was in the sheet but never once fired: the main module wasn't passing the cancellation mark to the receiver, so there was nothing to colour. I measured how far this went on the stock ledger sheet: across 16,478 rows the mark was missing in exactly one. Now the mark gets through, and cancelled orders really are highlighted.

Four safeguards that keep the bot away from people's work

Getting data into a working sheet is only half the job. The other half is making sure the bot leaves people's work alone. For that there are four independent safeguards, and each closes off one way of doing damage:

• Write only over a formula. Shipping cost is written only into a cell whose original formula is untouched. A cell where someone typed a value by hand is left alone.
• Check the target rows before writing. If there's anything at all in them, the whole write is cancelled. A refusal with the row numbers goes to the log and the chat, and the orders are added on the next run. This came from a real case: a second operator typed a product into a row with no order number, and 13 minutes later the bot wrote its own order into it.
• Insert at the end of the first unbroken block of rows. One row out of place used to drag every following order down with it. Now the shift stays where the error is.
• Refuse an unknown command. An unknown command name used to fall through to the branch that adds rows. A typo would have written row numbers into the sheet as orders while the receiver answered "success". Before switching this safeguard on, I checked all 19 commands the main module sends.

The check after the release took three requests: a command with a typo returns an error, an empty add answers "0 added", and a real command runs as usual.

The other direction: checking the sheet against the stores, read-only

The same stock ledger sheet is now checked against the stores every day. The export is strictly read-only, and two tests on the module's code make sure of it: the module has no function that writes. For a system that writes into people's working sheets, this follows naturally from the safe-writing rules. A check on live data: 701 rows, with differences within the threshold.

Merging overlapping data-validation rules

A separate discovery: when rows are inserted, the rules behind the dropdown lists multiply and start to overlap. Once enough of them pile up, the sheet stops opening. The receiver merges the overlapping rule ranges into one, and the sheet stays usable.

Eight timers around the exchange

The exchange runs unattended, so it's surrounded by scheduled jobs on systemd timers:

• Writing orders: every 30 minutes, new and changed orders
• The day's file for the partner: a check every 5 minutes; the wait for the file went from about 50 minutes to 15
• Files from the shipping partner: every 2 minutes from 07:00 to 22:00 Moscow time, picked up from both the group topic and direct messages
• Rebuilding the product-name list: about 187 successful builds a day
• Health check: is the system running, and are the writes getting through
• Daily check: the sheet compared with Shopify row by row
• Nightly backup: a snapshot of the sheets before the next day's writes
• Receiver tests: run against the real environment

A note on the faster day's file: besides the check every five minutes, there's now a chat command that builds the file straight away. The read load was measured in advance: about 380 requests a day, well within the limits. The whole system for this project runs on a server that currently has 58 timers, with a daily check of their state.

On top of that there are Telegram notifications to the operator, retries when Apps Script fails, and a separate test that fails if the business-rules reference drifts away from the code. The last one sounds minor, but it's exactly what keeps the written rules in line with what the code really does.

Automatic recovery that gives way to people

The recovery module could put lost rows back into the sheet. It also put back rows that a person had deleted on purpose. Replaying it on real snapshots of the sheets showed 67 such orders out of 80 restores in one day: in 84% of cases the bot was overruling a person.

Now the bot keeps a register of the base order numbers it has already seen. The register was built from 14 nightly snapshots, holds 7,696 numbers and is topped up with every check. Before putting a row back, the bot can tell a row a person deleted from one that failed to be written. If the register is empty, the bot assumes it hasn't seen the number, so even a lost register file won't switch recovery off.

Replaying the same data with the register in place: 67 restores would not have happened, and all 13 genuinely missed writes would still have been restored. The safety net stayed fully in place, and the person's decision comes first.

What checking the watchdogs turned up

Watchdog scripts are only worth something once you've confirmed that their warning reaches a person. The check turned up three findings.

• When one of the two stores failed, the count of products sold beyond what's in stock quietly dropped from 9 to 3. The figure looked like good news, even though stock is ordered based on it. A product listed only in the store that was down dropped out of the count entirely.
• The watchdog for stuck orders on the work computer wasn't working: when run on a schedule, it had no access to the ssh agent with the keys. Meanwhile it reported "the source returned no orders, staying quiet", in the very words a healthy watchdog uses when there's nothing to report. Run by hand, the same code found real stuck orders, 5 and 11 days old. The problem went away once the watchdog moved to the server.
• The check of the warehouse sheets ran faithfully every four hours and wrote a file that nobody ever opened.

What the first months of work showed

The system is built and running. Over this time there were three failures worth describing separately, because each of them was easy to miss.

The daily sync reported a failure after a successful run. The output of the deploy command was piped into a text search. With pipefail switched on, the exit code comes from the last failed command in the chain. An empty search returns exit code 1 exactly when the system says "no changes". So the calmer the day, the more surely the alarm went off. The false alarm fired for several days in a row, and people stopped reading it. The fix: the exit code is taken from the deploy command itself, the output is written to a file and read afterwards, the "no changes" case is logged in words and counted as a success, and a real failure puts the end of the output into the log.

The nightly backup failed every night on the steps that dump the databases of a service that had been paused. A real backup failure would have been lost among these daily errors. Now these steps are skipped if the containers aren't there, and nothing will need editing when the service is switched back on.

Deploying by a list of checksums. The code that writes into the working sheets had no deploy command of its own. It was copied by hand, and an old version missing one of the fixes sat unnoticed on the server for two weeks. Now there is such a command: rsync without deleting files (the environment settings, daily files and manual backups live next to the code), its own list file and 62 files checked against their checksums. Before that, I checked each file to see which way it differed from the repository. No file had lines that were on the server and missing from the repository; otherwise a deploy would have quietly wiped out a manual edit. On the next deploy, the comparison before upload showed 107 matching files.

Switching to the working sheets through parallel writing

Handing a robot a sheet that two people rely on every day is frightening, and rightly so. So the switch was made in parallel: for a while the bot wrote into the working sheets at the same time as into reference copies. The daily check caught any differences, and rolling back meant switching off a single timer. There was never a big switch-on moment.