Inventory Replenishment with Reorder Points and Lead Times

The Business Situation

Cedar Vale Industrial Distribution is a fictional 48-person distributor of maintenance supplies and industrial consumables. It operates three warehouses and maintains approximately 1,150 active stock-keeping units, resulting in about 2,700 active SKU-location combinations.

The supply chain team consists of a procurement manager, two buyers, an inventory analyst, and three warehouse supervisors. Warehouse employees record approximately 450 receipts, issues, transfers, and count adjustments on a typical business day.

The company already uses Google Workspace. Inventory movements are entered into location-specific Google Sheets, while buyers maintain a separate open purchase order spreadsheet. Reordering depends on visual stock checks, email conversations, and the personal judgment of experienced employees.

The process does not consistently combine current stock, open purchase orders, recent demand, supplier lead time, safety stock, and fixed ordering constraints. As a result, a buyer may place an order without seeing inbound stock, while another item may run out because its supplier has a longer lead time than staff remembered.

The business needs a controlled replenishment process without immediately purchasing a full warehouse management or enterprise resource planning platform. It also needs better location reporting and a practical path to migrate later if transaction volume or control requirements increase.

Note: This case study is provided as a representative example of the types of AI integration and digital transformation solutions Intelligex designs and delivers. Actual engagements are tailored to each client’s goals, constraints, existing systems, timeline, and available resources, so the approach, tools, and outcomes may vary.

The Existing Process

The original replenishment process follows this sequence:

1. Warehouse employees record receipts, issues, transfers, and count differences in separate location tabs.
2. A supervisor reviews the entries and periodically updates a summarized on-hand quantity.
3. Buyers open the inventory workbook and visually scan for quantities that appear low.
4. They check a separate purchase order sheet to determine whether stock is already inbound.
5. Recent demand is estimated from memory or by filtering transaction history manually.
6. Supplier lead time is taken from old purchase orders, email messages, or employee knowledge.
7. A buyer decides how much to order and emails the supplier.
8. The buyer adds the order to the open purchase order sheet.
9. Warehouse employees receive the goods and notify purchasing if the shipment is short or late.
10. Management receives an occasional spreadsheet summary, but no consistent location dashboard.

The spreadsheet itself is not the central problem. The primary issue is that it is being used as an informal collection of tabs rather than as a controlled transaction ledger, policy store, and calculation system.

What the New System Needed to Do

The project began by defining the required behavior independently of any particular tool.

The team also decided that automation would recommend and route replenishment work, but it would not send a supplier order or approve a high-value purchase without human confirmation.

Implementation Approaches Considered

Improved spreadsheet formulas

A formula-based workbook could calculate reorder points and highlight low stock. This would be appropriate for a smaller catalog with few daily movements and one purchasing location. It would not adequately address mobile transaction capture, duplicate events, controlled approvals, or reliable alerting for Cedar Vale’s three warehouses.

Google Workspace application

This approach retains the company’s existing Google environment while adding AppSheet for controlled mobile forms, Apps Script for calculation and workflow logic, and Looker Studio for reporting. It provides enough customization for location-level replenishment without requiring a separate database server.

Airtable

Airtable could provide a more database-like user experience, linked records, interfaces, and native automations. It remained a credible option, particularly if users preferred maintaining policy and purchasing records in one no-code application. The project team would still need to evaluate record growth, transaction retention, attachment storage, permissions, and recurring automation usage against current product terms.

BI dashboard only

A dashboard could expose low stock and overdue orders, but reporting cannot repair incomplete source data. A dashboard layered over inconsistent transaction tabs would make the existing uncertainty more visible without removing it.

Dedicated inventory software

A dedicated inventory or warehouse management system would provide stronger transaction controls, receiving, purchasing, location management, and potentially barcode support. It was not selected for this representative scenario because the business first wanted to standardize its policies and operating process. The resulting data model would also make a later migration more controlled.

The Selected Solution

Cedar Vale selected a Google Workspace-based replenishment application connecting Google Sheets, AppSheet, Apps Script, Google Drive, Gmail, and Looker Studio.

The selected design retained Google Sheets because employees already understood it and because the modeled volume remained reasonable when the active transaction ledger was archived on a controlled schedule. AppSheet removed direct end-user editing from the critical transaction sheets. Apps Script supplied logic that would be difficult to maintain as nested spreadsheet formulas.

System Architecture and Data Flow

AppSheet acts as the controlled user interface, while Google Sheets stores the underlying records. A time-driven Apps Script trigger reads new transactions, rebuilds inventory balances, reconciles purchase orders, and creates or updates recommendations. Looker Studio reads the calculated sheets rather than reproducing business logic in the dashboard.

1. Movement submission: A warehouse employee submits a receipt, issue, transfer, or count through AppSheet. AppSheet validates required fields and writes the row to the Transactions sheet.
2. Automation trigger: Apps Script runs every 15 minutes. A script lock prevents overlapping runs.
3. Input validation: The script checks event keys, transaction types, timestamps, quantities, SKUs, locations, and active SKU-location policies. Invalid rows receive an error status and remain visible for correction.
4. Chronological processing: Accepted transactions are ordered by timestamp and row number. Receipts add stock, issues subtract stock, transfers affect two locations, and counts set the balance to the observed quantity.
5. Purchase order reconciliation: Receipt quantities associated with a purchase order line update received quantity, open quantity, and purchase order status.
6. Demand calculation: Customer issues within each policy’s lookback period are divided by the number of calendar days in that period.
7. Reorder calculation: The script calculates reorder point, inventory position, suggested order quantity, days of cover, and replenishment status.
8. Recommendation control: The script updates an active recommendation or creates one when no active recommendation exists for the SKU-location combination.
9. Notification: New recommendations are grouped by buyer. Pending work receives reminders and overdue work is escalated.
10. Dashboard update: Looker Studio reads the refreshed InventoryBalances, ReorderRecommendations, PurchaseOrders, and Transactions sheets according to the report’s data-freshness configuration.
11. Failure path: The automation writes a failed run to AutomationLog, leaves unprocessed alerts eligible for retry, and notifies the designated support owner when email service is available.

• Intake: AppSheet forms for receipts, issues, transfers, counts, purchase orders, and recommendation review
• System of record: A controlled Google Sheets workbook
• Automation layer: Google Apps Script with a 15-minute time-driven trigger
• Document storage: A restricted Google Drive application folder
• Notifications: Grouped email messages sent through Apps Script
• Reporting: Looker Studio connected to calculated Google Sheets tabs
• AI layer: Optional structured extraction of supplier delivery updates, always subject to human review

Data Structure

The workbook uses separate sheets for master data, policies, transactions, purchasing, calculated results, and audit logs. The SKU and location fields form a logical one-to-many relationship with transactions and purchase order lines. A PolicyKey, formatted as SKU|LOCATION, identifies one inventory policy.

The system does not use editable summary totals as the source of truth. InventoryBalances is a materialized calculation table that Apps Script replaces after processing. Users correct source transactions or enter a controlled count rather than editing calculated balances.

Workflow Statuses and Ownership

A recommendation can move backward from REVIEW to NEW when required information is removed, or from AWAITING_APPROVAL to REVIEW when an approver requests more information. Rejection requires a reason. Negative stock, missing opening balances, unsupported transaction types, and invalid policy values enter a manual-review queue rather than being silently corrected.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create a dedicated Google Sheets workbook named Inventory Replenishment Control.
2. Store it in a controlled Google Drive location owned by the business rather than an individual employee.
3. Create or confirm AppSheet access for warehouse, procurement, inventory, and administrator users. Required licensing depends on the authentication, security, automation, and governance features selected at implementation time.
4. Create a Looker Studio report owned by a reporting administrator or controlled business account.
5. Identify a purchasing alert mailbox, an escalation mailbox, a primary automation owner, and a backup owner.
6. Give the Apps Script project permission to read and write the workbook and send email. The optional AI enhancement also requires Gmail and external-request permissions.
7. Restrict direct spreadsheet editing to the implementation team, automation owners, and a small number of data administrators. Routine users should work through AppSheet.
8. Create test users for warehouse, buyer, approver, inventory analyst, and read-only reporting roles.
9. Copy the production workbook to a separate test file. Use different spreadsheet IDs and test email addresses so that test alerts cannot reach operational recipients.

No separate API credential is needed for the core integration because AppSheet, Sheets, Apps Script, Drive, and Looker Studio use authenticated Google connections. The optional AI integration uses an API key stored in Apps Script Properties, never in a worksheet or source file.

Step 2: Build the Intake

Add the Google Sheets workbook to AppSheet and include Items, Locations, ItemLocationPolicies, Transactions, PurchaseOrders, ReorderRecommendations, and Users as application tables. Interface labels can vary as AppSheet evolves, but the required data behavior remains the same.

Use separate AppSheet form views for receipts, issues, transfers, and counts so the transaction type can be preselected. Use conditional visibility so employees see only fields relevant to the selected movement.

Representative AppSheet expressions include:

TransactionID initial value:
UNIQUEID()

EventKey initial value:
CONCATENATE([TransactionID], ":", [Type])

Timestamp initial value:
NOW()

UserEmail initial value:
USEREMAIL()

Quantity Valid_If:
AND(ISNOTBLANK([_THIS]), [_THIS] > 0)

CountedQuantity Valid_If:
AND(ISNOTBLANK([_THIS]), [_THIS] >= 0)

LocationFrom required condition:
IN([Type], {"ISSUE", "TRANSFER"})

LocationTo required condition:
IN([Type], {"RECEIPT", "TRANSFER", "COUNT"})

Transfer locations Valid_If:
[LocationFrom] <> [LocationTo]

AppSheet validation improves data quality but does not replace server-side validation. Mobile users can work with stale or temporarily offline data, so Apps Script validates every synchronized row again.

The confirmation message should state that the movement has been submitted and may remain in NEW status until the next automation run. Incomplete submissions should remain unsaved rather than being written with placeholder values. Require sign-in and limit access to approved users to reduce spam and unauthorized entries.

Step 3: Create the System of Record

Create the following worksheets. The provided Apps Script can initialize these headers automatically:

• Items
• Locations
• Suppliers
• ItemLocationPolicies
• OpeningBalances
• Transactions
• PurchaseOrders
• InventoryBalances
• ReorderRecommendations
• AutomationLog
• Users

Use uppercase, stable identifiers such as SKU-00124, WH-01, and SUP-0042. Do not use descriptions as keys because descriptions can change.

Build PolicyKey with the following logical format:

SKU|LOCATION

