The Complete Inventory System Masterclass

2.5 Hour Workshop Full-Stack Architecture Enterprise Security

Go beyond basic scripts. Build a scalable, multi-user inventory management system with audit logging, real-time dashboards, and automated restocking intelligence using Google Apps Script.

From Spreadsheet to Software

Every growing business eventually hits the "Spreadsheet Ceiling"—that moment when Excel formulas start breaking, multiple users overwrite each other's data, and stock counts become unreliable. Buying an ERP system like NetSuite costs $10,000+ per year. Building one yourself costs $0.

In this comprehensive guide, we are not just writing code; we are architecting a solution. We will implement MVC Pattern (Model-View-Controller) logic within the Google ecosystem to ensure your app is robust, secure, and maintainable.

ACID-Compliant Data

We implement atomic transactions using `LockService` to ensure that even if 50 users click "Update" simultaneously, the database never corrupts.

Forensic Audit Logs

Every action is tracked. You will know exactly who removed stock, when they did it, and what the stock level was before the change.

Business Intelligence

We'll build a live dashboard that visualizes stock health, enabling data-driven purchasing decisions rather than guesswork.

Curriculum Overview

This is a serious technical deep dive. Here is our roadmap:

  1. Database Engineering: Structuring Google Sheets for relational data integrity.
  2. Backend Logic: Writing the CRUD API with validation and error handling.
  3. Frontend Development: Creating a responsive, single-page application (SPA).
  4. Advanced Logic: Implementing Search, Filtering, and Audit Logging.
  5. Security & Deployment: Managing permissions and publishing to the web.
1

Database Architecture & Schema

A robust application begins with a robust database. In Google Sheets, individual tabs act as our "Tables". We need a normalized structure to prevent data redundancy.

Why strict naming? Code refers to sheets by name (e.g., `getSheetByName("StockData")`). If you change the tab name in the UI without updating the code, the application will crash.

1. The Master Inventory Table

Create a new Google Sheet and rename the first tab to StockData. This holds the current state of truth.

  • A1: SKU (Unique Identifier - Primary Key)
  • B1: Name (Product Description)
  • C1: Category (For filtering/reporting)
  • D1: Stock_Level (Integer)
  • E1: Reorder_Point (Threshold for alerts)
  • F1: Unit_Price (For value calculation)
  • G1: Last_Updated (Timestamp)

2. The Immutable Audit Log

Create a second tab named AuditLog. This is an "Append-Only" table. We never delete rows here; we only add them. This provides a forensic trail.

  • A1: Transaction_ID (UUID or timestamp)
  • B1: Timestamp
  • C1: User_Email (Who did it?)
  • D1: Action_Type (IN / OUT / ADJUST)
  • E1: SKU
  • F1: Quantity_Change
  • G1: Snapshot_Stock (Stock level after change)
2

The Backend Controller (Code.gs)

Now we enter the Apps Script environment. This code runs on Google's servers. It acts as the API (Application Programming Interface) between your HTML frontend and the Sheets database.

Code.gs
/**
 * ENTERPRISE INVENTORY SYSTEM - BACKEND CONTROLLER
 * Features: ACID Transactions, Audit Logging, Batch Fetching
 */

const CONFIG = {
  SHEET_INVENTORY: "StockData",
  SHEET_LOGS: "AuditLog",
  APP_TITLE: "Inventory Master v2"
};

/**
 * 1. HTTP ENTRY POINT
 * Serves the Single Page Application (SPA) to the browser.
 */
