E‑commerce: Shopify + Sheets ops hub & low‑stock alerts

Context

A multi‑channel retailer (Shopify plus occasional wholesale) with a small ops team. Finance needed accurate COGS views; warehouse staff needed a simple daily checklist—not another SaaS login.

Challenge

• Order and fulfillment data lived in Shopify; planning and margin math lived in Sheets. Someone exported CSVs every morning—errors, delays, and no single source of truth.
• Low stock was noticed only when pickers hit empty bins or when ad spend spiked on out‑of‑stock SKUs.
• The team had tried a generic connector tool but hit row limits, awkward field mapping, and a monthly bill that scaled with order volume.

Approach

1. Mapped the minimum fields needed for ops vs. finance (line items, refunds, inventory by location, cost assumptions in a locked tab).
2. Implemented incremental sync: pull changed orders since last run, upsert rows in a normalized “Orders” sheet and a rolling “Inventory snapshot” tab.
3. Added Gmail alerts when sellable quantity for any watched SKU crossed a threshold, with digest mode to avoid alert fatigue.
4. Documented OAuth/scopes, rate‑limit handling (429 backoff), and how to re‑authorize if tokens expire.

Solution

A container‑bound Apps Script project plus a time‑based trigger (every 30–60 minutes during business hours, lighter overnight). Shopify REST/GraphQL calls via UrlFetchApp with secrets stored in Script Properties—not in the sheet. Sheets served as the readable layer for humans; the script stayed the integration layer.

Outcome

• Ops stopped manual CSV work; the leadership team had one place to see orders, refunds, and stock without logging into Shopify for every question.
• Stockouts from “unknown” low inventory dropped sharply because alerts fired before pick issues.
• Total cost of ownership stayed predictable: no per‑order automation tax—just maintainable code in their own Google account.

Related on this site

• Shopify + Sheets API guide →
• API integration services →