Build a low-stock reorder alert system

Northwind’s print shop runs on consumables — paper stock, ink cartridges, mounting board. Run out of any one of them mid-job and the job stops. The person handling procurement cannot watch the inventory sheet all day, so a low stock level usually gets noticed too late, when someone reaches for the last ream.

This script does the watching. It reads an Inventory sheet, compares each item’s quantity on hand against its own reorder threshold, and emails procurement a single digest of everything that needs reordering. A lastAlerted date stops the same item from nagging more than once a day, so the alert stays useful instead of becoming background noise.

What you’ll need

• A Google Sheet with one tab named Inventory and these columns in row 1: sku, name, onHand, reorderAt, lastAlerted.
  • onHand — current quantity in stock.
  • reorderAt — the threshold; at or below this, the item needs reordering.
  • lastAlerted — left blank; the script fills it with the date of the last alert.
• The Sheet’s ID, taken from its URL.
• The email address of whoever owns procurement.

The script

// The spreadsheet that holds the Inventory tab.
const INVENTORY_SHEET_ID = '1abcInventoryId';

// Who gets the reorder alert.
const PROCUREMENT_EMAIL = '[email protected]';

/**
 * Reads the Inventory sheet, finds items at or below their reorder
 * threshold, and emails procurement a single digest. Records the alert
 * date per item so nothing is flagged twice in the same day.
 */
function checkLowStock() {
  const sheet = SpreadsheetApp.openById(INVENTORY_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet. The first row is the header.
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;

  // 2. Stop if the sheet has a header but no stock rows.
  if (!rows.length) {
    Logger.log('Inventory sheet is empty — nothing to check.');
    return;
  }

  // 3. Map column names to their index, so the code reads by name
  //    rather than by fragile numeric positions.
  const col = Object.fromEntries(header.map((h, i) => [h, i]));

  // 4. Today's date as a plain string, for the lastAlerted check.
  const today = Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd');

  // 5. Build the alert lines and stamp each flagged item with today's date.
  const lines = [];
  rows.forEach((r, i) => {
    // Skip items still above their threshold.
    if (r[col.onHand] > r[col.reorderAt]) return;

    // Skip items already alerted today.
    if (r[col.lastAlerted] === today) return;

    lines.push('• ' + r[col.name] + ' (' + r[col.sku] + '): '
      + r[col.onHand] + ' left');

    // i is the row index within rows; +1 maps it back to values.
    values[i + 1][col.lastAlerted] = today;
  });

  // 6. If anything is low, email the digest and save the updated dates.
  if (lines.length) {
    GmailApp.sendEmail(
      PROCUREMENT_EMAIL,
      'Reorder ' + lines.length + ' item' + (lines.length > 1 ? 's' : ''),
      lines.join('\n')
    );
    sheet.getDataRange().setValues(values);
    Logger.log('Alerted on ' + lines.length + ' low-stock item(s).');
  } else {
    Logger.log('All stock levels are healthy.');
  }
}

How it works

1. checkLowStock opens the inventory spreadsheet and reads the entire tab in one call, splitting off the header row from the stock rows.
2. If there are no stock rows, it logs a message and stops.
3. It builds a col lookup mapping each column name to its index, so the rest of the code refers to col.onHand rather than a hard-coded number — adding a column later will not break it.
4. It formats today’s date as a yyyy-MM-dd string for comparison against the lastAlerted column.
5. It walks each row. An item is skipped if its onHand is still above reorderAt, or if it was already alerted today. Anything that passes both checks is added to the digest, and its lastAlerted cell in the in-memory values array is set to today.
6. If the digest has any lines, it emails procurement a single message listing every low item, then writes the updated values back so the new lastAlerted dates persist.

Example run

Say the Inventory tab looks like this when the check runs:

sku	name	onHand	reorderAt	lastAlerted
PPR-A4	A4 paper (ream)	3	10
INK-CYN	Cyan ink	12	8
MNT-BLK	Black mount board	5	5

A4 paper is below its threshold and black mount board is exactly at it, so procurement receives:

Subject: Reorder 2 items • A4 paper (ream) (PPR-A4): 3 left • Black mount board (MNT-BLK): 5 left

Both rows have their lastAlerted cell updated to today’s date. If the check runs again an hour later with the same levels, those two items are skipped and no duplicate email is sent. Cyan ink, at 12 against a threshold of 8, is never mentioned.

Trigger it

Run this on an hourly schedule during the working day:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose the checkLowStock function, an event source of Time-driven, an Hour timer, and Every hour.
4. Save and approve the authorisation prompt the first time.

A trigger cannot be limited to business hours alone, so it will also run overnight — harmless, since the lastAlerted check means it simply finds nothing new to send.

Watch out for

• The lastAlerted guard is per calendar day. If A4 paper stays low for a week, procurement is reminded once each day until it is restocked — useful as a nudge, but turn it into a one-off alert if you prefer by clearing the date only when onHand rises back above the threshold.
• Dates are compared as GMT strings. If your team works in a very different timezone, the “new day” boundary may land at an odd local hour — switch the timezone in Utilities.formatDate to match.
• The whole sheet is read and written with getDataRange. That is fine for a print shop’s inventory but would be slow for tens of thousands of rows; batch the write if the sheet ever gets that large.
• Column names must match the header row exactly, including capitalisation. Rename a header and the matching col. lookup quietly becomes undefined.
• If a manual edit is in progress when the script writes back, the setValues call could overwrite an unsaved change. Hourly runs make a clash unlikely, but avoid editing the sheet exactly on the hour.