function doGet() {
  return HtmlService.createTemplateFromFile('Index')
      .evaluate()
      .setTitle(CONFIG.APP_TITLE)
      .addMetaTag('viewport', 'width=device-width, initial-scale=1')
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

/**
 * 2. DATA ACCESS LAYER (READ)
 * Fetches inventory with calculated status and value.
 * Uses batch operations (getValues) for performance.
 */
function getInventoryData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(CONFIG.SHEET_INVENTORY);
  
  if (sheet.getLastRow() < 2) return [];

  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 7).getValues();
  
  return data.map(row => {
    const stock = Number(row[3]);
    const reorder = Number(row[4]);
    return {
      sku: row[0],
      name: row[1],
      category: row[2],
      stock: stock,
      reorder: reorder,
      price: Number(row[5]),
      status: stock <= reorder ? "CRITICAL" : (stock <= reorder * 1.5 ? "WARNING" : "OK"),
      totalValue: (stock * Number(row[5])).toFixed(2)
    };
  });
}

/**
 * 3. TRANSACTION LAYER (WRITE)
 * Handles stock updates with ACID-like properties using LockService.
 * Automatically appends to Audit Log.
 */
function updateStockLevel(sku, changeAmount) {
  const lock = LockService.getScriptLock();
  
  try {
    // Critical Section: Wait up to 10s for other users to finish
    lock.waitLock(10000); 
    
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(CONFIG.SHEET_INVENTORY);
    const data = sheet.getDataRange().getValues();
    const user = Session.getActiveUser().getEmail();
    
    // Linear Search for SKU
    let rowIndex = -1;
    for (let i = 1; i < data.length; i++) {
      if (data[i][0] == sku) {
        rowIndex = i + 1;
        break;
      }
    }
    
    if (rowIndex === -1) throw new Error("SKU Not Found: " + sku);
    
    const currentStock = Number(sheet.getRange(rowIndex, 4).getValue());
    const newStock = currentStock + Number(changeAmount);
    
    if (newStock < 0) throw new Error("Resulting stock cannot be negative.");
    
    // Write Updates
    sheet.getRange(rowIndex, 4).setValue(newStock); 
    sheet.getRange(rowIndex, 7).setValue(new Date()); 
    
    // Log Transaction (Side Effect)
    logTransaction(sku, changeAmount > 0 ? "IN" : "OUT", changeAmount, newStock, user);

    return { success: true, newStock: newStock, sku: sku };
    
  } catch (e) {
    throw new Error(e.message);
  } finally {
    lock.releaseLock(); 
  }
}

/**
 * Helper: Appends an immutable record to the log sheet
 */
function logTransaction(sku, type, qty, snapshot, user) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_LOGS);
  const timestamp = new Date();
  const id = Utilities.getUuid();
  sheet.appendRow([id, timestamp, user, type, sku, qty, snapshot]);
}

/**
 * 4. DASHBOARD ANALYTICS
 */
function getDashboardStats() {
  const data = getInventoryData();
  const totalItems = data.length;
  const totalValue = data.reduce((acc, item) => acc + (item.stock * item.price), 0);
  const lowStockCount = data.filter(item => item.status === "CRITICAL").length;
  
  return {
    totalItems: totalItems,
    totalValue: totalValue,
    lowStockCount: lowStockCount
  };
}
3

The Frontend Interface (Index.html)

This is the View layer. We are building a Single Page Application (SPA). The HTML, CSS, and Client-Side JavaScript are all bundled into one file for simplicity in deployment.