Example:
BRG-6204|WH-02

The replenishment calculation is:

Average daily demand =
Issue quantity during lookback period / Lookback calendar days

Calculated reorder point =
CEILING(Average daily demand * Lead time days + Safety stock)

Inventory position =
On-hand quantity + Open purchase order quantity

Reorder condition =
Inventory position <= Reorder point

Suggested order quantity before rounding =
MAX(
  Fixed reorder quantity,
  Minimum order quantity,
  Reorder point + Fixed reorder quantity - Inventory position
)

Final suggested order quantity =
Suggested quantity rounded up to the supplier order multiple

A ReorderPointOverride is permitted only when an authorized employee documents the reason. Examples include seasonal demand, contractual service stock, known product discontinuation, or a planned promotion. The override should have an owner and review date even if those governance fields are maintained in an adjacent policy register.

Create filtered views or filter views for active policies, invalid policies, transaction errors, negative stock, new recommendations, approval work, late purchase orders, and failed automation runs. Protect calculated sheets and automation-managed columns from direct editing.

Step 4: Connect the Tools

AppSheet and Looker Studio authenticate through Google connections. Apps Script is bound to the inventory workbook and opens it using the configured spreadsheet ID. No webhook is required for the core implementation.

A time-driven trigger was selected instead of relying on a spreadsheet edit trigger. Writes made by applications and APIs do not always behave like direct user edits, and a scheduled batch provides more predictable processing. A 15-minute interval also groups alerts and reduces unnecessary recalculation.

Step 5: Build the Core Automation

The core automation performs the following sequence:

1. Acquire a script lock.
2. Validate workbook configuration and headers.
3. Read active items, policies, opening balances, transactions, and purchase orders.
4. Reject duplicate EventKeys.
5. Validate each transaction and mark invalid records.
6. Sort valid transactions chronologically.
7. Apply movements to opening balances.
8. Calculate demand from ISSUE transactions.
9. Reconcile purchase order receipts and open quantities.
10. Calculate inventory positions and reorder points.
11. Replace the materialized InventoryBalances table.
12. Create or update one active recommendation per PolicyKey.
13. Send grouped initial alerts, reminders, and escalations.
14. Write an AutomationLog record.
15. Release the script lock.

• Trigger: Time-driven Apps Script trigger every 15 minutes
• Conditions: Active policy, valid movement, nonduplicate EventKey, and valid quantity
• Actions: Rebuild balances, reconcile POs, calculate replenishment, update recommendations, and send notifications
• Fields updated: Transaction status, PO received quantity, balance fields, recommendation metrics, notification timestamps, and run log
• Notification: Grouped email by owner rather than one message per SKU
• Exception: Invalid rows are marked ERROR or DUPLICATE and remain available for correction

Recommendations are idempotent at the PolicyKey level. If an active recommendation already exists, the automation updates its current stock, open order quantity, reorder point, suggested quantity, estimated value, and reason. It does not create a second active recommendation.

Users recover a failed transaction by correcting the source fields and changing ProcessStatus to NEW through an authorized AppSheet action. The next automation run validates it again.

Step 6: Add Approvals, Reminders, and Escalations

Use a representative approval threshold of $2,500 for the case study. The business must replace this amount with its approved purchasing authority policy.

1. Every recommendation is reviewed by the assigned buyer.
2. If EstimatedValue is at or below $2,500, ApprovalStatus is NOT_REQUIRED. The buyer can approve or dismiss the recommendation.
3. If EstimatedValue exceeds $2,500, ApprovalStatus begins as NOT_REQUESTED.
4. The buyer uses an AppSheet action to change Status to AWAITING_APPROVAL and ApprovalStatus to PENDING.
5. The procurement manager approves, rejects, or returns the request for more information.
6. Approval records ApprovedBy and ApprovedAt. Rejection requires Notes and changes ApprovalStatus to REJECTED.
7. Approved recommendations can be converted into a PurchaseOrders row with an AppSheet action that copies SKU, location, supplier, suggested quantity, and buyer.
8. The buyer enters or confirms the supplier PO number and expected date before changing the PO status to ORDERED.

For an unavailable approver, the Users table should contain a designated backup approver. Reassignment must be an explicit action that records the new owner and a reason. Parallel approval is not required in this representative scenario. If finance or technical approval is later needed, separate approval records should be introduced rather than adding multiple approval columns to the recommendation row.

• Initial alert: Sent when a recommendation has no LastNotifiedAt value.
• Reminder: Sent after 24 hours for NEW, REVIEW, AWAITING_APPROVAL, or APPROVED work that remains incomplete.
• Escalation: Sent after 48 hours if EscalatedAt is blank.
• Final confirmation: AppSheet displays the created PO line and recommendation status to the buyer.
• Approval evidence: RecommendationID, status, approver, timestamp, notes, PO number, and automation run ID remain in the record.

Step 7: Add Documents and File Management

Create a dedicated Google Drive application folder with restricted business ownership. AppSheet file and image fields store an attachment reference in the transaction row and place the uploaded file in the configured application data folder.

Use this logical structure where the selected AppSheet storage configuration supports controlled subfolders:

InventoryApp/
  Transactions/
    Receipts/
    Counts/
    Transfers/
  PurchaseOrders/
  Archived/
  Exports/

Use the transaction ID, document type, and date in exported or manually renamed files, for example:

TXN-A84F2C_RECEIPT_2026-07-15.pdf
TXN-D21A90_COUNT_2026-07-15.jpg
PO-CVP-1048_CONFIRMATION.pdf

• Do not use publicly accessible links.
• Restrict warehouse users to attachments required for their locations.
• Keep the relative attachment path in Transactions.Attachment.
• Do not replace an original count document silently. Upload a new version and record the correction in Notes.
• Reject unsupported file types and define a practical file-size policy before rollout.
• Show missing required evidence in an incomplete-record view.
• When an upload fails, retain the transaction with an attachment exception rather than asking the employee to re-enter the movement.
• Archive files according to the company’s accounting, operational, and regulatory retention requirements.

Step 8: Add Reporting and Operational Views

Create separate Looker Studio data sources for InventoryBalances, ReorderRecommendations, PurchaseOrders, and Transactions. Each data source should point to one worksheet with a stable header row.

Dashboard controls should include Location, Buyer, Supplier, Category, ReplenishmentStatus, and date. Useful scorecards include stockout count, reorder count, estimated recommendation value, open PO quantity, overdue PO lines, manual-review count, and failed runs.

Looker Studio should consume the calculated fields from InventoryBalances instead of recreating the reorder formula. This ensures that AppSheet, email alerts, and dashboards use the same business result.

The dashboard owner should review data-source credentials, freshness settings, field types, and failed charts after structural changes. A report refresh is not a substitute for a completed Apps Script run.

Step 9: Add Security and Governance Controls

• Require authenticated access to AppSheet and Looker Studio.
• Use the Users table to assign roles such as Warehouse, Buyer, Approver, Analyst, Administrator, and Viewer.
• Use AppSheet security filters for row-level restrictions. Slices alone are interface filters and should not be treated as a security boundary.
• Give routine users no direct edit access to InventoryBalances, AutomationLog, or automation-managed recommendation fields.
• Protect spreadsheet ranges containing formulas, policy overrides, status timestamps, and error details.
• Store API keys and other secrets in Apps Script Properties.
• Restrict Drive attachments to authorized users and disable broad link sharing.
• Review Apps Script execution history and AutomationLog records.
• Remove former employees from Google groups, AppSheet access, Drive permissions, Looker Studio access, and approval assignments.
• Maintain a backup export or controlled copy of master data, active policies, opening balances, open POs, and active recommendations.
• Document retention requirements for transaction and purchasing records.
• Do not send sensitive personal, payment, legal, or regulated information to an AI service unless an approved governance review permits it.
• Require human approval for purchase commitments, policy overrides, write-offs, and negative-stock corrections.

Step 10: Deploy and Test

1. Build the workbook and script in the test environment.
2. Load a small set of representative SKUs, locations, policies, opening balances, and POs.
3. Create test users for every role.
4. Run the script manually before installing the trigger.
5. Compare calculated balances with hand-calculated results for at least 20 SKU-location combinations.
6. Test receipts, issues, transfers, counts, duplicates, invalid transactions, partial receipts, and cancelled orders.
7. Complete user acceptance testing with one warehouse supervisor, one buyer, the inventory analyst, and the procurement manager.
8. Pilot the application at one warehouse for two weeks while maintaining a documented reconciliation against the prior process.
9. Correct data and workflow issues before adding the remaining locations.
10. Activate the time-driven trigger only after recipient email addresses and approval assignments are verified.
11. Publish the Looker Studio report to the intended groups with view-only access where appropriate.
12. Provide one-page operating guides for movement entry, buyer review, approval, error correction, and failed-run response.
13. Maintain a rollback copy of the pre-launch workbook and opening balances. If processing must be suspended, disable the trigger, preserve submitted transactions, and continue controlled entry until reconciliation is complete.

Code and Configuration

The core solution requires Apps Script because the inventory calculation, duplicate detection, purchase order reconciliation, idempotent recommendations, retries, and grouped notifications are more maintainable in code than in nested spreadsheet formulas.

Place the following script in the Apps Script project attached to the Inventory Replenishment Control workbook. Open the workbook, open the Apps Script editor from the Extensions menu, replace the default file contents, and paste the complete script below.

var CONFIG = {
  spreadsheetId: 'YOUR_SPREADSHEET_ID',
  purchasingEmail: 'YOUR_EMAIL_ADDRESS',
  escalationEmail: 'YOUR_EMAIL_ADDRESS',
  triggerMinutes: 15,
  approvalThreshold: 2500,
  retryAttempts: 3,
  sheets: {
    items: 'Items',
    locations: 'Locations',
    suppliers: 'Suppliers',
    policies: 'ItemLocationPolicies',
    openings: 'OpeningBalances',
    transactions: 'Transactions',
    purchaseOrders: 'PurchaseOrders',
    balances: 'InventoryBalances',
    recommendations: 'ReorderRecommendations',
    log: 'AutomationLog',
    users: 'Users'
  }
};

