Reorder points belong next to on-hand counts. A nightly script can produce a buyer-facing ReorderList without filtering manually.
inventoryReorderCheck reads Inventory, compares onHand to reorderPoint, and copies matching SKUs with reorderQty and vendor.
ReorderList is cleared and rewritten each run so fulfilled lines disappear automatically when stock recovers.
Hook MailApp afterward if you want the buyer emailed when rows.length > 1.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Inventory | A sku | Item key |
| Inventory | B onHand | Current qty |
| Inventory | C reorderPoint | Threshold |
| Inventory | D reorderQty | Suggested PO qty |
| Inventory | E vendor | Supplier |
| ReorderList | A–E | Generated shortlist |
What this script does
Threshold scan producing a fresh ReorderList sheet.
Prerequisites
Inventory maintained; numeric onHand/reorderPoint.
- SKU unique
- ReorderList disposable
- Decide ≤ vs < policy
Walkthrough
Set onHand below reorderPoint for one SKU; run; confirm it appears.
Edge cases
Non-numeric onHand becomes 0 and may false-trigger — validate imports.
- ≤ includes equality
- Empty inventory yields header-only output
How to test
Three SKUs: below, equal, above — only first two list.
Hardening for production
Group ReorderList by vendor; create PO drafts.
Variations
Include days-of-cover using average daily sales column.
Full code: inventoryReorderCheck()
Keep Inventory updated, then run inventoryReorderCheck() on a daily trigger.
/**
* Flag SKUs at or below reorder point and write a ReorderList.
*/
const INV = "Inventory";
const OUT = "ReorderList";
function inventoryReorderCheck() {
const ss = SpreadsheetApp.getActive();
const values = ss.getSheetByName(INV).getDataRange().getValues();
const rows = [["sku", "onHand", "reorderPoint", "reorderQty", "vendor"]];
for (let i = 1; i < values.length; i++) {
const sku = values[i][0];
const onHand = Number(values[i][1]) || 0;
const reorderPoint = Number(values[i][2]) || 0;
const reorderQty = Number(values[i][3]) || 0;
const vendor = values[i][4] || "";
if (onHand <= reorderPoint) {
rows.push([sku, onHand, reorderPoint, reorderQty, vendor]);
}
}
const out = ss.getSheetByName(OUT) || ss.insertSheet(OUT);
out.clearContents();
out.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
Logger.log("%s SKUs need reorder", rows.length - 1);
}- Line 18: Inclusion rule for reorder candidates.
- Line 19: Collects SKUs that need purchasing.
- Line 24: Rebuilds ReorderList from scratch.
- Line 25: Writes header + candidates.
- Line 26: Reports how many SKUs need reorder.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: reorder check
- 1Inventory columns match A–E
- 2onHand reflects recent receipts/shipments
- 3reorderPoint agreed with ops
- 4ReorderList not used for manual notes
- 5Trigger cadence matches receiving cycle
- 6Optional email alert planned
Frequently asked questions
So recovered stock drops off automatically without stale buyer tasks.
Bake it into reorderPoint or add a separate column and compare onHand to reorderPoint+safety.
Filter by warehouse column or maintain one Inventory sheet per location.
After building ReorderList, group by vendor and append to a PO sheet.
Number() works for fractional units; round when vendors require integers.
Still ≤ reorderPoint — investigate data errors separately.
Single pass is fine for typical catalog sizes.