2026-10-20・Automation / Small business / Google Apps Script

A simple inventory tracker with low-stock alerts (Excel or Google Sheets)

Running out of something you sell or use every day is stressful. A cafe runs out of cups. A cleaner runs out of gloves. A shop runs out of its best seller just before the weekend.

Most small businesses do not need inventory software for this. A sheet with two tabs is enough: one where you log stock coming in and going out, and one that shows what is left and what needs reordering.

This guide works in both Excel and Google Sheets unless noted. The optional email alert at the end is for Google Sheets only.

What you will build

• A Log tab: one row every time stock comes in or goes out.
• An Items tab: one row per item, with current stock worked out by formula.
• A reorder point for each item, and a status that says Reorder when stock is low.
• Red rows for low items, so you see them at a glance.
• (Optional) A daily email listing everything that needs reordering.

Step 1. Set up the Log tab

Create a tab called Log with these headers in row 1:

Tips:

• Code is a short item code, like A1 or CUP-12. Short codes are easier to type than full names and avoid spelling mistakes.
• Type is either In or Out. Add a dropdown so nobody types "in", "IN" or "received". In Google Sheets it is Data > Data validation. In Excel it is Data > Data Validation. Choose a list and enter In, Out.
• Qty is always a positive number. The Type column decides whether it adds or subtracts.

To start, count what is on your shelves and add one In row per item with the note "Opening count". From then on, add a row each time a delivery arrives or stock is used or sold.

Step 2. Set up the Items tab

Create a second tab called Items with these headers:

Fill in the code and name of each item in columns A and B. The codes must match the ones you use in the Log exactly.

Step 3. Work out current stock with SUMIFS

In C2 (Total in), paste:

=SUMIFS(Log!$D:$D,Log!$B:$B,$A2,Log!$C:$C,"In")

In D2 (Total out), paste:

=SUMIFS(Log!$D:$D,Log!$B:$B,$A2,Log!$C:$C,"Out")

SUMIFS takes the column to add up first, then pairs of "column to check" and "what to look for". So C2 adds up every quantity in the Log where the code matches this row and the type is In.

In E2 (Current stock):

=C2-D2

Copy all three formulas down for every item. Now, whenever someone adds a row to the Log, the stock levels update by themselves.

Step 4. Set a reorder point

The reorder point is the level at which you want to order more. A simple way to choose it: how many do you use while you wait for a delivery? Add a little extra for safety.

Type a number in column F for each item. You can change it any time as you learn your real usage.

In G2 (Status), paste:

=IF(A2="","",IF(E2<=F2,"Reorder","OK"))

Copy it down. Any item at or below its reorder point now says Reorder.

To count how many items need ordering, put this in an empty cell, for example I1:

=COUNTIFS(G2:G500,"Reorder")

Step 5. Colour low-stock rows red

Google Sheets:

1. Select A2:G500.
2. Open Format > Conditional formatting.
3. Under "Format cells if", choose Custom formula is.
4. Paste:

=$G2="Reorder"

5. Pick a light red fill and click Done.

Excel:

1. Select A2:G500.
2. On the Home tab, choose Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Paste the same formula, click Format, and choose a light red fill.

The dollar sign in $G2 keeps the rule looking at the Status column, so the whole row turns red, not just one cell.

You can add an amber warning for items getting close. This rule marks items within 20 percent of their reorder point:

=AND($A2<>"",$E2>$F2,$E2<=$F2*1.2)

Step 6 (optional). Get an email when stock is low

This part is for Google Sheets only. A short Apps Script checks the Items tab and emails you a list of everything marked for reorder. If nothing is low, it sends nothing.

1. In your sheet, open Extensions > Apps Script.
2. Delete any code in the editor and paste this:

// Who gets the alert, and which tab to read
const ALERT_EMAIL = 'you@example.com';
const SHEET_NAME = 'Items';

function checkLowStock() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const rows = sheet.getDataRange().getValues();
  const lowItems = [];

  // Row 0 is the header, so start at 1
  for (let i = 1; i < rows.length; i++) {
    const code = rows[i][0];
    const name = rows[i][1];
    const stock = rows[i][4];
    const reorderPoint = rows[i][5];

    if (code === '' || reorderPoint === '') continue;
    if (stock <= reorderPoint) {
      lowItems.push(name + ' (' + code + '): ' + stock + ' left, reorder point ' + reorderPoint);
    }
  }

  if (lowItems.length === 0) return;

  const subject = 'Low stock: ' + lowItems.length + ' item(s) to reorder';
  const body = 'These items are at or below their reorder point:\n\n'
    + lowItems.join('\n')
    + '\n\nOpen the inventory sheet to check and reorder.';
  MailApp.sendEmail(ALERT_EMAIL, subject, body);
}

3. Change you@example.com to your own email address.
4. Click Save, choose checkLowStock in the function list, and click Run. The first time, Google asks you to give the script permission to read your sheet and send email as you. Review the request and allow it.

The script reads the columns in the order shown in Step 2, so keep that order. If you move columns, change the numbers in rows[i][...] to match (column A is 0, B is 1, and so on).

To run it every morning, open the Triggers page from the left side of the Apps Script editor (the clock icon). Add a trigger for checkLowStock, choose a time-driven trigger, then a day timer and the hour you want. Google runs it within that hour.

Using it day to day

• When a delivery arrives: add an In row to the Log.
• When you use or sell stock: add an Out row. If logging every sale is too much, do a quick count at the end of each day or week and log the difference as one Out row.
• Once a month: do a real count. If the sheet and the shelf disagree, add an In or Out row with the note "Count adjustment" to fix it.

Keep the Log as a history. Do not delete old rows; they are how you see what you use over time, which helps you set better reorder points.

What else could you automate?

Stock is one of many small admin jobs. Our free Small Business Automation Check asks about your country, industry and daily tasks, and shows which jobs are easiest to automate and where to start.