var REQUIRED_HEADERS = {
  Items: [
    'SKU', 'Description', 'Category', 'UnitOfMeasure',
    'EstimatedUnitCost', 'Active'
  ],
  Locations: [
    'Location', 'LocationName', 'SupervisorEmail', 'Active'
  ],
  Suppliers: [
    'SupplierID', 'SupplierName', 'DefaultLeadTimeDays',
    'BuyerEmail', 'Active'
  ],
  ItemLocationPolicies: [
    'PolicyKey', 'SKU', 'Location', 'SupplierID',
    'LeadTimeDays', 'DemandLookbackDays', 'SafetyStock',
    'ReorderPointOverride', 'ReorderQty', 'MinOrderQty',
    'OrderMultiple', 'MaxStock', 'BuyerEmail', 'Active'
  ],
  OpeningBalances: [
    'PolicyKey', 'SKU', 'Location', 'OpeningQty', 'AsOfDate'
  ],
  Transactions: [
    'TransactionID', 'EventKey', 'Timestamp', 'Type', 'SKU',
    'LocationFrom', 'LocationTo', 'Quantity', 'CountedQuantity',
    'POLineID', 'Reference', 'Attachment', 'UserEmail', 'Notes',
    'ProcessStatus', 'ProcessedAt', 'ErrorMessage'
  ],
  PurchaseOrders: [
    'POLineID', 'PONumber', 'SupplierID', 'SKU', 'Location',
    'OrderedQty', 'ReceivedBeforeGoLiveQty', 'ReceivedQty',
    'OpenQty', 'ExpectedDate', 'Status', 'CreatedDate',
    'BuyerEmail', 'LastUpdated', 'AutomationStatus', 'ErrorMessage'
  ],
  InventoryBalances: [
    'PolicyKey', 'SKU', 'Location', 'SupplierID', 'OnHand',
    'OpenPOQty', 'InventoryPosition', 'AvgDailyDemand',
    'DemandLookbackDays', 'LeadTimeDays', 'SafetyStock',
    'ReorderPoint', 'ReorderQty', 'SuggestedOrderQty',
    'DaysOfCover', 'ReplenishmentStatus', 'LastCalculated',
    'ExceptionMessage'
  ],
  ReorderRecommendations: [
    'RecommendationID', 'PolicyKey', 'CreatedAt', 'SKU',
    'Location', 'SupplierID', 'OnHand', 'OpenPOQty',
    'ReorderPoint', 'SuggestedQty', 'EstimatedValue',
    'Reason', 'Status', 'BuyerEmail', 'ApproverEmail',
    'ApprovalStatus', 'ApprovedBy', 'ApprovedAt',
    'LastNotifiedAt', 'LastReminderAt', 'EscalatedAt',
    'PONumber', 'Notes', 'AutomationRunID'
  ],
  AutomationLog: [
    'RunID', 'StartedAt', 'FinishedAt', 'Status', 'Step',
    'RecordsProcessed', 'Message'
  ],
  Users: [
    'Email', 'Role', 'Location', 'BackupApproverEmail', 'Active'
  ]
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Inventory Automation')
    .addItem('Initialize workbook', 'initializeInventoryWorkbook')
    .addItem('Run now', 'runInventoryAutomation')
    .addItem('Install trigger', 'setupInventoryAutomation')
    .addToUi();
}

function initializeInventoryWorkbook() {
  validateBaseConfiguration_();
  var book = getBook_();

  Object.keys(REQUIRED_HEADERS).forEach(function(sheetName) {
    var sheet = book.getSheetByName(sheetName);
    if (!sheet) {
      sheet = book.insertSheet(sheetName);
    }

    if (sheet.getLastRow() === 0) {
      sheet.getRange(1, 1, 1, REQUIRED_HEADERS[sheetName].length)
        .setValues([REQUIRED_HEADERS[sheetName]]);
      sheet.setFrozenRows(1);
    }
  });

  validateTables_();
  console.log('Workbook initialized and validated.');
}

function setupInventoryAutomation() {
  initializeInventoryWorkbook();
  validateConfiguration_();

  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    if (trigger.getHandlerFunction() === 'runInventoryAutomation') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger('runInventoryAutomation')
    .timeBased()
    .everyMinutes(CONFIG.triggerMinutes)
    .create();

  console.log('A time-driven trigger was installed.');
}

function runInventoryAutomation() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) {
    throw new Error('Another inventory automation run is still active.');
  }

  var runId = 'RUN-' + Utilities.getUuid();
  var startedAt = new Date();
  var currentStep = 'START';
  var processedCount = 0;

  try {
    validateConfiguration_();
    validateTables_();

    currentStep = 'CALCULATE_INVENTORY';
    var calculation = withRetry_(currentStep, function() {
      return calculateInventory_(runId);
    });
    processedCount = calculation.transactionCount;

    currentStep = 'RECONCILE_RECOMMENDATIONS';
    withRetry_(currentStep, function() {
      reconcileRecommendations_(calculation.balances, runId);
      return true;
    });

    currentStep = 'SEND_NOTIFICATIONS';
    withRetry_(currentStep, function() {
      sendRecommendationNotifications_();
      return true;
    });

    currentStep = 'COMPLETE';
    logRun_(runId, startedAt, new Date(), 'SUCCESS', currentStep,
      processedCount,
      'Inventory balances, purchase orders, and recommendations processed.');

    console.log(JSON.stringify({
      runId: runId,
      status: 'SUCCESS',
      transactionsProcessed: processedCount,
      balancesWritten: calculation.balances.length
    }));

    return {
      runId: runId,
      status: 'SUCCESS',
      transactionsProcessed: processedCount,
      balancesWritten: calculation.balances.length
    };
  } catch (error) {
    logRunSafe_(runId, startedAt, new Date(), 'FAILED', currentStep,
      processedCount, sanitizeError_(error));

    sendFailureNoticeSafe_(runId, currentStep, error);
    console.error(error.stack || error.message);
    throw error;
  } finally {
    lock.releaseLock();
  }
}

