Automated Inventory Tracking with Google Forms and Sheets

Plenty of small businesses track stock in a spreadsheet that someone updates "when they get a chance." It works until two people edit the same cell, a delivery never gets entered, or the reorder happens a week too late.

This guide shows how to build a lightweight automated tracker with Google Forms and Google Sheets. Staff log every stock movement from their phone, the sheet calculates what is on hand, and an alert goes out when something drops below its reorder point. It suits small e-commerce brands, med spas with retail shelves and back-bar supplies, and dental or medical offices managing consumables.

Decide what you are actually tracking

Start by drawing a line around the job. If you sell online, your store platform probably already tracks sellable stock. Shopify, for example, can track product inventory across locations once tracking is turned on. You do not need to rebuild that.

The spreadsheet approach shines for everything the store does not track:

E-commerce brands: shipping boxes, mailers, tissue, inserts, samples, and raw packaging for products you assemble yourself.
Med spas: retail skincare on the front shelf, plus back-bar and treatment-room consumables.
Dental and medical offices: gloves, bibs, cleaning supplies, office stock, and anything with a lot number or expiration date you want visibility on.

Pick one category to start. A tracker that covers 40 items well beats one that covers 400 badly.

The key design choice: a log, not a counter

The most common mistake is a sheet with one row per item and a "Quantity" cell that people overwrite. It has no history, it breaks when two updates land at once, and when the number is wrong nobody can tell why.

Build it the other way around. Every stock movement becomes a new row in a log: what item, which direction, how many, who, when. The on-hand number is never typed in by hand. A formula adds up the log.

This gives you an audit trail for free. When a count looks off, you filter the log for that item and see exactly what happened.

Build it step by step

1. Create the item list

In a new Google Sheet, add a tab called Items with these columns:

Column	Example
Item code	BOX-SM
Item name	Small shipping box
Unit	each
Starting count	120
Reorder point	40
Reorder quantity	200
Supplier	Your packaging vendor
On hand	formula, see step 4

Use short, unique item codes. They are what the form and formulas match on, so a typo here breaks everything downstream.

2. Build one form for every movement

Create a Google Form with a few questions:

Item: a dropdown listing your item codes and names. Dropdowns prevent misspellings.
Movement type: Received, Used or sold, Damaged or expired, Count correction.
Quantity: a short answer with number validation set to greater than zero.
Lot or expiration date: optional, for items where it matters.
Notes: optional, required in practice for corrections.

One form for all movements is easier for staff than three separate forms. Add the form link as a home screen shortcut on the phones or tablets near the stockroom.

3. Send responses to the sheet

In the form's Responses tab, choose a destination for responses and select your existing spreadsheet. Each submission then lands as a new row with a timestamp. If your organization uses Google Workspace accounts and the form is set to collect email addresses, you also get who submitted it.

4. Calculate on-hand stock with a formula

On the Items tab, the On hand column adds the starting count to everything received and subtracts everything that went out. With SUMIFS, the logic for each row reads like this:

Starting count
plus the sum of quantities where the item matches and the type is Received
minus the sum of quantities where the item matches and the type is Used or sold
minus the sum where the type is Damaged or expired

Count corrections need a decision. The simplest rule: a correction records the difference (positive or negative), and staff explain it in Notes. Add a helper column on the responses tab that turns each row into a signed number (positive for received, negative for outgoing), and the on-hand formula becomes a single SUMIFS on that column.

5. Flag low stock visually

Add a Status column that shows "Reorder" when On hand is at or below the Reorder point. Then use conditional formatting to turn those rows red. Anyone opening the sheet sees what needs ordering at a glance.

6. Send an alert when something crosses the line

A red cell only helps if someone opens the sheet. To push a notification, you have two common options:

Apps Script: Google Sheets supports an installable trigger that runs a script whenever a linked form is submitted. A short script can recheck the item's status and send an email when it flips to Reorder. Apps Script emails the trigger's creator if a run fails.
An automation platform: tools like Zapier or Make can watch for new rows in the responses tab, look up the item, and send an email, team chat message or text when stock is low.

Only alert on the crossing (when status changes to Reorder), not on every submission below the line, or people will start ignoring the messages.

Two quick examples

A small skincare brand. Products live in Shopify, but packaging does not. The brand logs packaging deliveries through the form, and an automation subtracts one mailer and one insert for each new order using Shopify's order webhook. When small mailers drop below 40, the owner gets an email with the supplier and reorder quantity already filled in.

A med spa supply room. The office manager tracks retail skincare and treatment-room consumables. Staff scan a QR code on the cabinet door that opens the form. Every Friday, a filtered view lists items expiring in the next 60 days so they can be used first or returned to the vendor if the vendor allows it.

Keep the numbers honest

Automation makes logging easier. It does not make the shelf match the sheet on its own.

Count before you start. The starting counts must come from a physical count, not last month's guess.
Run cycle counts. Count a handful of items each week instead of everything once a quarter. Log differences as corrections with a reason.
Lock the formulas. Protect the Items tab ranges so only the manager can edit formulas and reorder points.
Review corrections. A pattern of negative corrections on one item usually means a process gap, not a math error.

When you have outgrown the spreadsheet

This setup is meant for a single location and a modest item list. Signs it is time for dedicated inventory software or your store platform's built-in tools:

You stock the same items across several locations and transfer between them.
You want barcode scanning at receiving and picking.
You need purchase orders, landed costs or a sync with your accounting system.
More than a few people log movements every hour.

The log-based structure still helps when you move, because you will have clean history to import.

A note for healthcare practices

Keep patient information out of an inventory tracker entirely. Item codes, lot numbers and quantities do not need a name, chart number or appointment attached. If your practice needs to link a specific lot to a specific patient, that belongs in the clinical record system, not in a supply sheet.

If any protected health information could end up in Google Forms or Sheets, note that Google offers a HIPAA business associate addendum for Google Workspace. Google's included functionality list covers Forms and Sheets, but only after an administrator accepts the BAA in the Admin console and configures sharing appropriately. Third-party add-ons and outside automation tools are separate. Zapier, for example, states that it does not sign BAAs. Talk to your compliance lead or counsel before connecting anything that could carry PHI.

Where NutriCove fits

NutriCove builds e-commerce stores and the email and SMS flows around them, including the order-triggered automations that keep packaging and fulfillment steps from relying on memory. If you're planning a store build or cleaning up your post-purchase operations, see our e-commerce services.

Sources

This article is general information, not legal or medical advice. Talk to a qualified attorney or licensed professional about your situation.