Using AI to build a simple inventory reorder alert system from a spreadsheet you already use

43% of small businesses track inventory manually or not at all, according to a 2023 Wasp Barcode Technologies survey — and the cost of getting it wrong is not small: IHL Group research puts global inventory distortion (stockouts plus overstocks) at over $1.77 trillion annually. If you want to track inventory reorder points without software, this post walks you through building a reorder alert system inside Google Sheets using AI to write the automation code — no inventory software purchase required. The setup takes under 30 minutes, runs every morning without any manual action, and can reduce emergency reorder costs by 10–25% according to SCORE's operational guides{target="_blank"} — a real return on a one-time 30-minute investment.

What You Need Before You Start

Google Sheets{target="_blank"} — free spreadsheet tool from Google; this guide is written for Sheets because it supports free automated email alerts via Apps Script without any add-ons. Free with a Google account.

Google Apps Script{target="_blank"} — free, JavaScript-based automation platform built into Google Sheets. No separate account needed; you access it directly from your spreadsheet. The free daily quota covers 100 email recipients and 20,000 script runtime seconds — more than enough for any small business inventory list.

Claude{target="_blank"}, ChatGPT{target="_blank"}, or Gemini{target="_blank"} — any of these AI chat tools will write the Apps Script code for you from plain English. Free tiers on all three are sufficient for this task.

Time required: 15–20 minutes for basic setup (columns + conditional formatting). 25–35 minutes for full setup including the automated daily email alert.

Skill level: No coding required. You need to be comfortable opening a spreadsheet, pasting data into columns, and following copy-paste instructions. No prior programming knowledge assumed.

---

The Inventory Reorder Point Formula Small Businesses Actually Need

Before building anything, you need the right numbers in your spreadsheet. The reorder point formula is:

Reorder Point = (Average Daily Usage × Lead Time in Days) + Safety Stock

Safety stock — the buffer that protects you when demand spikes or a supplier runs late — is calculated separately:

Safety Stock = (Maximum Daily Usage − Average Daily Usage) × Lead Time

An example: if you sell an average of 12 units per day of a product, your supplier takes 7 days to deliver, and your maximum daily usage is 18 units, your safety stock is (18 − 12) × 7 = 42 units. Your reorder point is (12 × 7) + 42 = 126 units. When your current stock hits 126, you place the order.

Here's the catch: most small business owners plug in theoretical usage numbers rather than actual historical averages. AI can audit this. Paste your existing spreadsheet columns into any AI chat and ask it to flag whether your reorder point formulas use realistic averages or optimistic assumptions. It will tell you exactly which rows are underprotected.

---

How to Set Up Inventory Alerts in a Spreadsheet: Four Columns That Power the System

Open your existing inventory spreadsheet and add these four columns if you don't already have them. Column names matter — you'll reference them in the script.

1. Column A — Product Name: The item identifier. Keep it consistent; abbreviations cause mismatches later.
2. Column B — Current Stock: The live quantity on hand. This is the number you (or a team member) updates when stock arrives or gets used.
3. Column C — Reorder Point: The calculated threshold from the formula above. You can hard-code this number or use a formula referencing separate columns for daily usage and lead time — either works.
4. Column D — Supplier / Notes: Optional but useful when the alert fires and you need to act immediately.

Once those columns exist, add a visual warning layer:

5. Select all rows in your data range (e.g., A2:D50).
6. Click Format > Conditional formatting.
7. Under "Format cells if," choose Custom formula is.
8. Enter this formula: =$B2<$C2
9. Set the formatting to a red fill.
10. Click Done.

Every row where current stock falls below the reorder point will now turn red automatically. This takes under five minutes and requires no code. It doesn't send alerts, but it gives you a visual dashboard the moment you open the file.

---

How to Use AI to Write the Alert Script Without Knowing Any Code

The visual dashboard is useful, but it requires someone to open the file. The automated email alert fires whether or not you're looking. Here's how to get AI to build it for you.

Open Claude, ChatGPT, or Gemini and paste this prompt — adjusted to match your actual column layout:

I use Google Sheets to track inventory. My spreadsheet has these columns: Column A = Product Name, Column B = Current Stock (number), Column C = Reorder Point (number), Column D = Supplier. I want a Google Apps Script that checks every row starting at row 2. If the value in Column B is less than the value in Column C, I want it to send an email to [your email address] with a subject line of "Reorder Alert" and a body that lists each product name and current stock level that triggered the alert. Please write the complete script, ready to paste into Google Apps Script.