function calculateInventory_(runId) {
  var now = new Date();
  var itemsTable = readTable_(CONFIG.sheets.items);
  var policiesTable = readTable_(CONFIG.sheets.policies);
  var openingsTable = readTable_(CONFIG.sheets.openings);
  var transactionsTable = readTable_(CONFIG.sheets.transactions);
  var poTable = readTable_(CONFIG.sheets.purchaseOrders);

  var itemsBySku = {};
  itemsTable.objects.forEach(function(item) {
    var sku = normalize_(item.SKU);
    if (sku && asBoolean_(item.Active)) {
      itemsBySku[sku] = item;
    }
  });

  var policiesByKey = {};
  policiesTable.objects.forEach(function(policy) {
    var sku = normalize_(policy.SKU);
    var location = normalize_(policy.Location);
    var expectedKey = makePolicyKey_(sku, location);
    var suppliedKey = normalize_(policy.PolicyKey);

    if (!asBoolean_(policy.Active)) {
      return;
    }

    if (suppliedKey !== expectedKey) {
      throw new Error(
        'PolicyKey mismatch for ' + suppliedKey +
        '. Expected ' + expectedKey + '.'
      );
    }

    if (policiesByKey[suppliedKey]) {
      throw new Error('Duplicate active policy: ' + suppliedKey);
    }

    policiesByKey[suppliedKey] = policy;
  });

  var openingsByKey = {};
  openingsTable.objects.forEach(function(opening) {
    var key = normalize_(opening.PolicyKey);
    if (!key) {
      key = makePolicyKey_(opening.SKU, opening.Location);
    }

    if (openingsByKey[key]) {
      throw new Error('Duplicate opening balance: ' + key);
    }

    var qty = toNumber_(opening.OpeningQty);
    var asOf = toDate_(opening.AsOfDate);

    if (qty === null || !asOf) {
      throw new Error('Invalid opening balance for ' + key);
    }

    openingsByKey[key] = {
      qty: qty,
      asOf: asOf
    };
  });

  var onHandByKey = {};
  Object.keys(policiesByKey).forEach(function(key) {
    onHandByKey[key] = openingsByKey[key] ?
      openingsByKey[key].qty : 0;
  });

  var acceptedTransactions = [];
  var seenEvents = {};
  var receiptQtyByPoLine = {};
  var demandByKey = {};

  transactionsTable.objects.forEach(function(tx) {
    tx.ErrorMessage = '';

    var eventKey = normalize_(tx.EventKey);
    if (!eventKey) {
      markTransactionError_(tx, 'EventKey is required.', now);
      return;
    }

    if (seenEvents[eventKey]) {
      tx.ProcessStatus = 'DUPLICATE';
      tx.ProcessedAt = tx.ProcessedAt || now;
      tx.ErrorMessage = 'Duplicate EventKey. Original row: ' +
        seenEvents[eventKey];
      return;
    }
    seenEvents[eventKey] = tx._row;

    var timestamp = toDate_(tx.Timestamp);
    var type = normalize_(tx.Type);
    var sku = normalize_(tx.SKU);
    var fromLocation = normalize_(tx.LocationFrom);
    var toLocation = normalize_(tx.LocationTo);
    var quantity = toNumber_(tx.Quantity);
    var countedQuantity = toNumber_(tx.CountedQuantity);

    if (!normalize_(tx.TransactionID)) {
      markTransactionError_(tx, 'TransactionID is required.', now);
      return;
    }

    if (!timestamp) {
      markTransactionError_(tx, 'A valid Timestamp is required.', now);
      return;
    }

    if (!itemsBySku[sku]) {
      markTransactionError_(tx, 'SKU is missing or inactive.', now);
      return;
    }

    if (['RECEIPT', 'ISSUE', 'TRANSFER', 'COUNT'].indexOf(type) === -1) {
      markTransactionError_(tx, 'Unsupported transaction Type.', now);
      return;
    }

    var keys = [];

    if (type === 'RECEIPT') {
      keys = [makePolicyKey_(sku, toLocation)];
      if (!toLocation || quantity === null || quantity <= 0) {
        markTransactionError_(
          tx,
          'RECEIPT requires LocationTo and positive Quantity.',
          now
        );
        return;
      }
    }

    if (type === 'ISSUE') {
      keys = [makePolicyKey_(sku, fromLocation)];
      if (!fromLocation || quantity === null || quantity <= 0) {
        markTransactionError_(
          tx,
          'ISSUE requires LocationFrom and positive Quantity.',
          now
        );
        return;
      }
    }

    if (type === 'TRANSFER') {
      keys = [
        makePolicyKey_(sku, fromLocation),
        makePolicyKey_(sku, toLocation)
      ];
      if (!fromLocation || !toLocation ||
          fromLocation === toLocation ||
          quantity === null || quantity <= 0) {
        markTransactionError_(
          tx,
          'TRANSFER requires different locations and positive Quantity.',
          now
        );
        return;
      }
    }

    if (type === 'COUNT') {
      keys = [makePolicyKey_(sku, toLocation)];
      if (!toLocation ||
          countedQuantity === null ||
          countedQuantity < 0) {
        markTransactionError_(
          tx,
          'COUNT requires LocationTo and non-negative CountedQuantity.',
          now
        );
        return;
      }
    }

    var missingPolicy = keys.some(function(key) {
      return !policiesByKey[key];
    });

    if (missingPolicy) {
      markTransactionError_(
        tx,
        'One or more active SKU-location policies are missing.',
        now
      );
      return;
    }

    tx.ProcessStatus = 'PROCESSED';
    tx.ProcessedAt = tx.ProcessedAt || now;
    tx.ErrorMessage = '';

    acceptedTransactions.push({
      row: tx._row,
      timestamp: timestamp,
      type: type,
      sku: sku,
      fromLocation: fromLocation,
      toLocation: toLocation,
      quantity: quantity,
      countedQuantity: countedQuantity,
      poLineId: normalize_(tx.POLineID)
    });
  });

  acceptedTransactions.sort(function(a, b) {
    var difference = a.timestamp.getTime() - b.timestamp.getTime();
    return difference !== 0 ? difference : a.row - b.row;
  });

  acceptedTransactions.forEach(function(tx) {
    if (tx.type === 'RECEIPT') {
      var receiptKey = makePolicyKey_(tx.sku, tx.toLocation);
      if (shouldApplyAfterOpening_(
          openingsByKey, receiptKey, tx.timestamp)) {
        onHandByKey[receiptKey] += tx.quantity;
      }

      if (tx.poLineId) {
        receiptQtyByPoLine[tx.poLineId] =
          (receiptQtyByPoLine[tx.poLineId] || 0) + tx.quantity;
      }
    }

    if (tx.type === 'ISSUE') {
      var issueKey = makePolicyKey_(tx.sku, tx.fromLocation);
      if (shouldApplyAfterOpening_(
          openingsByKey, issueKey, tx.timestamp)) {
        onHandByKey[issueKey] -= tx.quantity;
      }

      var issuePolicy = policiesByKey[issueKey];
      var lookbackDays = toNumber_(issuePolicy.DemandLookbackDays) || 90;
      var cutoff = now.getTime() -
        lookbackDays * 24 * 60 * 60 * 1000;

      if (tx.timestamp.getTime() >= cutoff) {
        demandByKey[issueKey] =
          (demandByKey[issueKey] || 0) + tx.quantity;
      }
    }

    if (tx.type === 'TRANSFER') {
      var transferFromKey =
        makePolicyKey_(tx.sku, tx.fromLocation);
      var transferToKey =
        makePolicyKey_(tx.sku, tx.toLocation);

      if (shouldApplyAfterOpening_(
          openingsByKey, transferFromKey, tx.timestamp)) {
        onHandByKey[transferFromKey] -= tx.quantity;
      }

      if (shouldApplyAfterOpening_(
          openingsByKey, transferToKey, tx.timestamp)) {
        onHandByKey[transferToKey] += tx.quantity;
      }
    }

    if (tx.type === 'COUNT') {
      var countKey = makePolicyKey_(tx.sku, tx.toLocation);
      if (shouldApplyAfterOpening_(
          openingsByKey, countKey, tx.timestamp)) {
        onHandByKey[countKey] = tx.countedQuantity;
      }
    }
  });

  writeObjectColumns_(transactionsTable, [
    'ProcessStatus', 'ProcessedAt', 'ErrorMessage'
  ]);

  var openPoByKey = {};
  var seenPoLines = {};

  poTable.objects.forEach(function(po) {
    po.ErrorMessage = '';
    po.AutomationStatus = 'OK';

    var poLineId = normalize_(po.POLineID);
    var sku = normalize_(po.SKU);
    var location = normalize_(po.Location);
    var key = makePolicyKey_(sku, location);
    var orderedQty = toNumber_(po.OrderedQty);
    var priorReceived =
      toNumber_(po.ReceivedBeforeGoLiveQty) || 0;
    var status = normalize_(po.Status);

    if (!poLineId || seenPoLines[poLineId]) {
      po.AutomationStatus = 'ERROR';
      po.ErrorMessage = !poLineId ?
        'POLineID is required.' :
        'Duplicate POLineID.';
      return;
    }
    seenPoLines[poLineId] = true;

    if (!policiesByKey[key] ||
        orderedQty === null ||
        orderedQty <= 0) {
      po.AutomationStatus = 'ERROR';
      po.ErrorMessage =
        'Valid policy and positive OrderedQty are required.';
      return;
    }

    var receivedQty =
      priorReceived + (receiptQtyByPoLine[poLineId] || 0);
    var openQty = Math.max(orderedQty - receivedQty, 0);

    if (status === 'CANCELLED') {
      openQty = 0;
    } else if (receivedQty >= orderedQty) {
      status = 'CLOSED';
    } else if (receivedQty > 0) {
      status = 'PART_RECEIVED';
    }

    if (receivedQty > orderedQty) {
      po.AutomationStatus = 'WARNING';
      po.ErrorMessage = 'Received quantity exceeds ordered quantity.';
    }

    po.ReceivedQty = receivedQty;
    po.OpenQty = openQty;
    po.Status = status;
    po.LastUpdated = now;

    if (['ORDERED', 'PART_RECEIVED'].indexOf(status) !== -1) {
      openPoByKey[key] = (openPoByKey[key] || 0) + openQty;
    }
  });

  writeObjectColumns_(poTable, [
    'ReceivedQty', 'OpenQty', 'Status', 'LastUpdated',
    'AutomationStatus', 'ErrorMessage'
  ]);

  var balances = [];

  Object.keys(policiesByKey).sort().forEach(function(key) {
    var policy = policiesByKey[key];
    var sku = normalize_(policy.SKU);
    var location = normalize_(policy.Location);
    var errors = [];

    var leadTime = toNumber_(policy.LeadTimeDays);
    var lookback = toNumber_(policy.DemandLookbackDays);
    var safetyStock = toNumber_(policy.SafetyStock);
    var reorderQty = toNumber_(policy.ReorderQty);
    var minOrderQty = toNumber_(policy.MinOrderQty);
    var orderMultiple = toNumber_(policy.OrderMultiple);
    var maxStock = toNumber_(policy.MaxStock);

    if (!itemsBySku[sku]) {
      errors.push('Item is missing or inactive.');
    }
    if (!openingsByKey[key]) {
      errors.push('Opening balance is missing; zero was assumed.');
    }
    if (leadTime === null || leadTime < 0) {
      errors.push('LeadTimeDays is invalid.');
    }
    if (lookback === null || lookback <= 0) {
      errors.push('DemandLookbackDays is invalid.');
    }
    if (safetyStock === null || safetyStock < 0) {
      errors.push('SafetyStock is invalid.');
    }
    if (reorderQty === null || reorderQty <= 0) {
      errors.push('ReorderQty is invalid.');
    }
    if (minOrderQty === null || minOrderQty < 0) {
      errors.push('MinOrderQty is invalid.');
    }
    if (orderMultiple === null || orderMultiple <= 0) {
      errors.push('OrderMultiple is invalid.');
    }

    leadTime = leadTime === null ? 0 : leadTime;
    lookback = lookback === null || lookback <= 0 ? 90 : lookback;
    safetyStock = safetyStock === null ? 0 : safetyStock;
    reorderQty = reorderQty === null ? 0 : reorderQty;
    minOrderQty = minOrderQty === null ? 0 : minOrderQty;
    orderMultiple =
      orderMultiple === null || orderMultiple <= 0 ?
      1 : orderMultiple;
    maxStock = maxStock === null ? 0 : maxStock;

    var demandQty = demandByKey[key] || 0;
    var averageDailyDemand = demandQty / lookback;
    var override = toNumber_(policy.ReorderPointOverride);
    var reorderPoint = override !== null ?
      override :
      Math.ceil(averageDailyDemand * leadTime + safetyStock);

    var onHand = onHandByKey[key] || 0;
    var openPoQty = openPoByKey[key] || 0;
    var inventoryPosition = onHand + openPoQty;
    var suggestedQty = 0;
    var status = 'HEALTHY';

    if (errors.length > 0) {
      status = 'MANUAL_REVIEW';
    } else if (onHand <= 0) {
      status = inventoryPosition > reorderPoint ?
        'INBOUND_COVERED' : 'STOCKOUT';
    } else if (inventoryPosition <= reorderPoint) {
      status = 'REORDER';
    } else if (maxStock > 0 && onHand > maxStock) {
      status = 'EXCESS';
    }

    if (errors.length === 0 &&
        inventoryPosition <= reorderPoint) {
      var unrounded = Math.max(
        reorderQty,
        minOrderQty,
        reorderPoint + reorderQty - inventoryPosition
      );
      suggestedQty = roundUpToMultiple_(
        unrounded, orderMultiple
      );
    }

    if (onHand < 0) {
      errors.push(
        'Negative on-hand balance; verify issues and counts.'
      );
    }

    balances.push({
      PolicyKey: key,
      SKU: sku,
      Location: location,
      SupplierID: normalize_(policy.SupplierID),
      OnHand: onHand,
      OpenPOQty: openPoQty,
      InventoryPosition: inventoryPosition,
      AvgDailyDemand: round_(averageDailyDemand, 4),
      DemandLookbackDays: lookback,
      LeadTimeDays: leadTime,
      SafetyStock: safetyStock,
      ReorderPoint: reorderPoint,
      ReorderQty: reorderQty,
      SuggestedOrderQty: suggestedQty,
      DaysOfCover: averageDailyDemand > 0 ?
        round_(Math.max(onHand, 0) / averageDailyDemand, 2) :
        '',
      ReplenishmentStatus: status,
      LastCalculated: now,
      ExceptionMessage: errors.join(' ')
    });
  });

  replaceTableData_(CONFIG.sheets.balances, balances);

  return {
    runId: runId,
    transactionCount: acceptedTransactions.length,
    balances: balances
  };
}

