प्रोजेक्ट: ईमेल अलर्ट वाला ऑटोमेटेड इन्वेंटरी ट्रैकर

1. Spreadsheet तैयार करें

दो tabs वाली spreadsheet बनाइए।

Inventory (row 1 = headers):

A SKU	B Item	C Stock	D Reorder Level	E Last Alert
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

फिर Extensions → Apps Script खोलें और Script Properties (Project Settings) सेट करें:

• ALERT_EMAIL → जिस address पर alerts जाएँ
• ALERT_REPEAT_DAYS → 3 (एक ही item का alert 3 दिन तक दोबारा न भेजें)

2. Settings और 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 और 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. Movement दर्ज करना (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();
  }
}

Lock दो लोगों (या sidebar और trigger) को एक ही पल में एक ही stock update करने से रोकता है।

5. रोज़ का 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();
  }
}

ध्यान दें:

• रोज़ एक email में सारे नए low items होते हैं — हर item का अलग email नहीं।
• Last Alert एक ही item को रोज़ report होने से रोकता है; stock सुधरते ही यह साफ़ हो जाता है।
• range.offset से सिर्फ़ column E वापस लिखा जाता है।
• कोई भी error आपको email हो जाता है, ताकि चुपचाप हुई गड़बड़ी छूट न जाए।

ईमेल भेजना और समय सहेजना अलग काम हैं। ईमेल चला जाए लेकिन समय न सहेजा जाए, तो दोबारा चलाने से पहले भेजे गए ईमेल की जाँच करें।

6. Trigger install करें (बिना 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 करें

1. शीट reload करें → Inventory → Record movement। CHN-014 का 2 का OUT दर्ज करें। Stock column और Movements tab जाँचें।
2. Stock से ज़्यादा OUT करके देखें — sidebar में error message आना चाहिए।
3. Inventory → Check low stock now — inbox जाँचें, और देखें कि Last Alert भर गया।
4. फिर चलाएँ — कोई नया email नहीं (alert पहले ही जा चुका)।
5. ऐसा IN दर्ज करें जिससे stock reorder level से ऊपर चला जाए, check चलाएँ — Last Alert साफ़ हो जाएगा।
6. Inventory → Install daily alert, फिर Triggers page पर पुष्टि करें।

आगे बढ़ाएँ (एक या ज़्यादा चुनें)

• Supplier column: alert email को supplier के हिसाब से बाँटिए और हर supplier को उसकी reorder request भेजिए (पहले draft!)।
• PDF stock report: हर सोमवार Inventory tab को PDF में export करके email कीजिए (getAs + time-driven trigger)।
• Web app: दुकान से movements दर्ज करने के लिए mobile-friendly doGet page।
• Barcode-friendly input: staff list से चुनने की जगह SKU type या scan कर सके।
• मासिक इतिहास: हर महीने की 1 तारीख़ को stock levels को History tab में copy करने वाला function, trend charts के लिए।

जमा करने से पहले checklist

• Menu, sidebar और movement दर्ज करना काम करता है
• Stock negative नहीं हो सकता
• अलर्ट ALERT_REPEAT_DAYS का अंतर मानते हैं; स्टॉक सुधरने पर पिछला समय साफ़ होता है
• Settings code से नहीं, Script Properties से आती हैं
• ठीक एक daily trigger है
• Errors email होते हैं