DEV Community

Cover image for How to Build an Inventory Tracker with Automatic Reorder Alerts in Google Sheets
Juan Giles
Juan Giles

Posted on AI-assisted

How to Build an Inventory Tracker with Automatic Reorder Alerts in Google Sheets

#googlesheets #excel #productivity #tutorial

When I worked in the production area of a packaging plant, stock was counted by hand and we usually found out an item was missing when the line stopped. This is the spreadsheet I wish we had. It works in Excel and Google Sheets, uses only formulas, and an optional script emails you every morning.

1. Two tables, not one

The mistake most people make is typing the current stock directly. Instead, keep:

  • An Inventory sheet: Code, Item, Category, Unit, Opening stock, Min stock, Unit cost
  • A Movements sheet: Date, Code, Type (IN/OUT), Quantity, Note

Every delivery or consumption is a new row in Movements. You never overwrite a number, so you always know why stock changed.

2. Stock IN and OUT with SUMIFS

In the Inventory sheet the columns are: A Code, B Item, C Category, D Unit, E Opening stock, F Stock IN, G Stock OUT, H Current stock, I Min stock, J Unit cost, K Stock value, L Status. For the item in row 2:

Stock IN:  =IF($A2="","",SUMIFS(Movements!$D:$D,Movements!$B:$B,$A2,Movements!$C:$C,"IN"))
Stock OUT: =IF($A2="","",SUMIFS(Movements!$D:$D,Movements!$B:$B,$A2,Movements!$C:$C,"OUT"))
Enter fullscreen mode Exit fullscreen mode

The IF($A2="","",...) part keeps empty rows clean.

3. Current stock and value 

Current stock: =IF($A2="","",E2+F2-G2)
Stock value:   =IF($A2="","",H2*J2)
Enter fullscreen mode Exit fullscreen mode

4. The reorder alert 

Status: =IF($A2="","",IF(H2<=I2,"Reorder",IF(H2<=I2*1.5,"Low","OK")))
Enter fullscreen mode Exit fullscreen mode

"Reorder" means you are at or below your minimum. "Low" gives you a warning band (up to 1.5× the minimum) so you can order before it's urgent. Add conditional formatting: red for Reorder, amber for Low.

5. Avoid typos with dropdowns

Use data validation for Category, for Code in the Movements sheet (pointing to the Inventory codes) and for Type (IN/OUT). Most stock errors come from a code typed slightly differently.

6. A small dashboard 

Items tracked:     =COUNTA(Inventory!A2:A201)
Total stock value: =SUM(Inventory!K2:K201)
Items to reorder:  =COUNTIF(Inventory!L2:L201,"Reorder")
Items low:         =COUNTIF(Inventory!L2:L201,"Low")
Enter fullscreen mode Exit fullscreen mode

7. Optional: a daily low-stock email (Google Sheets)

With Apps Script (Extensions → Apps Script), you can read the Status column, collect the Reorder and Low items and send them with MailApp.sendEmail. Then a time-driven trigger runs it every morning. The key part:

const items = values.filter(r => r[11] === 'Reorder' || r[11] === 'Low');
MailApp.sendEmail({ to: Session.getEffectiveUser().getEmail(),
  subject: 'Low stock alert: ' + items.length + ' item(s)', htmlBody: html });
ScriptApp.newTrigger('sendLowStockReport').timeBased().everyDays(1).atHour(8).create();
Enter fullscreen mode Exit fullscreen mode

Free template

If you'd rather not build it from scratch, the complete file (Excel + Google Sheets, with dashboard, dropdowns and the email script) is free, pay what you want: https://gileson2.gumroad.com/l/inventory-tracker?utm_source=devto

The code is also on GitHub: https://github.com/JuanGiles260/inventory-tracker-sheets

I'm Juan, an industrial engineering student from Bolivia. I build spreadsheet templates for small businesses at Lean Grid Co. Questions about the formulas? Ask in the comments.

Top comments (0)