function reconcileRecommendations_(balances, runId) {
  var now = new Date();
  var recommendations =
    readTable_(CONFIG.sheets.recommendations);
  var items = readTable_(CONFIG.sheets.items);
  var policies = readTable_(CONFIG.sheets.policies);

  var itemCostBySku = {};
  items.objects.forEach(function(item) {
    itemCostBySku[normalize_(item.SKU)] =
      toNumber_(item.EstimatedUnitCost) || 0;
  });

  var policyByKey = {};
  policies.objects.forEach(function(policy) {
    if (asBoolean_(policy.Active)) {
      policyByKey[normalize_(policy.PolicyKey)] = policy;
    }
  });

  var activeStatuses = [
    'NEW', 'REVIEW', 'AWAITING_APPROVAL',
    'APPROVED', 'PO_CREATED'
  ];

  var activeByPolicy = {};
  recommendations.objects.forEach(function(rec) {
    if (activeStatuses.indexOf(normalize_(rec.Status)) !== -1 &&
        !activeByPolicy[normalize_(rec.PolicyKey)]) {
      activeByPolicy[normalize_(rec.PolicyKey)] = rec;
    }
  });

  balances.forEach(function(balance) {
    var key = balance.PolicyKey;
    var existing = activeByPolicy[key];
    var requiresAction = [
      'REORDER', 'STOCKOUT', 'INBOUND_COVERED'
    ].indexOf(balance.ReplenishmentStatus) !== -1;

    if (requiresAction) {
      var policy = policyByKey[key] || {};
      var buyerEmail = normalize_(policy.BuyerEmail) ||
        CONFIG.purchasingEmail;
      var estimatedValue =
        balance.SuggestedOrderQty *
        (itemCostBySku[balance.SKU] || 0);
      var requiresApproval =
        estimatedValue > CONFIG.approvalThreshold;
      var reason = recommendationReason_(balance);

      if (existing) {
        updateRecommendationFields_(recommendations, existing, {
          OnHand: balance.OnHand,
          OpenPOQty: balance.OpenPOQty,
          ReorderPoint: balance.ReorderPoint,
          SuggestedQty: balance.SuggestedOrderQty,
          EstimatedValue: round_(estimatedValue, 2),
          Reason: reason,
          BuyerEmail: buyerEmail,
          ApproverEmail: requiresApproval ?
            CONFIG.escalationEmail : ''
        });
      } else {
        var newRecord = {
          RecommendationID: 'REC-' + Utilities.getUuid(),
          PolicyKey: key,
          CreatedAt: now,
          SKU: balance.SKU,
          Location: balance.Location,
          SupplierID: balance.SupplierID,
          OnHand: balance.OnHand,
          OpenPOQty: balance.OpenPOQty,
          ReorderPoint: balance.ReorderPoint,
          SuggestedQty: balance.SuggestedOrderQty,
          EstimatedValue: round_(estimatedValue, 2),
          Reason: reason,
          Status: 'NEW',
          BuyerEmail: buyerEmail,
          ApproverEmail: requiresApproval ?
            CONFIG.escalationEmail : '',
          ApprovalStatus: requiresApproval ?
            'NOT_REQUESTED' : 'NOT_REQUIRED',
          ApprovedBy: '',
          ApprovedAt: '',
          LastNotifiedAt: '',
          LastReminderAt: '',
          EscalatedAt: '',
          PONumber: '',
          Notes: '',
          AutomationRunID: runId
        };

        appendObject_(recommendations, newRecord);
      }
    } else if (existing &&
               ['NEW', 'REVIEW'].indexOf(
                 normalize_(existing.Status)) !== -1) {
      updateRecommendationFields_(recommendations, existing, {
        Status: 'RESOLVED',
        Notes: appendNote_(
          existing.Notes,
          'Automatically resolved after stock position recovered.'
        )
      });
    }
  });
}

function sendRecommendationNotifications_() {
  var table = readTable_(CONFIG.sheets.recommendations);
  var now = new Date();
  var newGroups = {};
  var reminderGroups = {};
  var escalationGroups = {};

  table.objects.forEach(function(rec) {
    var status = normalize_(rec.Status);
    var createdAt = toDate_(rec.CreatedAt);
    if (!createdAt) {
      return;
    }

    var buyer = validEmail_(rec.BuyerEmail) ?
      normalize_(rec.BuyerEmail) : CONFIG.purchasingEmail;
    var approver = validEmail_(rec.ApproverEmail) ?
      normalize_(rec.ApproverEmail) : CONFIG.escalationEmail;
    var ageHours =
      (now.getTime() - createdAt.getTime()) / 3600000;

    if (status === 'NEW' && !toDate_(rec.LastNotifiedAt)) {
      addToGroup_(newGroups, buyer, rec);
    }

    var openStatuses = [
      'NEW', 'REVIEW', 'AWAITING_APPROVAL', 'APPROVED'
    ];

    if (openStatuses.indexOf(status) !== -1 &&
        ageHours >= 24 &&
        hoursSince_(rec.LastReminderAt, now) >= 24) {
      var reminderRecipient =
        status === 'AWAITING_APPROVAL' ? approver : buyer;
      addToGroup_(reminderGroups, reminderRecipient, rec);
    }

    if (openStatuses.indexOf(status) !== -1 &&
        ageHours >= 48 &&
        !toDate_(rec.EscalatedAt)) {
      addToGroup_(
        escalationGroups,
        CONFIG.escalationEmail,
        rec
      );
    }
  });

  sendGroupedRecommendations_(
    table, newGroups, 'New inventory recommendations',
    'LastNotifiedAt', now
  );

  sendGroupedRecommendations_(
    table, reminderGroups, 'Inventory action reminder',
    'LastReminderAt', now
  );

  sendGroupedRecommendations_(
    table, escalationGroups, 'Overdue inventory escalation',
    'EscalatedAt', now
  );
}

function sendGroupedRecommendations_(
  table, groups, subject, timestampField, now) {

  Object.keys(groups).forEach(function(recipient) {
    var records = groups[recipient];
    var lines = [
      subject,
      '',
      'The following inventory records require attention:'
    ];

    records.forEach(function(rec) {
      lines.push(
        rec.RecommendationID + ' | ' +
        rec.SKU + ' | ' +
        rec.Location + ' | Status: ' +
        rec.Status + ' | Suggested: ' +
        rec.SuggestedQty + ' | Reason: ' +
        rec.Reason
      );
    });

    lines.push('');
    lines.push('Review these records in the inventory application.');

    sendMailWithRetry_(recipient, subject, lines.join('\n'));

    records.forEach(function(rec) {
      setObjectField_(table, rec, timestampField, now);
    });
  });
}

function recommendationReason_(balance) {
  if (balance.ReplenishmentStatus === 'STOCKOUT') {
    return 'Stockout with insufficient inbound coverage.';
  }

  if (balance.ReplenishmentStatus === 'INBOUND_COVERED') {
    return 'Stockout is covered by open orders; verify or expedite delivery.';
  }

  return 'Inventory position is at or below the reorder point.';
}

function markTransactionError_(tx, message, now) {
  tx.ProcessStatus = 'ERROR';
  tx.ProcessedAt = tx.ProcessedAt || now;
  tx.ErrorMessage = message;
}

function shouldApplyAfterOpening_(openingsByKey, key, timestamp) {
  var opening = openingsByKey[key];
  if (!opening || !opening.asOf) {
    return true;
  }
  return timestamp.getTime() > opening.asOf.getTime();
}

function roundUpToMultiple_(quantity, multiple) {
  return Math.ceil(quantity / multiple) * multiple;
}

function round_(value, decimals) {
  var factor = Math.pow(10, decimals);
  return Math.round(value * factor) / factor;
}

function makePolicyKey_(sku, location) {
  return normalize_(sku) + '|' + normalize_(location);
}

function normalize_(value) {
  return value === null || value === undefined ?
    '' : String(value).trim().toUpperCase();
}

function toNumber_(value) {
  if (value === '' || value === null || value === undefined) {
    return null;
  }

  var number = Number(value);
  return isFinite(number) ? number : null;
}

function toDate_(value) {
  if (!value) {
    return null;
  }

  var date = value instanceof Date ? value : new Date(value);
  return isNaN(date.getTime()) ? null : date;
}

function asBoolean_(value) {
  if (value === true) {
    return true;
  }
  return ['TRUE', 'YES', '1', 'ACTIVE'].indexOf(
    normalize_(value)) !== -1;
}

function validEmail_(value) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(
    String(value || '').trim()
  );
}

function hoursSince_(value, now) {
  var date = toDate_(value);
  if (!date) {
    return Number.POSITIVE_INFINITY;
  }
  return (now.getTime() - date.getTime()) / 3600000;
}

function appendNote_(existing, note) {
  var current = String(existing || '').trim();
  return current ? current + '\n' + note : note;
}

function addToGroup_(groups, recipient, record) {
  if (!validEmail_(recipient)) {
    throw new Error('Invalid notification email: ' + recipient);
  }

  if (!groups[recipient]) {
    groups[recipient] = [];
  }
  groups[recipient].push(record);
}

function sendMailWithRetry_(recipient, subject, body) {
  var lastError;

  for (var attempt = 1;
       attempt <= CONFIG.retryAttempts;
       attempt++) {
    try {
      MailApp.sendEmail({
        to: recipient,
        subject: subject,
        body: body
      });
      return;
    } catch (error) {
      lastError = error;
      if (attempt < CONFIG.retryAttempts) {
        Utilities.sleep(Math.pow(2, attempt - 1) * 1000);
      }
    }
  }

  throw lastError;
}

function readTable_(sheetName) {
  var sheet = getBook_().getSheetByName(sheetName);
  if (!sheet) {
    throw new Error('Missing sheet: ' + sheetName);
  }

  var values = sheet.getDataRange().getValues();
  if (values.length === 0) {
    throw new Error('Sheet has no header row: ' + sheetName);
  }

  var headers = values[0].map(function(value) {
    return String(value).trim();
  });

  var objects = [];

  for (var rowIndex = 1;
       rowIndex < values.length;
       rowIndex++) {
    var row = values[rowIndex];
    var hasData = row.some(function(value) {
      return value !== '' &&
        value !== null &&
        value !== undefined;
    });

    if (!hasData) {
      continue;
    }

    var object = {_row: rowIndex + 1};
    headers.forEach(function(header, columnIndex) {
      object[header] = row[columnIndex];
    });
    objects.push(object);
  }

  return {
    sheet: sheet,
    headers: headers,
    objects: objects
  };
}

function writeObjectColumns_(table, fieldNames) {
  var lastRow = table.sheet.getLastRow();
  if (lastRow < 2) {
    return;
  }

  var byRow = {};
  table.objects.forEach(function(object) {
    byRow[object._row] = object;
  });

  fieldNames.forEach(function(fieldName) {
    var column = table.headers.indexOf(fieldName) + 1;
    if (column === 0) {
      throw new Error('Missing field: ' + fieldName);
    }

    var range = table.sheet.getRange(
      2, column, lastRow - 1, 1
    );
    var values = range.getValues();

    for (var row = 2; row <= lastRow; row++) {
      if (byRow[row]) {
        values[row - 2][0] = byRow[row][fieldName];
      }
    }

    range.setValues(values);
  });
}

