Guide / Sheets and stock
Inventory in Google Sheets: the honest build guide
Sheets fixes Excel's sharing problem and invents its own: everyone can edit everything, simultaneously, with no guardrails. A sound build, its ceiling, and when to migrate.
The key facts
- Sheets beats Excel at one thing: shared, simultaneous editing from any browser — the "second person" problem partially solved.
- A sound build: products tab, movements tab, derived quantities, protected ranges — the same architecture real systems use, hand-rolled.
- The ceiling: no guardrails, no movement enforcement, quantity drift from mobile edits, and reporting that depends on discipline.
- BSimple's position: the stage above Sheets — $180/month AUD upward, full-product trial, spreadsheet import is easy.
- Context: the full inventory system holds the wider system, this page one stop in it.
- 01The key facts
- 02The sound way to build it
- 03The ceiling — where Sheets stops being management
- 04The migration trigger, and the path
The sound way to build it
If Google Sheets is your stage, build it properly: a products tab (SKU, name, supplier, par level — one row per product, never edited in passing), a movements tab (date, product, quantity in or out, reason, who — append-only), and quantities derived by formula from the movements, never typed. Two habits complete it: protect every range except the movements tab, and reconcile with a real count monthly. That structure is, genuinely, the architecture of a real inventory record (the Excel-and-VBA variant is the same idea with macros) — and it exports cleanly to anything later.
What the sharing gives you is real: the warehouse and the office see the same tab, from any device, and edits arrive live. For a small team with one person who owns the file, this is a legitimate operation.
The ceiling — where Sheets stops being management
No guardrails. Formulas suggest; nothing enforces. Selling below zero is a typo away, and the movement that explains a variance is whatever someone remembered to type in the reason column.
Editing drift. The price of "everyone can edit everything" is paid at scale: a mobile quick-fix of a quantity cell bypasses the movements tab, and the derived number is wrong in a way nobody will find until the count. Protection helps; enforcement is not what Sheets does.
No scan-to-record. A scanner types into a focused cell — workable on a laptop, awkward on the floor. The scan-to-movement flow that warehouses live on does not exist here. The scanner pairing is the missing half.
No actions. Sheets records; it does not manage — no reorder drafts, no order checking against stock, no supplier reconciliation. Every management act is a human running the right filter at the right time, and the running is the failure point. The management-vs-recording distinction is exactly this line.
The migration trigger, and the path
The honest trigger is not size — it is the first quantity that turned out wrong because someone edited a cell instead of recording a movement, or the first day two people needed the file to enforce something. When the count stops agreeing with the tab, the free stage has ended.
The path is kind: your tabs are already a data model. Export products and movements (or run one honest stocktake), import into the system, and record movements there from day one. We build BSimple, so weigh that: import is first-class, guardrails and scan-to-record are built in, reorders and counts come with the record — $180/$250/$399 per month AUD, accounting handed to Xero or MYOB, the trial free and full. The Sheets-specific comparison and the spreadsheet-exit guide cover the transition in detail.
Frequently Asked Questions
Can Google Sheets be used as inventory management software?
As an inventory record, yes — built properly (movements tab, derived quantities, protected ranges) it is a legitimate small-business tool. As management — reorders, order checking, enforced movements — no; that is the line where software starts.
How do we stop people editing quantities directly?
Protected ranges plus a movements-only sheet is the honest ceiling of what Sheets can enforce. If the protection gets circumvented — and at volume it will — that is the migration trigger, not a training failure.
What is the best free inventory template for Sheets?
The one you build from the structure above — templates downloaded cold tend to store editable totals, which is the exact trap. Fifteen minutes building the movements version beats an afternoon repairing a template's drift.
Can Sheets connect to barcode scanners?
A keyboard-mode scanner types into a focused cell, so scans work at a laptop. The floor-grade flow — scan at the shelf, movement recorded against live stock — is what an inventory system provides and Sheets cannot.
When should we move off Sheets to a real system?
When a quantity is ever wrong because a cell was edited instead of a movement recorded, or when reordering and order-checking decisions outgrow manual filters. The trial with your own tabs imported is the honest test of the other side.
BSimple