To build an AppSheet inventory app that scans stock in and out, create an Items table keyed on the barcode and a Movements table with item, In or Out, quantity and date. Mark the barcode input as scannable, calculate stock on hand from the movements, and add a bot that emails you when an item falls to its reorder level.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowScanning needs the Core plan or above, and it works only in the AppSheet app on a phone or tablet, not in a browser. If your range is under a few hundred lines and you already use Google Workspace, this build is a good fit; the sections below cover the data design, the formulas, the alert and the monthly count.
Which AppSheet plan and storage does an inventory app need?
An AppSheet inventory app that scans barcodes needs the Core plan or above, and its movement log belongs in a Google Sheet, because AppSheet's own database is capped at 2,500 rows per database on Core. Take a marketing agency that keeps branded merchandise for clients in a store room: 60 lines such as tote bags, notebooks, pens and mugs, with deliveries in and parcels out every day. Three staff need the app. Before anything else, check the licence and the data store, because both have limits that surprise people.
| Question | Answer from AppSheet's own pages | What it means here |
|---|---|---|
| Is barcode scanning in Starter? | No. Barcode scanning and NFC are in Core and Enterprise Plus | Budget for Core: 3 users x $10 = $30 a month, or $0 if Workspace includes it |
| Where does scanning work? | Mobile devices only, not browser or editor emulator | Test on a real phone from day one |
| Rows in AppSheet's own database? | Free 1,000, Starter and Core 2,500 per database; Enterprise Plus 200,000 | A busy movement log would hit 2,500 within months |
| Rows in a Google Sheet? | Sheets itself allows up to 20 million cells | Use a connected sheet for the log |
| Email recipients a day? | Starter, Core and Publisher Pro: 1,000 to 20,000; free plan 30 | Plenty for a stock alert |
Do the movement sum for this agency: 15 movements a day over 250 working days is 3,750 rows a year. In an AppSheet database on Core, that would stop accepting new data after about 167 working days (2,500 divided by 15), though existing data stays readable. In a Google Sheet, 3,750 rows of seven columns is 26,250 cells, a tiny fraction of the 20 million limit. So: data in Google Sheets, app in AppSheet. The AppSheet pricing guide goes through the free plan's limits; the free plan cannot scan, but it is fine for building and testing the sheet structure.
Should you start from the AppSheet sample template or your own sheet?
Start from AppSheet's Inventory Management sample template if your items already have manufacturer barcodes, and from your own sheet if you already have a stock spreadsheet with your own codes. AppSheet's barcode help points to an Inventory Management sample template that manages items and lets you scan a barcode to generate the Item ID when adding a new item. It is worth copying and opening, if only to see how a working app is laid out. Whether to use it or your own sheet comes down to two tests:
- If your items already have manufacturer barcodes (a UPC or EAN printed on the box) and you want to scan to add new lines, the template is a quick start.
- If you have an existing stock spreadsheet with your own codes and a reorder point, start from your own sheet. It will need less rewriting, and the template's column names may not match your terms.
Either way, build two tables, not one. The beginner's mistake is a single sheet with a Stock column that staff overtype. That gives you the current number but not the history, so a wrong count cannot be traced. A log of movements gives you both.
The two tables, with filled-in rows
The Items table holds one row per product. The Movements table holds one row per event. Here is the agency's structure with sample rows (illustrative):
| Items column | Type | Sample row |
|---|---|---|
| Barcode (key) | Text, scannable | 5012345678900 |
| Name | Text | Canvas tote bag, natural |
| Location | Enum | Shelf B2 |
| Reorder level | Number | 40 |
| Reorder quantity | Number | 200 |
| Supplier email | orders@example-supplier.com | |
| On hand | Number, virtual (formula) | calculated |
| Low stock | Yes/No, virtual (formula) | calculated |
| Movements column | Type | Sample row |
|---|---|---|
| Movement ID (key) | Text, initial value UNIQUEID() | a3f9c1d2 |
| Item | Ref to Items | 5012345678900 |
| Direction | Enum: In, Out, Count adjustment | Out |
| Qty | Number | 25 |
| Signed qty | Number, app formula | -25 |
| Reason | Enum: Delivery, Client order, Event, Damaged, Count | Client order |
| Moved by | Email, initial value the signed-in user | name@example.com |
| Moved at | DateTime, initial value NOW() | 17 Nov 2026 9:42 am |
The Signed qty formula turns direction into a sign, so that adding up one column gives the balance:
IFS(
[Direction] = "In", [Qty],
[Direction] = "Out", -[Qty],
TRUE, [Qty]
)
For a Count adjustment the quantity is entered positive or negative by whoever does the count; the TRUE branch passes it through unchanged.
If the Items and Movements sheet is the part you want to get better at, try The Complete Excel Course with AI Integration on Coding Liquids.
How do you make an AppSheet barcode column scannable?
To make a barcode column scannable in AppSheet, go to Data in the editor, select the table and enable the Scan setting for the column in the column list. You can also open the column's edit dialog, expand Other Properties and tick Scannable. Following AppSheet's barcode help, optionally enable Search (Searchable, in the edit dialog) so the scanner also works in the app's search box. Inside the app, the field then shows a scan icon; tapping it opens the camera and enters the code as the column's value.
On the Items table, make Barcode the key and mark it scannable, so adding a new line is a scan plus a name. On the Movements table, add a scannable text column called Scan item, hide the Item column from the form, and give Item the app formula [Scan item], so the scanned code resolves to the matching item. Add a Valid_If on Scan item that checks the code exists in Items, which catches a mis-scan or an unregistered product at the shelf rather than in the log. Test whichever version you choose on a real phone, because the editor's preview cannot scan.
AppSheet reads these formats out of the box: Codabar, Code 39, Code 93, Code 128, Data Matrix, EAN-8, EAN-13, Interleaved 2 of 5, PDF417, QR Code, UPC-A and UPC-E. If you have damaged labels or poor lighting, the help suggests the optional Scandit scanner, which needs its own account and licence key. For your own products without a manufacturer code, print labels in Code 128 or QR from any label tool and stick them on the shelf bay or the box.
Where the editor's emulator catches people out
In the editor's preview the Scan button appears but returns the same value, 111122333, each time. A builder who sees that and concludes scanning "works" has not tested anything. Install the AppSheet app on a phone, sign in with the same account and scan a real box. Do this before you tell the team it is ready.
How do you set up stock-in and stock-out screens in AppSheet?
Give staff two big home-screen buttons, Receive stock and Send stock, that open the Movements form with Direction preset. Staff do not want to open a table. Create two form views on the Movements table with different initial values for Direction (In for the first, Out for the second), and add each as a menu item. For each form, order the fields: scan item, quantity, reason. The rest fill themselves.
Add two safeguards on the Qty column:
- Valid_If: Qty must be greater than 0 for In and Out movements, for example
OR([Direction] = "Count adjustment", [Qty] > 0). This blocks a stray minus sign. - Out limit: for Out, the quantity must not exceed what is on hand, for example
OR([Direction] <> "Out", [Qty] <= [Item].[On hand]). This stops stock going negative, which is the first sign something was missed.
Pair this with a plain rule for the team: scan, type the number, tap save. The first week, look at the Movements log each evening, because you will catch a wrong item or direction while the box is still in front of you.
How do you work out stock on hand in AppSheet?
Stock on hand is the sum of each item's signed movement quantities, worked out in a virtual column on the Items table. On the Items table, create the virtual column On hand with a formula that adds the signed quantities for that item:
SUM(SELECT(Movements[Signed qty], [Item] = [_THISROW].[Barcode]))
AppSheet also creates a reverse-reference column on the Items table automatically when Movements has a Ref to Items (its name begins with "Related" and it uses REF_ROWS()), so SUM([Related Movements][Signed qty]) is the shorter form. The formulas in this tutorial are patterns assembled from AppSheet's documented functions, not recipes tested on your data, so test each in the preview and use whichever works; if the sum returns 0 while the log has data, check that the formula points at the table and not at a slice that omits the Signed qty column.
The agency's tote bag has these movements (illustrative):
| Date | Direction | Qty | Signed | Running |
|---|---|---|---|---|
| 3 Nov | In | 200 | +200 | 200 |
| 7 Nov | Out | 60 | -60 | 140 |
| 11 Nov | Out | 55 | -55 | 85 |
| 14 Nov | Out | 30 | -30 | 55 |
| 17 Nov | Out | 20 | -20 | 35 |
With a reorder level of 40, the item should be flagged after the 17 November movement, when stock drops to 35. Add a second virtual column, Low stock, with the formula [On hand] <= [Reorder level], and a format rule that turns the item red when it is true. Also create a slice called "To reorder" for items where Low stock is true, and show it as a view, so the buyer has one screen to open.
How do you get an email when an item reaches its reorder level?
Create a bot on the Movements table that emails you only on the movement that takes an item across its reorder level. Set the event to adds only, with a condition that fires only on that movement, not on every later one:
AND(
[Direction] = "Out",
[Item].[On hand] <= [Item].[Reorder level],
([Item].[On hand] + [Qty]) > [Item].[Reorder level]
)
The first test says this was a stock-out movement, the second says the item is now at or below its level, and the third says it was above the level before this movement. Without the third, every sale below the line sends another email. Test it in the preview with the tote bag figures above: the 17 November movement should fire it, the 14 November one should not.
Add a Send an email task using a custom template. Put the buyer's address in To, or use the item's supplier email column, and write a subject such as "Low stock: <<[Item].[Name]>>" with a body that includes On hand, Reorder level and Reorder quantity. AppSheet's email help lists the recipient options, including a column in the current row or a referenced row. Remember two limits: in test mode every email goes only to the app creator until you run a deployment check and deploy, and on the free plan email goes only to the owner. For more on emails when a status changes, see the notifications tutorial.
If you would rather receive a single morning list than an email per item, use a scheduled event instead. Scheduled events are part of Core and Enterprise Plus, and the same bot can run on the Items table each day at 8 am with the condition set to Low stock. Be aware that a scheduled bot on a table with 12 low items is likely to send 12 emails; if that is too noisy, stay with the crossing rule above.
How do you keep stock numbers honest with a monthly count?
A short monthly count, written back as an adjustment so the history is preserved, stops a movement log drifting from reality through breakages, uncounted samples or a box taken without a scan.
- Create a Counts table: Count ID, Item (Ref), Expected (initial value the item's On hand), Counted, Variance (Counted minus Expected), Counted by, Counted at.
- On the first of the month, a staff member walks the shelves and enters Counted by scanning each barcode. Expected is frozen at that moment, so a movement while counting does not muddy the variance.
- A button on the Counts row adds a Movement with Direction set to Count adjustment, Qty equal to the variance and Reason set to Count.
- Review variances greater than 5 or 10 per cent by hand before you accept the adjustment.
A filled-in example for the agency: 60 lines, 9 show a variance, and the largest is mugs, expected 120 and counted 114 (variance of -6, which is 5 per cent). Six mugs were handed out at an event without a scan. The adjustment fixes the number and the Reason column tells you why. If the same item shows a variance three months running, the problem is a process, such as a second door or a box kept at a desk, not a counting error. The tutorial on running a stocktake with a phone scanner covers the counting routine in more depth.
From a clipboard to the app: what changes on the shelf
Before the app, the agency's routine was a clipboard by the store-room door: staff wrote a line when they took something, and the owner typed the sheet up on Fridays. A typical week had 75 movements, of which about 10 were never written down. After the app, the same week is 75 scans, each with a name, a time and a reason. The Friday typing goes, and with it the arguments about who took the last box of notebooks. The time saved is modest, perhaps an hour a week (my estimate), but the real gain is that stock-outs reach the buyer on the day they happen rather than when a client asks for something that is not there.
A bigger shape: a 300-line shop
Take a small gift shop with 300 lines, a till that does not talk to the app, and two people receiving deliveries. Three changes suit it. Use manufacturer barcodes wherever the product has one, so receiving is scan, count, save. Stick a printed QR label on loose items, such as bulk candles, that encodes your own code. And split Items by supplier with a Supplier table, so the low-stock bot can email the right supplier from a referenced row, a recipient option listed in AppSheet's email help. The sheet stays comfortably inside Google's limits even with 40 movements a day, because 10,000 rows a year of seven columns is 70,000 cells.
Ten minutes to train a new member of staff
Write the routine on one card by the door: open the app, tap Receive or Send, scan the code, type the number, pick the reason, save. If the scan fails, type the last six digits into the search box and pick the item, which shows why the Search setting is worth enabling. Show it once, watch them do two movements, and check the log that evening.
If you want help fitting a stock app into how your business actually runs, I offer one-to-one AI consultations for small businesses.
What goes wrong in the first fortnight of an AppSheet inventory app?
The usual first-fortnight faults are a movement saved twice and cartons logged as single units. Say the agency's first week looks fine, then On hand for pens reads minus 14. The movements show an Out of 20 recorded twice: someone scanned, typed 20, saved, then the form stayed open and they saved again. The fix is a rule, not a rebuild. Set the form to close on save, add the Out limit above, and order the form so the quantity field is last before Save. A second pattern is a staff member scanning a pack barcode while the item is stored as single units, so a carton of 12 is logged as one. Decide up front whether you count cartons or units and put it in the item name.
Yearly housekeeping and limits you will meet
A movement log only grows. At 3,750 rows a year it is no problem for a sheet, but the On hand formula reads every movement for every item, so after several years it slows. At year end, add an "Opening balance" movement for each item equal to its On hand, then move the old rows to an archive sheet that the app does not read. The balances stay correct, the live log is short, and the old history is still there if a dispute arises.
If your range grows past a few hundred lines, with several locations, batch numbers or supplier prices, compare the effort with a bought tool: best inventory tools for small retailers lists options, and forecasting stock and reorders shows what your log could feed. If the sheet-versus-app question is still open, AppSheet versus plain Google Sheets helps.
A good first week looks like this: day one, build the two tables and enter 10 items; day two, scan a real box and test both forms on two phones; day three, add the low-stock bot and test with a throwaway item; day four, load the rest of the range with opening balances; day five, run the first count. Do not roll it out to the whole team until the throwaway item has triggered one email correctly.
Questions about scanning stock with AppSheet
Which AppSheet plan allows barcode scanning?
AppSheet's barcode help says scanning is supported on Core and Enterprise plans only, so Starter ($5 per user a month) will not do. Core is $10 per user a month, and it is included in most paid Google Workspace plans, so check your Workspace edition before buying licences separately.
Can staff scan from a laptop or the editor preview?
No. AppSheet scans only when the app runs on a mobile device. In a web browser the Scan button is not shown, and in the editor's emulator it appears but always returns the same test value, 111122333. Always test on a real phone.
Can I use a handheld Bluetooth scanner instead?
Yes. For an external scanner that behaves like a keyboard, set the barcode column to Text and do not mark it scannable. The scanner types the code into the field when you trigger it. The phone-camera scan and the external scanner will not work on the same column at once.
How many stock movements can the sheet take?
A Google Sheet holds up to 20 million cells. A movement log of seven columns would need about 2.8 million rows to reach that. AppSheet's own database is capped at 2,500 rows per database on Starter and Core, which is why this build uses a sheet.
How do I flag low stock in an AppSheet inventory app?
Add a Low stock Yes/No virtual column on the Items table that compares On hand with the reorder level, then a bot on the Movements table that emails you when a movement takes an item to or below its level. The bot's condition has three tests, so you get one email per crossing, not one for every later sale.
Further reads
- AI Inventory Forecasting for Small Businesses: How It Works — Turn the movement log into a demand forecast.
- How to Sync Stock Across Shopify, Amazon and eBay Automatically — Sync stock if you also sell online.
- How a Boutique Can Manage Size Runs and Reorders With AI — Size-run stock for fashion lines.
- AppSheet Field Service App: Job Cards, Photos and Sign-Off — Another AppSheet build, for jobs and photos.
- Which Jobs in a Small Clothing Workshop Can You Automate With AI? — Order intake, tech pack checks, fabric and cutting-plan sums, production tracking and customer updates for a cut-and-sew workshop, with tool costs and limits.
- Do Small Contractors Need Tool Tracking Software or a Sheet? — A sheet with a QR check-out form costs nothing; paid apps start free or around $100 a month. See what each must record and the sum that decides it.
Show 2 more related tutorials
- Airtable Inventory Tracker With AI Reorder Notes for a Small Shop — Products, suppliers and stock movements in Airtable, a low-stock flag with case rounding, and a field agent that drafts each supplier's reorder.
- AppSheet With Gemini: Build a Job-Tracking App by Describing It — Describe a job tracker to Gemini in AppSheet, edit the tables it proposes, clear the sample data and test it with staff.
Browse all AI-for-business tutorials
Sources: AppSheet pricing page and help-centre articles on barcode scanning, subscriptions, automation limits, email and references; Google Drive help on Sheets limits; read on 17 Nov 2026.