function replaceTableData_(sheetName, objects) {
  var table = readTable_(sheetName);
  var sheet = table.sheet;
  var lastRow = sheet.getLastRow();

  if (lastRow > 1) {
    sheet.getRange(
      2, 1, lastRow - 1, table.headers.length
    ).clearContent();
  }

  if (objects.length === 0) {
    return;
  }

  var rows = objects.map(function(object) {
    return table.headers.map(function(header) {
      return object[header] === undefined ?
        '' : object[header];
    });
  });

  sheet.getRange(
    2, 1, rows.length, table.headers.length
  ).setValues(rows);
}

function appendObject_(table, object) {
  var row = table.headers.map(function(header) {
    return object[header] === undefined ? '' : object[header];
  });
  table.sheet.appendRow(row);
}

function updateRecommendationFields_(table, object, fields) {
  Object.keys(fields).forEach(function(fieldName) {
    setObjectField_(table, object, fieldName, fields[fieldName]);
  });
}

function setObjectField_(table, object, fieldName, value) {
  var column = table.headers.indexOf(fieldName) + 1;
  if (column === 0) {
    throw new Error('Missing field: ' + fieldName);
  }

  table.sheet.getRange(object._row, column).setValue(value);
  object[fieldName] = value;
}

function validateBaseConfiguration_() {
  if (!CONFIG.spreadsheetId ||
      CONFIG.spreadsheetId === 'YOUR_SPREADSHEET_ID') {
    throw new Error('Replace YOUR_SPREADSHEET_ID.');
  }
}

function validateConfiguration_() {
  validateBaseConfiguration_();

  if (!validEmail_(CONFIG.purchasingEmail)) {
    throw new Error('Set a valid purchasingEmail.');
  }

  if (!validEmail_(CONFIG.escalationEmail)) {
    throw new Error('Set a valid escalationEmail.');
  }

  if ([1, 5, 10, 15, 30].indexOf(
      CONFIG.triggerMinutes) === -1) {
    throw new Error(
      'triggerMinutes must be 1, 5, 10, 15, or 30.'
    );
  }
}

function validateTables_() {
  var book = getBook_();

  Object.keys(REQUIRED_HEADERS).forEach(function(sheetName) {
    var sheet = book.getSheetByName(sheetName);
    if (!sheet) {
      throw new Error('Missing sheet: ' + sheetName);
    }

    var required = REQUIRED_HEADERS[sheetName];
    var actual = sheet.getRange(
      1, 1, 1, Math.max(sheet.getLastColumn(), 1)
    ).getValues()[0].map(function(value) {
      return String(value).trim();
    });

    required.forEach(function(header) {
      if (actual.indexOf(header) === -1) {
        throw new Error(
          'Missing header ' + header + ' in ' + sheetName
        );
      }
    });
  });
}

function getBook_() {
  return SpreadsheetApp.openById(CONFIG.spreadsheetId);
}

function withRetry_(stepName, operation) {
  var lastError;

  for (var attempt = 1;
       attempt <= CONFIG.retryAttempts;
       attempt++) {
    try {
      return operation();
    } catch (error) {
      lastError = error;
      var transientError = /rate|quota|timeout|timed out|internal|try again|service/i
        .test(String(error.message || error));

      if (!transientError ||
          attempt === CONFIG.retryAttempts) {
        throw new Error(
          stepName + ': ' + sanitizeError_(error)
        );
      }

      Utilities.sleep(Math.pow(2, attempt - 1) * 1000);
    }
  }

  throw lastError;
}

function logRun_(
  runId, startedAt, finishedAt, status,
  step, recordsProcessed, message) {

  var table = readTable_(CONFIG.sheets.log);
  appendObject_(table, {
    RunID: runId,
    StartedAt: startedAt,
    FinishedAt: finishedAt,
    Status: status,
    Step: step,
    RecordsProcessed: recordsProcessed,
    Message: message
  });
}

function logRunSafe_(
  runId, startedAt, finishedAt, status,
  step, recordsProcessed, message) {

  try {
    logRun_(
      runId, startedAt, finishedAt, status,
      step, recordsProcessed, message
    );
  } catch (loggingError) {
    console.error(
      'Unable to write AutomationLog: ' +
      sanitizeError_(loggingError)
    );
  }
}

function sendFailureNoticeSafe_(runId, step, error) {
  try {
    MailApp.sendEmail({
      to: CONFIG.escalationEmail,
      subject: 'Inventory automation failed',
      body:
        'Run ID: ' + runId + '\n' +
        'Step: ' + step + '\n' +
        'Error: ' + sanitizeError_(error) + '\n\n' +
        'Review Apps Script executions and AutomationLog.'
    });
  } catch (notificationError) {
    console.error(
      'Unable to send failure notification: ' +
      sanitizeError_(notificationError)
    );
  }
}

function sanitizeError_(error) {
  return String(
    error && error.message ? error.message : error
  ).substring(0, 1000);
}

Configuration and deployment

1. Replace YOUR_SPREADSHEET_ID with the identifier between /d/ and /edit in the workbook URL.
2. Replace both YOUR_EMAIL_ADDRESS values with controlled business recipients.
3. Change the representative approval threshold only after confirming purchasing policy.
4. Save the project.
5. Run initializeInventoryWorkbook manually. Authorize spreadsheet access when prompted.
6. Populate test master data, policies, and opening balances.
7. Run runInventoryAutomation manually.
8. Inspect the InventoryBalances, ReorderRecommendations, and AutomationLog sheets.
9. Open the Apps Script execution history and confirm there are no authorization or runtime errors.
10. Run setupInventoryAutomation once to install the 15-minute trigger.

The script requests permission to read and write the configured spreadsheet and send email. The account installing the trigger becomes the executing identity, so it must retain access to the workbook and recipients.

Duplicate execution is controlled through a script lock, unique EventKeys, and one active recommendation per PolicyKey. Transient service errors receive exponential retries. If the run still fails, AutomationLog records the step and error. Correct the source or configuration and run runInventoryAutomation manually to recover.

Likely errors include missing headers, invalid email addresses, duplicate policies, malformed opening balances, inactive SKUs, or an expired installer account. The error message identifies the affected sheet or processing step. Never delete failed source transactions until reconciliation is complete.

Failure Handling and Operational Reliability

The Transactions sheet serves as the operational ledger and manual-review queue. AutomationLog serves as the run-level failure record. For a larger implementation, a separate dead-letter table can store failed external API payloads. It is not required for the core design because all source records remain available in Google Sheets.

Idempotency is provided by EventKey and active PolicyKey controls. There is still a small ambiguity if an email provider accepts a message but the script fails before recording its notification timestamp. Staff should treat the recommendation record as authoritative and tolerate an occasional duplicate notice rather than risking a missed purchasing alert.

At least weekly, the inventory analyst should reconcile a sample of opening balance plus movements against InventoryBalances. Monthly, compare open PO quantities with supplier confirmations and receiving records. Any unexplained difference should be resolved before archiving transactions.

A Complete Example

A warehouse employee at location WH-02 issues eight units of SKU BRG-6204. Before the issue, the calculated on-hand quantity is 56 units and there are no open purchase orders.

AppSheet generates the identifier, records the authenticated user, validates the positive quantity, and writes the transaction to Google Sheets. Apps Script later confirms that the EventKey is unique and that BRG-6204|WH-02 is an active policy.

Demand over the previous 90 calendar days is 360 units, producing average daily demand of 4 units. Lead time is 8 days and safety stock is 20 units.

Reorder point =
4 units per day * 8 days + 20 safety units
= 52 units

New on-hand quantity =
56 - 8
= 48 units

Inventory position =
48 on hand + 0 open PO
= 48 units

Because 48 is at or below the 52-unit reorder point, the status becomes REORDER. The fixed reorder quantity is 80 units, minimum order quantity is 40, and order multiple is 20.

Unrounded suggestion =
MAX(80, 40, 52 + 80 - 48)
= 84 units

Rounded to a multiple of 20 =
100 units

The automation creates recommendation REC-6D92C1, assigns it to the policy’s buyer, estimates the value using the item’s planning cost, and sends it in the buyer’s grouped alert. At an estimated unit cost of $6.20, the recommendation value is $620, below the representative manager-approval threshold.

The buyer reviews demand, confirms the supplier and quantity, and approves the recommendation. An AppSheet action creates purchase order line POL-1048-01. The buyer records PO number CVP-1048, quantity 100, expected date, and status ORDERED.

On the next run, OpenPOQty becomes 100 and inventory position becomes 148. The recommendation remains linked as PO_CREATED rather than creating a duplicate. When the warehouse receives 100 units and selects POL-1048-01, the receipt increases on-hand quantity, ReceivedQty becomes 100, OpenQty becomes zero, and the PO status becomes CLOSED.

If the original issue had been submitted twice with the same EventKey, the second row would have become DUPLICATE and would not have reduced stock again.

Implementation Cost

All amounts below are representative planning assumptions, not vendor quotations or verified client costs. Actual software charges depend on current licensing, user count, storage, authentication, and selected features.

Existing Google Workspace costs are excluded from incremental savings if the company already pays for those accounts. They should be included if the automation requires new users, storage, security, or administrative features.

Estimated Time and Cost Savings

The calculation focuses on recurring SKU-location replenishment reviews. It does not assume payroll reduction and does not include possible benefits from fewer stockouts or lower excess inventory.

Current monthly labour hours: Monthly volume × current minutes per record ÷ 60

10,800 * 0.75 / 60 = 135.0 hours

New monthly labour hours: Monthly volume × new minutes per record ÷ 60, plus exception handling and maintenance

Automated review overhead:
10,800 * 0.05 / 60 = 9.0 hours

Exception handling:
432 * 4 / 60 = 28.8 hours

Maintenance:
5.0 hours

Total new monthly labour:
9.0 + 28.8 + 5.0 = 42.8 hours

Monthly hours recovered: Current monthly labour hours minus new monthly labour hours

135.0 - 42.8 = 92.2 hours

Estimated monthly labour value: Monthly hours recovered × loaded hourly labour cost

92.2 * $42 = $3,872.40

Net estimated monthly value: Monthly labour value minus recurring tool costs

$3,872.40 - $150 = $3,722.40

Estimated payback period: One-time implementation cost ÷ net estimated monthly value

$10,250 / $3,722.40 = approximately 2.75 months

