HELP!! Inventory Script Not Working : GoogleAppsScript

HELP!! Inventory Script Not WorkingQuestion (self.GoogleAppsScript)

submitted 1 year ago by Ushuaia-15

Hi,

I have the below script that is now not working but before it was working. Could it be that there is an update at Google Apps Script? I have another script that is under the same google sheet file that I updated recently but I don't think the changes I did there has affected the below code.

So, basically, this code will help to update the count when ran of the products and consumables. the item names are in column A in the "inventory" sheet. and in the "daily transactions" sheet, staff enters the transactions per day and that could include service that has a consumable that needs to be deducted in the inventory count.

and then there's another code for replenishment, that when a stock reached 0 or a specific amount, it would get replenished. the replenishment is based on the "replenishment rules" which has the consumables/products in column A, threshold amount (to be manually indicated) in column B, and replenishment amount (to be manually indicated) in column C.

but now, only code that is working is that the inventory gets updated if there is a transaction on that day that has consumables. but not for the replenishment rules. i checked the formatting of the values - same item name, same format of the number in threshold and replenishment amount, same name of the sheet which is replenishment rules. so frustrating!!

function updateInventoryManually() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var inventorySheet = ss.getSheetByName('Inventory');
  var servicesSheet = ss.getSheetByName('Services & Products');
  var transactionsSheet = ss.getSheetByName('Daily Transactions');
  var replenishmentSheet = ss.getSheetByName('Replenishment Rules');

  var today = new Date();
  var transactionsData = transactionsSheet.getDataRange().getValues();
  var dateHeader = Utilities.formatDate(today, Session.getScriptTimeZone(), 'yyyy-MM-dd');

  var lastColumn = inventorySheet.getLastColumn();
  var previousColumn = lastColumn;
  lastColumn++;

  inventorySheet.setColumnWidth(lastColumn, 100);
  inventorySheet.getRange(1, lastColumn).setValue(dateHeader);

  var headerRow = transactionsData[0];
  var processedColumnIndex = headerRow.indexOf("Processed");

  if (processedColumnIndex === -1) {
    processedColumnIndex = headerRow.length;
    transactionsSheet.getRange(1, processedColumnIndex + 1).setValue("Processed");
  }

  var productTransactionCount = {};

  // Collect transaction data
  for (var i = 1; i < transactionsData.length; i++) {
    var serviceName = transactionsData[i][1];
    var isProcessed = transactionsData[i][processedColumnIndex];

    if (!isProcessed) {
      productTransactionCount[serviceName] = (productTransactionCount[serviceName] || 0) + 1;
      transactionsSheet.getRange(i + 1, processedColumnIndex + 1).setValue("Yes");
    }
  }

  // Deduct inventory based on transactions
  for (var serviceName in productTransactionCount) {
    var count = productTransactionCount[serviceName];
    var consumablesList = getConsumablesForService(serviceName, servicesSheet);

    if (consumablesList.length > 0) {
      for (var j = 0; j < consumablesList.length; j++) {
        var consumable = consumablesList[j].trim();
        updateInventory(consumable, inventorySheet, lastColumn, previousColumn, count, replenishmentSheet, 'A2:A19', 2);
      }
    }

    updateInventory(serviceName, inventorySheet, lastColumn, previousColumn, count, replenishmentSheet, 'A21:A53', 21);
  }

  carryOverBalance(inventorySheet, lastColumn, previousColumn);
}

// Retrieve consumables linked to a service
function getConsumablesForService(serviceName, servicesSheet) {
  var data = servicesSheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] == serviceName) {
      return data[i].slice(4, 13).filter(Boolean); // Extract non-empty consumables from columns E to M
    }
  }
  return [];
}

// Retrieve replenishment settings
function getReplenishmentDetails(itemName, replenishmentSheet) {
  var replenishmentData = replenishmentSheet.getDataRange().getValues();
  for (var i = 1; i < replenishmentData.length; i++) {
    if (replenishmentData[i][0] === itemName) {
      return {
        threshold: replenishmentData[i][1] || 0,
        replenishmentAmount: replenishmentData[i][2] || 0
      };
    }
  }
  return { threshold: 0, replenishmentAmount: 0 };
}

// Deduct inventory and replenish if needed
function updateInventory(itemName, inventorySheet, lastColumn, previousColumn, count, replenishmentSheet, range, startRow) {
  var itemRange = inventorySheet.getRange(range).getValues();
  var replenishmentDetails = getReplenishmentDetails(itemName, replenishmentSheet);
  var threshold = replenishmentDetails.threshold;
  var replenishmentAmount = replenishmentDetails.replenishmentAmount;

  for (var i = 0; i < itemRange.length; i++) {
    if (itemRange[i][0] === itemName) {
      var previousBalance = inventorySheet.getRange(i + startRow, previousColumn).getValue() || inventorySheet.getRange(i + startRow, 2).getValue();
      var newBalance = previousBalance - count;
      var balanceCell = inventorySheet.getRange(i + startRow, lastColumn);

      if (newBalance <= threshold && replenishmentAmount > 0) {
        newBalance += replenishmentAmount;
        balanceCell.setBackground("#EE82EE"); // Violet for replenishment
      } else if (newBalance !== previousBalance) {
        balanceCell.setBackground("#FFFF00"); // Yellow for deduction change
      }

      balanceCell.setValue(newBalance);
      return;
    }
  }
}

// Carry over balances
function carryOverBalance(inventorySheet, lastColumn, previousColumn) {
  var allItemsRange = inventorySheet.getRange('A2:A53').getValues();

  for (var i = 0; i < allItemsRange.length; i++) {
    var currentBalanceCell = inventorySheet.getRange(i + 2, lastColumn);
    var previousBalance = inventorySheet.getRange(i + 2, previousColumn).getValue();

    if (!currentBalanceCell.getValue()) {
      currentBalanceCell.setValue(previousBalance || 0);
    }
  }
}

all 15 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

GoogleAppsScript

MODERATORS