Paid Shopify orders → warehouse spreadsheets, with no manual entry
A two-way pipeline between two Shopify stores and the warehouse team's working Google Sheets: orders, tracking numbers, statuses and cancellations land on their own every 30 minutes — with reconciliation, backups and self-healing.
Every paid order was retyped into the spreadsheet by hand
A US outdoor-gear brand sells through two Shopify stores, with goods shipping from China. All of the logistics — shipments, warehouses, tracking numbers — lived in two large Google Sheets maintained by two operations people. Every paid order was copied there manually: order number, product, source warehouse, date, tracking number.
That's hours of work a day and a spreadsheet permanently lagging behind reality. Hence the familiar set of failures: orders went missing, cancelled ones stayed listed as active, tracking numbers never made it into the row. Mistakes surfaced not at entry time, but after someone had already made a decision based on the sheet.
And here's why nobody had automated it before. This spreadsheet isn't a database — it's a living tool a human works in: manual edits straight into cells, formulas, conditional formatting, an established row order, dropdown lists. Any naive "just append a row at the bottom" breaks that order and destroys the sheet's value for the people using it.
A pipeline in two halves: Python pulls, Apps Script places carefully
The Python half answers "what to write": it fetches paid orders from both stores through the Shopify Admin API, normalizes the data and assembles a batch of rows. The Google Apps Script half answers "how to write it so the human never notices an intrusion" — and that turned out to be the hard part.
Paid orders from two stores, incremental fetch every 30 minutes
Product name maps, source warehouse, partial-refund and cancellation states
What to insert, update or mark cancelled — decided before any write
Advanced Sheets Service: mid-grid insertion, formula fill-down, formatting
The operator sees the order in its place — row order and manual edits intact
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 not the same thing, 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. By the time it went live on the
production books it had reached version 69 — nearly every release closed off another
undocumented Google behaviour.
Mid-grid insertion and formula fill-down
An order has to land in its place, not at the bottom of 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.
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 — not an optimization, but the condition for the spreadsheet staying usable at all.
Operations layer: six 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 — every 20 minutes, a snapshot for work outside the sheet
- 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, not only in CI
On top of that: Telegram notifications to the operator, self-healing for lost records, 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.
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.
Nothing exotic — all the difficulty sits in Google's behaviour
Pulls paid orders from both stores; HTTP client on urllib, no extra dependencies
Receiver on Advanced Sheets Service: mid-grid insertion, formula fill-down, formatting
Reads book state for reconciliation and export, applies batched edits
Six timers, locking against overlapping runs, OnFailure alerts
Notifications to the operator: what was written, what diverged, what failed
A thousand-plus tests, including a check that the business-rules reference matches the code
Comparison before and after
automatic posting interval, both stores
711 problem cells before the bot went live — exactly 711 after
timers: posting, export, healthcheck, reconciliation, backup, tests
Manual order entry disappeared as a category of work. Paid orders from both stores show up in the working books on their own — with tracking number, warehouse, status, and a cancellation mark when an order gets cancelled.
The "zero new errors" metric deserves an explanation, because it matters more than speed. Before launch the book was inventoried: 711 problem cells, the legacy of years of manual work. After the bot went live the same check was repeated and returned exactly those same 711. The automation added not a single new error to a living working document — while writing into it every half hour.
Daily reconciliation against Shopify catches divergences on its own and hands them over as a list: in one run, 28 discrepancies were flagged and marked resolved. That's a fundamentally different mode of work — nobody hunts for what drifted, they work through a ready-made list.
"Everything through Shopify — much faster and more convenient, it's become far easier."
The second operations person onboarded to the pipeline, after the first had been running on the production books.
Worth noting separately: roughly five weeks from the first MVP to writing into production books. Most of that went not into "pull orders out of Shopify" — that's a day's work — but into making writes to a living human spreadsheet safe.
Where else the same methodology applies
This case isn't "a Shopify integration". It's the standard problem of "source of truth in an API → a live spreadsheet people work in". Every other company has it, and almost everywhere the attempt was to append rows at the bottom of the sheet — then abandoned, because it breaks how people work:
- → Marketplaces (Amazon, Wildberries, Ozon) → the purchasing and supply sheets managers maintain
- → CRM / ERP → the owner's roll-up book, full of manual comments and hand-built formulas
- → Logistics and tracking — carrier statuses into the shipments register with no copy-paste
- → Payment and billing systems → financial registers where reconciliation matters more than speed
- → Any "legendary spreadsheet" a person has maintained for years and that you can't simply replace with a dashboard
- The Apps Script receiver: mid-grid insertion, formula fill-down, validation-rule collapsing
- Per-spreadsheet name-normalization maps the operator edits directly — no code release
- Daily reconciliation against the source of truth, producing a list of divergences instead of a vague "something looks off"
- The "second circuit" cutover: parallel writes to the production book and a reference copy, rollback = one timer off
- A test that fails when the business-rules reference drifts from the code — documentation can't go stale quietly
If your people move data from a system into a spreadsheet by hand — that can go away
And without "migrating to a proper system": the spreadsheet stays exactly as it is, row order and formulas survive, the data arrives by itself. It starts with one book and one source — adding more after that is cheap.
The 5,000 ₽ audit — with a concrete report and quote
I'll tell you what to deploy in your business first, what the payback looks like, and whether you need AI for the task at all (sometimes you don't).
Or just send your question — I reply within 2 hours