Index.html
<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <link href="https://fonts.googleapis.com/css2?family=Questrial&display=swap" rel="stylesheet">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
  <style>
    :root {
      --primary: #00897b;
      --danger: #e53935;
      --warning: #fbc02d;
      --dark: #263238;
      --light: #f5f5f5;
    }
    
    body { font-family: 'Questrial', sans-serif; background: #e0eceb; margin: 0; padding: 20px; color: var(--dark); }
    
    .dashboard-container { max-width: 1200px; margin: 0 auto; }
    
    /* Stats Cards */
    .stat-cards { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 20px; margin-bottom: 30px; }
    .card { background: white; padding: 20px; border-radius: 12px; box-shadow: 0 4px 6px rgba(0,0,0,0.05); }
    .stat-number { font-size: 2rem; font-weight: bold; margin-top: 10px; }
    .stat-label { font-size: 0.85rem; text-transform: uppercase; color: #777; letter-spacing: 1px; }

    /* Controls */
    .controls { display: flex; gap: 15px; margin-bottom: 20px; }
    .search-box { flex-grow: 1; position: relative; }
    .search-input { width: 100%; padding: 15px 15px 15px 45px; border: none; border-radius: 8px; box-shadow: 0 2px 5px rgba(0,0,0,0.05); font-family: inherit; font-size: 1rem; }
    .search-icon { position: absolute; left: 15px; top: 50%; transform: translateY(-50%); color: #999; }
    
    /* Inventory Grid */
    .inventory-grid { display: grid; grid-template-columns: repeat(auto-fill, minmax(300px, 1fr)); gap: 20px; }
    
    .item-card { background: white; border-radius: 12px; padding: 20px; position: relative; transition: transform 0.2s; box-shadow: 0 2px 8px rgba(0,0,0,0.05); }
    .item-card:hover { transform: translateY(-3px); box-shadow: 0 5px 15px rgba(0,0,0,0.1); }
    
    .item-header { display: flex; justify-content: space-between; margin-bottom: 15px; }
    .item-sku { font-size: 0.8rem; background: #eee; padding: 4px 8px; border-radius: 4px; font-weight: bold; }
    .item-cat { font-size: 0.8rem; color: var(--primary); }
    .item-name { font-size: 1.2rem; margin-bottom: 15px; font-weight: bold; white-space: nowrap; overflow: hidden; text-overflow: ellipsis; }
    
    .stock-level-wrapper { display: flex; justify-content: space-between; align-items: center; margin-bottom: 20px; }
    .stock-number { font-size: 2.5rem; font-weight: bold; }
    .stock-status { font-size: 0.8rem; font-weight: bold; padding: 5px 10px; border-radius: 20px; }
    
    .status-OK { background: #e8f5e9; color: #2e7d32; }
    .status-CRITICAL { background: #ffebee; color: #c62828; animation: pulse 2s infinite; }
    
    .actions { display: grid; grid-template-columns: 1fr 1fr; gap: 10px; }
    .btn { border: none; padding: 12px; border-radius: 8px; cursor: pointer; font-weight: bold; color: white; transition: opacity 0.2s; }
    .btn-add { background: var(--primary); }
    .btn-sub { background: var(--danger); }
    
    /* Loader */
    #loader-overlay { position: fixed; top: 0; left: 0; width: 100%; height: 100%; background: rgba(255,255,255,0.8); z-index: 1000; display: flex; justify-content: center; align-items: center; backdrop-filter: blur(5px); display: none; }
    .spinner { width: 50px; height: 50px; border: 5px solid #eee; border-top-color: var(--primary); border-radius: 50%; animation: spin 1s linear infinite; }
    
    @keyframes spin { 100% { transform: rotate(360deg); } }
    @keyframes pulse { 0% { opacity: 1; } 50% { opacity: 0.6; } 100% { opacity: 1; } }

  </style>
</head>
<body>

<div id="loader-overlay">
  <div class="spinner"></div>
</div>

<div class="dashboard-container">
  
  <!-- Stats -->
  <div class="stat-cards">
    <div class="card" style="border-left: 5px solid var(--primary);">
      <div class="stat-label">Total Items</div>
      <div class="stat-number" id="stat-skus">0</div>
    </div>
    <div class="card" style="border-left: 5px solid var(--primary);">
      <div class="stat-label">Inventory Value</div>
      <div class="stat-number" id="stat-value">$0</div>
    </div>
    <div class="card" style="border-left: 5px solid var(--danger);">
      <div class="stat-label">Low Stock Alerts</div>
      <div class="stat-number" id="stat-alerts" style="color: var(--danger)">0</div>
    </div>
  </div>
  
  <!-- Controls -->
  <div class="controls">
    <div class="search-box">
      <i class="fas fa-search search-icon"></i>
      <input type="text" class="search-input" id="search-input" placeholder="Search by Name, SKU or Category..." onkeyup="filterGrid()">
    </div>
    <button class="btn btn-add" style="width: auto; padding: 0 20px;" onclick="fetchData()">Refresh</button>
  </div>

  <!-- Main Grid -->
  <div id="grid-container" class="inventory-grid">
    <!-- Items injected here -->
  </div>

</div>

<script>
  let globalInventory = [];

  window.onload = fetchData;

  function fetchData() {
    showLoader();
    google.script.run
      .withSuccessHandler(onDataReceived)
      .withFailureHandler(onError)
      .getInventoryData();
      
    google.script.run
      .withSuccessHandler(updateDashboard)
      .getDashboardStats();
  }

  function updateStock(sku, amount) {
    showLoader();
    google.script.run
      .withSuccessHandler((response) => {
        const item = globalInventory.find(i => i.sku === response.sku);
        if(item) {
           item.stock = response.newStock;
           if(item.stock <= item.reorder) item.status = "CRITICAL";
           else item.status = "OK";
        }
        renderGrid(globalInventory);
        hideLoader();
      })
      .withFailureHandler((err) => {
        alert("Update Failed: " + err.message);
        hideLoader();
      })
      .updateStockLevel(sku, amount);
  }

  function onDataReceived(data) {
    globalInventory = data;
    renderGrid(data);
    hideLoader();
  }
  
  function updateDashboard(stats) {
    document.getElementById('stat-skus').innerText = stats.totalItems;
    document.getElementById('stat-value').innerText = '$' + stats.totalValue.toLocaleString();
    document.getElementById('stat-alerts').innerText = stats.lowStockCount;
  }

  function renderGrid(data) {
    const container = document.getElementById('grid-container');
    container.innerHTML = '';
    
    if(data.length === 0) {
      container.innerHTML = '<div style="grid-column: 1/-1; text-align:center; padding: 40px; color:#999">No items found.</div>';
      return;
    }

    data.forEach(item => {
      const card = document.createElement('div');
      card.className = 'item-card';
      const statusClass = 'status-' + item.status;
      
      card.innerHTML = `
        <div class="item-header">
          <span class="item-sku">${item.sku}</span>
          <span class="item-cat">${item.category}</span>
        </div>
        <div class="item-name" title="${item.name}">${item.name}</div>
        
        <div class="stock-level-wrapper">
          <div class="stock-number">${item.stock}</div>
          <div class="stock-status ${statusClass}">${item.status}</div>
        </div>
        
        <div class="actions">
          <button class="btn btn-add" onclick="updateStock('${item.sku}', 1)">+ Add</button>
          <button class="btn btn-sub" onclick="updateStock('${item.sku}', -1)">- Use</button>
        </div>
      `;
      container.appendChild(card);
    });
  }

  function filterGrid() {
    const term = document.getElementById('search-input').value.toLowerCase();
    const filtered = globalInventory.filter(item => 
      item.name.toLowerCase().includes(term) || 
      item.sku.toLowerCase().includes(term) ||
      item.category.toLowerCase().includes(term)
    );
    renderGrid(filtered);
  }

  function showLoader() { document.getElementById('loader-overlay').style.display = 'flex'; }
  function hideLoader() { document.getElementById('loader-overlay').style.display = 'none'; }
  function onError(e) { alert("System Error: " + e.message); hideLoader(); }

</script>
</body>
</html>
4

Deployment & Security

Because we are handling sensitive business data, deployment settings are critical.

Critical Security Warning: Never deploy this as "Execute as User Accessing the Web App" if you want to protect your code or other sheets. Always "Execute as Me" if you want to control the backend environment, or "Execute as User" if you want to force every employee to have edit rights to the spreadsheet.

Recommended Deployment Settings

  1. Execute as: `Me (your@email.com)` - This means the script runs with YOUR permissions. The user does not need edit access to the underlying spreadsheet. This is the safest way to prevent users from messing up your formulas.
  2. Who has access: `Anyone with Google Account` (if internal corporate usage) or `Anyone` (if purely public).