Project: automated inventory tracker with email alerts · Google Apps Script

Concept

1. Set up the spreadsheet

Create a spreadsheet with two tabs.

Inventory (row 1 = headers):

A SKU	B Item	C Stock	D Reorder Level
RNG-001	Gold ring 22K	12	5
CHN-014	Chain 18"	3	4
BNG-007	Bangle pair	6	6

Movements (row 1 = headers):

A Date	B SKU	C Type (IN/OUT)	D Qty	E Note	F User

Then Extensions → Apps Script and set Script Properties (Project Settings):

ALERT_EMAIL → the address that gets alerts
ALERT_REPEAT_DAYS → 3 (don't re-alert the same item for 3 days)

2. Settings and helpers (`Code.gs`)

function getConfig() {
  const p = PropertiesService.getScriptProperties().getProperties();
  return {
    alertEmail: p.ALERT_EMAIL || Session.getEffectiveUser().getEmail(),
    repeatDays: Number(p.ALERT_REPEAT_DAYS || 3)
  };
}

function getSheet(name) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
  if (!sheet) throw new Error(`Tab "${name}" not found`);
  return sheet;
}

3. Menu and sidebar

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Inventory')
    .addItem('Record movement', 'showMovementForm')
    .addItem('Check low stock now', 'checkLowStock')
    .addSeparator()
    .addItem('Install daily alert', 'installDailyTrigger')
    .addToUi();
}

function showMovementForm() {
  const html = HtmlService.createHtmlOutputFromFile('MovementForm').setTitle('Record movement');
  SpreadsheetApp.getUi().showSidebar(html);
}

function getSkuList() {
  const sheet = getSheet('Inventory');
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return [];
  return sheet.getRange(2, 1, lastRow - 1, 2).getValues()
    .filter(r => r[0])
    .map(r => ({ sku: r[0], item: r[1] }));
}

HTML file MovementForm:

<!DOCTYPE html>
<html>
  <body style="font-family: Arial; padding: 12px;">
    <label>Item</label><br>
    <select id="sku" style="width: 100%;"></select><br><br>
    <label>Type</label><br>
    <select id="type"><option>IN</option><option>OUT</option></select><br><br>
    <label>Quantity</label><br>
    <input id="qty" type="number" min="1" value="1"><br><br>
    <label>Note</label><br>
    <input id="note" style="width: 100%;"><br><br>
    <button id="save" onclick="save()">Save</button>
    <p id="msg"></p>
    <script>
      google.script.run.withSuccessHandler(list => {
        const sel = document.getElementById('sku');
        list.forEach(x => {
          const o = document.createElement('option');
          o.value = x.sku;
          o.textContent = x.sku + ' – ' + x.item;
          sel.appendChild(o);
        });
      }).getSkuList();

      function save() {
        const btn = document.getElementById('save');
        const msg = document.getElementById('msg');
        btn.disabled = true;
        msg.textContent = 'Saving…';
        google.script.run
          .withSuccessHandler(text => { msg.textContent = text; btn.disabled = false; })
          .withFailureHandler(err => { msg.textContent = 'Error: ' + err.message; btn.disabled = false; })
          .recordMovement(
            document.getElementById('sku').value,
            document.getElementById('type').value,
            Number(document.getElementById('qty').value),
            document.getElementById('note').value
          );
      }
    </script>
  </body>
</html>

4. Recording a movement (with a lock)

function recordMovement(sku, type, qty, note) {
  if (!sku) throw new Error('Choose an item');
  if (!Number.isSafeInteger(qty) || qty <= 0) throw new Error('Quantity must be a positive whole number');
  if (type !== 'IN' && type !== 'OUT') throw new Error('Type must be IN or OUT');

  const lock = LockService.getScriptLock();
  lock.waitLock(10000);
  try {
    const inv = getSheet('Inventory');
    const lastRow = inv.getLastRow();
    if (lastRow < 2) throw new Error('Inventory is empty');
    const skus = inv.getRange(2, 1, lastRow - 1, 1).getValues().map(r => r[0]);
    const index = skus.indexOf(sku);
    if (index === -1) throw new Error(`SKU ${sku} not found`);

    const stockCell = inv.getRange(index + 2, 3);
    const current = Number(stockCell.getValue()) || 0;
    const change = type === 'IN' ? qty : -qty;
    if (current + change < 0) throw new Error(`Only ${current} in stock`);

    stockCell.setValue(current + change);
    getSheet('Movements').appendRow([
      new Date(), sku, type, qty, note, Session.getActiveUser().getEmail()
    ]);

    return `Saved. ${sku} stock is now ${current + change}.`;
  } finally {
    SpreadsheetApp.flush();
    lock.releaseLock();
  }
}

The lock stops two people (or the sidebar and a trigger) from updating the same stock at the same moment.

5. Daily low-stock alert