If internal setup time is included, the economic setup cost is $13,114 and the corresponding representative payback is approximately 3.52 months.

Recovered time does not automatically reduce payroll. It can represent additional purchasing capacity, quicker response, less overtime, fewer administrative checks, or the ability to support a larger catalog without adding equivalent review effort.

Non-financial benefits can include clearer ownership, fewer duplicate orders, quicker identification of missing data, consistent replenishment logic, improved auditability, more reliable location reporting, and lower dependency on individual employee knowledge.

Readers should replace transaction volume, SKU-location count, review frequency, handling time, exception rate, labour rate, recurring software cost, implementation cost, and maintenance time with their own measured figures.

Adding AI to the Automation

AI should be added only after the transaction ledger, policy data, reorder calculation, approvals, and failure handling work reliably.

The core replenishment decision does not require AI. Required fields, exact SKU matching, arithmetic formulas, lead-time values, order multiples, thresholds, and permissions are better handled by deterministic rules.

Potential AI applications include:

• Extracting revised delivery dates from supplier emails
• Classifying supplier delay reasons
• Summarizing long purchasing conversations
• Identifying potentially duplicate item descriptions for human review
• Suggesting categories for unstructured exception notes
• Comparing supplier confirmations with purchase order details
• Summarizing recurring stockout themes for management

The normal automation creates the inventory balance, reorder point, alert, workflow, approval evidence, and dashboard. AI contributes only when useful information arrives as unstructured text or documents.

The Recommended AI Enhancement

The recommended enhancement extracts proposed delivery changes from supplier emails. A buyer applies a dedicated Gmail label to an email. Apps Script sends only the approved operational text to an AI service and writes structured output to an AIReviewQueue sheet.

• Trigger: Gmail message receives the InventoryAI_Pending label
• AI input: Sender, subject, received timestamp, and a limited plain-text body
• Expected output: PO number, revised expected date, delay reason, proposed action, confidence, and evidence
• Validation: JSON schema, valid date format, confidence range, and PO number match
• Record update: AIReviewQueue receives a proposed update
• Human review: A buyer confirms, corrects, or rejects the extraction
• Low confidence: Confidence below 0.85 remains REVIEW
• Failure behavior: Message receives an error label and the buyer follows the normal manual process

The reusable system instruction is:

You extract operational delivery information from supplier emails.

Return only data supported by the supplied email. Do not infer a purchase
order number, date, quantity, commitment, or cause that is not explicitly
stated. A proposed or estimated date must not be described as confirmed.

Classify action_required as one of:
none, confirm_date, contact_supplier, or escalate.

Use null when a purchase order number or revised date is not present.
Confidence must be between 0 and 1. Evidence must contain short exact
snippets from the email. Do not make purchasing decisions and do not
approve or update a purchase order.

The reusable user prompt is:

Extract a proposed supplier delivery update.

Sender: {{sender}}
Received: {{received_at}}
Subject: {{subject}}

Email body:
{{plain_text_body}}

The required structured output is:

{
  "po_number": "CVP-1048",
  "revised_expected_date": "2026-07-22",
  "delay_reason": "Carrier capacity constraint",
  "action_required": "confirm_date",
  "confidence": 0.91,
  "evidence": [
    "PO CVP-1048",
    "expected to arrive July 22",
    "carrier capacity"
  ]
}

Prohibited input includes payment card data, bank details, passwords, private employee data, legal advice, sensitive customer information, and unrelated email-thread content. The buyer remains responsible for confirming the supplier, PO, date, and operational response.

For implementation, add an AIReviewQueue sheet and paste the following second file into the same Apps Script project. Store OPENAI_API_KEY and OPENAI_MODEL in Apps Script Properties. Select a currently supported model that accepts structured JSON output.

var AI_CONFIG = {
  endpoint: 'https://api.openai.com/v1/chat/completions',
  pendingLabel: 'InventoryAI_Pending',
  processedLabel: 'InventoryAI_Processed',
  errorLabel: 'InventoryAI_Error',
  queueSheet: 'AIReviewQueue',
  confidenceThreshold: 0.85,
  maxBodyCharacters: 12000,
  maxMessagesPerRun: 20,
  retryAttempts: 3
};

var AI_QUEUE_HEADERS = [
  'QueueID', 'GmailMessageID', 'ReceivedAt', 'Sender',
  'Subject', 'PONumber', 'RevisedExpectedDate',
  'DelayReason', 'ActionRequired', 'Confidence',
  'Evidence', 'ValidationStatus', 'HumanDecision',
  'ReviewedBy', 'ReviewedAt', 'ErrorMessage', 'CreatedAt'
];

function setupSupplierDelayAi() {
  var properties = PropertiesService.getScriptProperties();
  if (!properties.getProperty('OPENAI_API_KEY')) {
    throw new Error('Set OPENAI_API_KEY in Script Properties.');
  }
  if (!properties.getProperty('OPENAI_MODEL')) {
    throw new Error('Set OPENAI_MODEL in Script Properties.');
  }

  var book = SpreadsheetApp.openById(CONFIG.spreadsheetId);
  var sheet = book.getSheetByName(AI_CONFIG.queueSheet);

  if (!sheet) {
    sheet = book.insertSheet(AI_CONFIG.queueSheet);
  }

  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, AI_QUEUE_HEADERS.length)
      .setValues([AI_QUEUE_HEADERS]);
    sheet.setFrozenRows(1);
  }

  GmailApp.createLabel(AI_CONFIG.pendingLabel);
  GmailApp.createLabel(AI_CONFIG.processedLabel);
  GmailApp.createLabel(AI_CONFIG.errorLabel);

  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    if (trigger.getHandlerFunction() ===
        'processSupplierDelayEmails') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  ScriptApp.newTrigger('processSupplierDelayEmails')
    .timeBased()
    .everyMinutes(15)
    .create();
}

function processSupplierDelayEmails() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) {
    throw new Error('Another automation run is active.');
  }

  try {
    var queue = getAiQueue_();
    var processedIds = {};

    queue.objects.forEach(function(row) {
      processedIds[String(row.GmailMessageID)] = true;
    });

    var pendingLabel =
      GmailApp.getUserLabelByName(AI_CONFIG.pendingLabel);
    var processedLabel =
      GmailApp.getUserLabelByName(AI_CONFIG.processedLabel);
    var errorLabel =
      GmailApp.getUserLabelByName(AI_CONFIG.errorLabel);

    var threads = GmailApp.search(
      'label:' + AI_CONFIG.pendingLabel,
      0,
      AI_CONFIG.maxMessagesPerRun
    );

    threads.forEach(function(thread) {
      var messages = thread.getMessages();
      var message = messages[messages.length - 1];
      var messageId = message.getId();

      if (processedIds[messageId]) {
        thread.removeLabel(pendingLabel);
        thread.addLabel(processedLabel);
        return;
      }

      try {
        var extracted = extractSupplierDelay_(message);
        var validation = validateAiExtraction_(extracted);
        var poExists = purchaseOrderExists_(extracted.po_number);

        var validationStatus = 'REVIEW';
        if (validation.valid &&
            poExists &&
            extracted.confidence >=
              AI_CONFIG.confidenceThreshold) {
          validationStatus = 'MATCHED_REVIEW_REQUIRED';
        } else if (!poExists && extracted.po_number) {
          validationStatus = 'PO_NOT_FOUND';
        } else if (!validation.valid) {
          validationStatus = 'INVALID_OUTPUT';
        } else {
          validationStatus = 'LOW_CONFIDENCE';
        }

        appendAiQueueRow_({
          QueueID: 'AIQ-' + Utilities.getUuid(),
          GmailMessageID: messageId,
          ReceivedAt: message.getDate(),
          Sender: message.getFrom(),
          Subject: message.getSubject(),
          PONumber: extracted.po_number || '',
          RevisedExpectedDate:
            extracted.revised_expected_date || '',
          DelayReason: extracted.delay_reason || '',
          ActionRequired: extracted.action_required,
          Confidence: extracted.confidence,
          Evidence: extracted.evidence.join(' | '),
          ValidationStatus: validationStatus,
          HumanDecision: '',
          ReviewedBy: '',
          ReviewedAt: '',
          ErrorMessage: validation.message,
          CreatedAt: new Date()
        });

        thread.removeLabel(pendingLabel);
        thread.addLabel(processedLabel);
      } catch (error) {
        appendAiQueueRow_({
          QueueID: 'AIQ-' + Utilities.getUuid(),
          GmailMessageID: messageId,
          ReceivedAt: message.getDate(),
          Sender: message.getFrom(),
          Subject: message.getSubject(),
          PONumber: '',
          RevisedExpectedDate: '',
          DelayReason: '',
          ActionRequired: '',
          Confidence: '',
          Evidence: '',
          ValidationStatus: 'SERVICE_ERROR',
          HumanDecision: '',
          ReviewedBy: '',
          ReviewedAt: '',
          ErrorMessage: String(error.message).substring(0, 1000),
          CreatedAt: new Date()
        });

        thread.removeLabel(pendingLabel);
        thread.addLabel(errorLabel);
        console.error(error.stack || error.message);
      }
    });
  } finally {
    lock.releaseLock();
  }
}