The AI will return a complete, working script. It typically looks like a block of code starting with function checkInventory() {. Copy the entire output.

Now paste it into your spreadsheet:

1. In Google Sheets, click Extensions > Apps Script. A new browser tab opens.
2. Delete any placeholder code in the editor.
3. Paste the script the AI gave you.
4. Click the Save icon (or Ctrl+S / Cmd+S).
5. Click Run to test it manually. The first time you run it, Google will ask you to authorize the script to send email on your behalf — click through the permissions prompts.
6. Check your inbox. If any rows have current stock below reorder point, you'll receive an email within 30 seconds.

If the email arrives and lists your at-risk products correctly, the script works. If it fires but the product names are wrong or missing, your column references in the script don't match your actual sheet — go back to the AI, describe the mismatch, and ask it to fix the column letters.

---

Activating the Alert: Setting a Daily Trigger

Running the script manually defeats the purpose. You want it to run automatically every morning.

1. In the Apps Script editor, click Triggers (the clock icon in the left sidebar).
2. Click + Add Trigger in the bottom right.
3. Set Choose which function to run to checkInventory (or whatever the AI named your function).
4. Set Select event source to Time-driven.
5. Set Select type of time based trigger to Day timer.
6. Set the time range to 7am to 8am (or whenever you start your day).
7. Click Save.

From this point forward, the script checks your stock levels every morning and emails you only when something needs attention. You don't open the spreadsheet unless you get an alert. The Google Apps Script installable triggers documentation{target="_blank"} covers all available trigger types if you want to run checks more frequently.

The trade-off here is update frequency versus accuracy. A daily trigger is only as useful as how often Column B (Current Stock) gets updated. If your team updates stock weekly, a daily alert will either fire every day or never fire at the right time. Decide on your update cadence before you set the trigger interval, and make sure whoever updates stock knows that number drives the whole system.

---

Making It Smarter: Factoring In Lead Time Variability

The most common reason reorder points fail in practice isn't math — it's lead time variability. If your supplier's delivery time ranges from 5 to 14 days, using the average of 9.5 days in your formula creates systematic stockout risk. The formula looks correct but underestimates real-world exposure.

The fix is to recalculate safety stock using your maximum lead time, not your average. Paste your actual historical order and delivery dates into any AI chat and ask:

Based on these delivery records, what is my maximum lead time, average lead time, and recommended safety stock for a product with average daily usage of [X] units?

The AI will calculate dynamic safety stock using your real data rather than your assumptions. This matters most for products with inconsistent suppliers or seasonal demand spikes — those are the items most likely to create emergency reorders that cost 15–30% more than standard orders.

AI can also help you back-calculate reorder points from historical sales data. Paste a column of weekly or monthly sales figures directly into the chat and ask it to derive average daily usage, flag any demand seasonality, and suggest a reorder point that accounts for your highest-usage weeks. No separate analytics tool required.

---

When Something Goes Wrong

Symptom: The script runs but you receive no email, even when stock is clearly below the reorder point. Root cause: The values in Column B or Column C are stored as text strings, not numbers, so the < comparison evaluates to false. This happens when data is copy-pasted from another system. Fix: Select Column B and Column C, go to Format > Number > Number, then re-run the script. Alternatively, ask your AI to add a parseFloat() conversion to the comparison in the script.

Symptom: The email fires every morning even when no stock is low. Root cause: The script is sending a blank or default email because the alert condition logic has an error — it's running regardless of the check result. Fix: Paste the script back into the AI chat and ask it to add a condition that only sends the email if at least one product meets the alert threshold, and to include a count of triggered rows in the logic.

Symptom: The trigger disappears or stops running after a few weeks. Root cause: Google Apps Script triggers tied to a spreadsheet can sometimes detach if the spreadsheet is moved, renamed, or copied. Fix: Return to the Apps Script editor, check the Triggers panel, and re-create the time-driven trigger. If you've shared the spreadsheet with a new owner, the trigger needs to be re-authorized under the new owner's account.

---

What to Do Next

Once the daily alert is running reliably for two to three weeks, go back to the AI and ask it to add a second email threshold — a "critical" alert that fires immediately (not on the daily schedule) when stock drops below 50% of the reorder point. That's the difference between a heads-up and an emergency.

If you use Microsoft 365 instead of Google Workspace, the equivalent workflow uses Microsoft Power Automate{target="_blank"} with the Excel Online connector — it watches a table for a row condition and sends an Outlook email. It's included in most Microsoft 365 Business plans{target="_blank"} (starting at $6/user/month as of early 2026). For teams not on Google or Microsoft, Airtable's free tier{target="_blank"} supports up to 1,000 records and has built-in automations that send email or Slack alerts when a field drops below a threshold — no code required.

For a broader look at keeping operations running on a lean stack, see how small businesses automate recurring admin tasks without dedicated software.

---

FAQ

How do I track inventory reorder points without buying dedicated software? The system described in this post runs entirely on Google Sheets and Google Apps Script — both free. You don't need tools like Fishbowl{target="_blank"}, inFlow{target="_blank"}, or Sortly{target="_blank"}. The trade-off is that you're managing updates manually, which works well for businesses with 20–150 SKUs and a consistent stock-updating habit. Above roughly 200 SKUs with multiple locations, a dedicated inventory tool likely justifies its cost.

What's the actual ROI of setting up a reorder point system? The numbers say 10–25% reduction in emergency and rush order costs, per SCORE's operational data. The honest answer is that the ROI depends on how much you currently spend on expedited shipping and emergency orders. If you spend $500/month on rush orders due to stockouts, even a 15% reduction is $900/year saved — for a one-time setup of 30 minutes. That's a favorable ratio by any measure.

Can AI write the Google Apps Script if my spreadsheet columns are in a different order? Yes, and this is one of the most practical uses of AI for non-technical users. Paste your actual column headers into the chat, describe what you want the alert to do, and the AI generates code mapped to your exact layout. If your structure changes later, paste the updated headers back in and ask for a revised script — the turnaround is under a minute.

What if my lead time varies a lot from order to order? This is the most important edge case to handle. If your supplier's lead time ranges significantly — say 5 to 14 days — use the maximum lead time in your safety stock calculation, not the average. Ask your AI tool to calculate safety stock using your maximum observed lead time and your average daily usage. The result will be a higher reorder point than you might expect, but it's the number that actually prevents stockouts.

Does this system work for businesses with seasonal demand? It works, but you need to update your reorder points seasonally. A reorder point calibrated for February demand will be wrong in November. Set a recurring calendar reminder — quarterly is usually sufficient — to revisit your average daily usage figures and recalculate. You can ask AI to help recalculate from a pasted column of recent sales in about two minutes.