function checkLowStock() {
  const lock = LockService.getScriptLock();
  lock.waitLock(10000);
  try {
    const cfg = getConfig();
    if (!cfg.alertEmail || !Number.isFinite(cfg.repeatDays) || cfg.repeatDays < 1) {
      throw new Error('Set ALERT_EMAIL and a positive ALERT_REPEAT_DAYS');
    }
    const inv = getSheet('Inventory');
    const lastRow = inv.getLastRow();
    if (lastRow < 2) return;
    const range = inv.getRange(2, 1, lastRow - 1, 5);
    const rows = range.getValues();
    const now = new Date();
    const repeatMs = cfg.repeatDays * 24 * 60 * 60 * 1000;
    const low = [];
    rows.forEach(r => {
      const [sku, item, stock, reorder, lastAlert] = r;
      if (!sku) return;
      if (stock === '' || reorder === '' || !Number.isFinite(Number(stock)) || !Number.isFinite(Number(reorder))) {
        throw new Error('Invalid stock/reorder value for ' + sku);
      }
      const isLow = Number(stock) <= Number(reorder);
      const recentlyAlerted = lastAlert instanceof Date && now - lastAlert < repeatMs;
      if (isLow && !recentlyAlerted) {
        low.push({ sku, item, stock, reorder });
        r[4] = now; // commit only after sending succeeds
      }
      if (!isLow) r[4] = '';
    });
    if (low.length) {
      const escapeHtml = value => String(value).replace(/[&<>"']/g, c => ({
        '&': '&amp;', '<': '&lt;', '>': '&gt;', '"': '&quot;', "'": '&#39;'
      })[c]);
      const tableRows = low.map(x =>
        `<tr><td>${escapeHtml(x.sku)}</td><td>${escapeHtml(x.item)}</td><td>${escapeHtml(x.stock)}</td><td>${escapeHtml(x.reorder)}</td></tr>`
      ).join('');
      const html = '<p>Items at or below their reorder level:</p>' +
        '<table border="1" cellpadding="6"><tr><th>SKU</th><th>Item</th><th>Stock</th><th>Reorder level</th></tr>' + tableRows + '</table>';
      const plain = low.map(x => `${x.sku} ${x.item}: ${x.stock} (reorder at ${x.reorder})`).join('\n');
      GmailApp.sendEmail(cfg.alertEmail, `Low stock: ${low.length} item(s)`, plain, { htmlBody: html });
    }
    range.offset(0, 4, rows.length, 1).setValues(rows.map(r => [r[4]]));
    SpreadsheetApp.flush();
  } catch (err) {
    console.error(err);
    try {
      MailApp.sendEmail(Session.getEffectiveUser().getEmail(), 'Inventory script error', err.stack || String(err));
    } catch (reportError) { console.error(reportError); }
    throw err;
  } finally {
    lock.releaseLock();
  }
}

Notes:

Sending email and saving the timestamp are separate operations. If sending succeeds but saving fails, inspect the sent email before retrying to avoid a duplicate.
One email per day lists every new low item — not one email per item.
Last Alert stops the same item being reported every day; once stock recovers it's cleared.
Only column E is written back, using range.offset.
Any error is emailed to you, so a silent failure doesn't go unnoticed.

6. Install the trigger (no duplicates)

function installDailyTrigger() {
  ScriptApp.getProjectTriggers()
    .filter(t => t.getHandlerFunction() === 'checkLowStock')
    .forEach(t => ScriptApp.deleteTrigger(t));

  ScriptApp.newTrigger('checkLowStock').timeBased().everyDays(1).atHour(9).create();
  SpreadsheetApp.getUi().alert('Daily low-stock check installed (runs between 9 and 10 a.m.).');
}

7. Test it

Reload the sheet → Inventory → Record movement. Record an OUT of 2 for CHN-014. Check the Stock column and the Movements tab.
Try an OUT larger than the stock — you should get an error message in the sidebar.
Inventory → Check low stock now — check your inbox, and that Last Alert was filled.
Run it again — no new email (already alerted).
Record an IN that brings stock above the reorder level, run the check — Last Alert is cleared.
Inventory → Install daily alert, then confirm it in the Triggers page.

Extensions (choose one or more)

Supplier column: group the alert email by supplier and send each supplier its own reorder request (draft first!).
PDF stock report: every Monday, export the Inventory tab to PDF and email it (getAs + a time-driven trigger).
Web app: a mobile-friendly doGet page to record movements from the shop floor.
Barcode-friendly input: let staff type or scan the SKU instead of picking from a list.
Monthly history: a function that copies stock levels into a History tab on the 1st of each month, for trend charts.

Submission checklist

Menu, sidebar and movement recording work
Stock cannot go negative
Alerts respect ALERT_REPEAT_DAYS; recovery clears the alert timestamp
Settings come from Script Properties, not code
Exactly one daily trigger exists
Errors are emailed

What you'll learn