function extractSupplierDelay_(message) {
  var properties = PropertiesService.getScriptProperties();
  var apiKey = properties.getProperty('OPENAI_API_KEY');
  var model = properties.getProperty('OPENAI_MODEL');

  if (!apiKey || !model) {
    throw new Error('AI credentials or model are not configured.');
  }

  var systemInstruction =
    'You extract operational delivery information from supplier emails. ' +
    'Return only data supported by the supplied email. Do not infer a ' +
    'purchase order number, date, quantity, commitment, or cause that is ' +
    'not explicitly stated. A proposed or estimated date must not be ' +
    'described as confirmed. Classify action_required as one of: none, ' +
    'confirm_date, contact_supplier, or escalate. Use null when a purchase ' +
    'order number or revised date is not present. Confidence must be ' +
    'between 0 and 1. Evidence must contain short exact snippets from the ' +
    'email. Do not make purchasing decisions and do not approve or update ' +
    'a purchase order.';

  var body = message.getPlainBody().substring(
    0, AI_CONFIG.maxBodyCharacters
  );

  var userPrompt =
    'Extract a proposed supplier delivery update.\n\n' +
    'Sender: ' + message.getFrom() + '\n' +
    'Received: ' + message.getDate().toISOString() + '\n' +
    'Subject: ' + message.getSubject() + '\n\n' +
    'Email body:\n' + body;

  var payload = {
    model: model,
    temperature: 0,
    messages: [
      {role: 'system', content: systemInstruction},
      {role: 'user', content: userPrompt}
    ],
    response_format: {
      type: 'json_schema',
      json_schema: {
        name: 'supplier_delivery_update',
        strict: true,
        schema: {
          type: 'object',
          additionalProperties: false,
          properties: {
            po_number: {
              type: ['string', 'null']
            },
            revised_expected_date: {
              type: ['string', 'null'],
              description: 'ISO date in YYYY-MM-DD format'
            },
            delay_reason: {
              type: 'string'
            },
            action_required: {
              type: 'string',
              enum: [
                'none',
                'confirm_date',
                'contact_supplier',
                'escalate'
              ]
            },
            confidence: {
              type: 'number',
              minimum: 0,
              maximum: 1
            },
            evidence: {
              type: 'array',
              items: {type: 'string'},
              maxItems: 3
            }
          },
          required: [
            'po_number',
            'revised_expected_date',
            'delay_reason',
            'action_required',
            'confidence',
            'evidence'
          ]
        }
      }
    }
  };

  var response = fetchAiWithRetry_(apiKey, payload);
  var parsedResponse = JSON.parse(response.getContentText());

  if (!parsedResponse.choices ||
      !parsedResponse.choices[0] ||
      !parsedResponse.choices[0].message ||
      !parsedResponse.choices[0].message.content) {
    throw new Error('AI response did not contain structured content.');
  }

  return JSON.parse(
    parsedResponse.choices[0].message.content
  );
}

function fetchAiWithRetry_(apiKey, payload) {
  var lastError;

  for (var attempt = 1;
       attempt <= AI_CONFIG.retryAttempts;
       attempt++) {
    var response = UrlFetchApp.fetch(AI_CONFIG.endpoint, {
      method: 'post',
      contentType: 'application/json',
      headers: {
        Authorization: 'Bearer ' + apiKey
      },
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });

    var status = response.getResponseCode();

    if (status >= 200 && status < 300) {
      return response;
    }

    lastError = new Error(
      'AI API returned HTTP ' + status + ': ' +
      response.getContentText().substring(0, 500)
    );

    if ((status === 429 || status >= 500) &&
        attempt < AI_CONFIG.retryAttempts) {
      Utilities.sleep(Math.pow(2, attempt - 1) * 1000);
      continue;
    }

    throw lastError;
  }

  throw lastError;
}

function validateAiExtraction_(result) {
  if (!result ||
      typeof result.confidence !== 'number' ||
      result.confidence < 0 ||
      result.confidence > 1) {
    return {
      valid: false,
      message: 'Confidence is missing or invalid.'
    };
  }

  if ([
      'none',
      'confirm_date',
      'contact_supplier',
      'escalate'
    ].indexOf(result.action_required) === -1) {
    return {
      valid: false,
      message: 'Action category is invalid.'
    };
  }

  if (!Array.isArray(result.evidence)) {
    return {
      valid: false,
      message: 'Evidence must be an array.'
    };
  }

  if (result.revised_expected_date &&
      !isValidIsoDate_(result.revised_expected_date)) {
    return {
      valid: false,
      message: 'Revised date is not a valid YYYY-MM-DD date.'
    };
  }

  return {
    valid: true,
    message: ''
  };
}

function isValidIsoDate_(value) {
  if (!/^\d{4}-\d{2}-\d{2}$/.test(value)) {
    return false;
  }

  var date = new Date(value + 'T00:00:00Z');
  return !isNaN(date.getTime()) &&
    date.toISOString().substring(0, 10) === value;
}

function purchaseOrderExists_(poNumber) {
  if (!poNumber) {
    return false;
  }

  var table = readTable_(CONFIG.sheets.purchaseOrders);
  var target = normalize_(poNumber);

  return table.objects.some(function(po) {
    return normalize_(po.PONumber) === target;
  });
}

function getAiQueue_() {
  var book = SpreadsheetApp.openById(CONFIG.spreadsheetId);
  var sheet = book.getSheetByName(AI_CONFIG.queueSheet);

  if (!sheet) {
    throw new Error(
      'Run setupSupplierDelayAi before processing messages.'
    );
  }

  return readTable_(AI_CONFIG.queueSheet);
}

function appendAiQueueRow_(object) {
  var table = getAiQueue_();
  appendObject_(table, object);
}

To test the AI extension, use a fabricated supplier email containing a known test PO number and date. Apply the pending label, run processSupplierDelayEmails manually, and inspect AIReviewQueue. Test missing PO numbers, ambiguous dates, malformed model output, rate limits, and service failure before allowing real operational email.

The endpoint uses bearer authentication. Requests are JSON POST operations with a structured-output schema. HTTP 429 and server errors are retried. Other non-success responses stop processing and enter the error queue. The script processes a limited number of messages per run to control runtime and cost.

Benefits of the AI Enhancement

• Less time reading repetitive supplier delay messages
• Consistent extraction of PO numbers and proposed dates
• Faster identification of messages that require escalation
• Evidence snippets that help buyers verify the result
• Structured delay reasons that can be summarized in reporting
• Reduced manual copying from email into a review queue

These are AI-specific benefits. Inventory balances, reorder calculations, order quantities, ownership, alerts, approvals, and dashboards continue to come from the deterministic core automation.

What Remains Rule-Based or Human-Controlled

• Inventory quantity: Calculated from validated transactions, not AI interpretation.
• Reorder point: Calculated from approved numerical policy values.
• Order multiple: Enforced through an exact arithmetic rule.
• Duplicate detection: Based on identifiers and exact matching.
• Purchase approval: Completed by an authorized person.
• Supplier commitment: Confirmed by the buyer because AI cannot establish contractual intent.
• Expected-date update: Written to the purchase order only after human confirmation.
• Policy override: Approved and documented by the inventory or procurement owner.
• Stock write-off: Requires authorized investigation and accounting treatment.
• Final escalation: Determined by the buyer or procurement manager.

AI output is a proposal. It must never release a purchase, alter accounting records, change inventory, or make final supplier decisions without human review.

Estimating the Additional Value of AI

Use a separate assumption set because supplier email processing is different from SKU-location replenishment review.

Original process:
120 * 6 / 60 = 12.0 hours

Core automation without AI:
120 * 4 / 60 = 8.0 hours

AI base review:
120 * 1.25 / 60 = 2.50 hours

Corrections:
18 * 2 / 60 = 0.60 hours

Service failures:
4 * 4 / 60 = 0.27 hours

Total AI-assisted time:
2.50 + 0.60 + 0.27 = 3.37 hours

Additional capacity compared with core automation:
8.00 - 3.37 = 4.63 hours

Labour value:
4.63 * $42 = $194.46

AI usage cost:
120 * $0.04 = $4.80

Net representative additional monthly value:
$194.46 - $4.80 = $189.66

The correction and failure assumptions must be measured during a controlled pilot. AI does not eliminate review, and the result should be evaluated primarily as additional purchasing capacity and faster response.

Testing Checklist

Use fabricated sample data and test accounts before processing real inventory, purchasing, supplier, or employee information.

Ongoing Maintenance

The inventory analyst is the primary business owner. The procurement manager is the backup business owner. A Google Workspace or automation administrator owns credentials, triggers, and technical recovery.

Credential rotation should follow company policy. API changes, AppSheet schema changes, renamed headers, and Looker Studio field changes must be tested in the non-production workbook first. Update operating documentation whenever statuses, thresholds, fields, or ownership rules change.

When to Move to Dedicated Software

The implementation should not be replaced merely because it uses Google Sheets. It should be reviewed when operating or control requirements exceed the architecture.

• Transaction history regularly causes script timeouts or slow AppSheet synchronization.
• Multiple facilities require real-time reservation, allocation, picking, or bin-level inventory.
• Barcode scanning, lot tracking, serial numbers, expiration dates, or traceability become mandatory.
• Formal accounting integration requires automated inventory valuation or general-ledger posting.
• Approvals require complex parallel routing or strict segregation of duties.
• Customers or suppliers require external portals.
• Warehouse work must continue offline for extended periods.
• Formal regulatory or audit requirements exceed spreadsheet controls.
• Exception rates rise because processes have become more complex.
• Maintaining scripts, policies, and permissions requires excessive specialist time.
• Multiple currencies, companies, or legal entities must share the process.
• Advanced forecasting, purchasing optimization, or supplier scheduling becomes necessary.
• Mobile warehouse workflows require guided receiving, picking, packing, and cycle counting.
• Vendor support and guaranteed service levels become business requirements.

Relevant categories include inventory management systems, warehouse management systems, purchasing platforms, distribution ERP systems, and supply chain planning applications. The existing ledger, policy structure, and identifiers can provide a cleaner migration foundation if they have been governed consistently.

Implementation Checklist

• Confirm business requirements, locations, SKU count, transaction volume, and approval policy.
• Select Google Sheets, AppSheet, Apps Script, Google Drive, Gmail, and Looker Studio roles.
• Create controlled accounts, ownership, test users, and backup administrators.
• Define least-privilege permissions and row-level access rules.
• Create master data, policies, opening balances, transaction, PO, recommendation, and log structures.
• Build receipt, issue, transfer, and count intake forms.
• Configure unique TransactionID, EventKey, and PolicyKey values.
• Validate SKU, location, quantity, type, date, and PO fields.
• Map AppSheet records to Google Sheets columns.
• Install and authorize the complete Apps Script.
• Configure duplicate prevention, chronological calculations, and purchase order reconciliation.
• Configure reorder points, safety stock, lead time, reorder quantity, minimum quantity, and order multiple rules.
• Configure buyer ownership and approval thresholds.
• Configure reminders, escalation, rejection, reassignment, and unavailable-approver handling.
• Configure grouped notifications and failure alerts.
• Create restricted document storage and attachment conventions.
• Create Looker Studio location, purchasing, exception, and automation views.
• Protect calculated sheets, policy overrides, secrets, and sensitive fields.
• Test normal, invalid, duplicate, rejected, overdue, failed, and recovered records.
• Complete user acceptance testing and a controlled warehouse pilot.
• Document rollback, support ownership, and manual recovery.
• Replace representative cost assumptions with current licensing and labour figures.
• Replace savings assumptions with measured volume, time, exception, and maintenance data.
• Add the AI enhancement only after the rule-based workflow is reliable.
• Require human confirmation for every AI-proposed purchase order update.
• Assign primary and backup maintenance owners.
• Define transaction-volume, security, workflow, and regulatory upgrade criteria.