Sending stock alert emails using google apps script

Question

I am trying to setup a simple google apps script to send emails when inventory on various components hits the defined threshold. After reading and searching I put together a script that appears to work but starts to send erroneous emails when I increase the range being pulled from my spreadsheet.

Here's my code and further details are below.

var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("Inventory Report");
var howFar = 5 //sheet.getMaxRows(); // how many rows of data
var onHand = sheet.getRange(2,14,howFar,1).getValues();
var minimum = sheet.getRange(2,16,howFar,1).getValues();
var itemNum = sheet.getRange(2,1,howFar,1).getValues();
var itemName = sheet.getRange(2,2,howFar,1).getValues();

var sendTo = "[email protected]";
var overStock = 1.5;  // warning factor over minimum

function stockAlert() {
  for (var i = 0; i < onHand.length; i++) { 
    if (onHand[i] < minimum[i] * overStock && onHand[i] > minimum[i]) {
        MailApp.sendEmail( sendTo , "Testing Stock Reorder Notifications", itemNum[i] + " " + itemName[i] +" - Stock is low! Current stock is " + onHand[i] + ". Minimum is " + minimum[i] + ".");
    }
    else if (minimum[i] > 0 && onHand[i] < minimum[i]) {
        MailApp.sendEmail( sendTo , "Testing Stock Cirtical Notifications", itemNum[i] + " " + itemName[i] +" - Stock is Critical and will be depleted! Current stock is " + onHand[i] + ". Minimum is " + minimum[i] + ".");
    } 
  }
}

In my sheet the values for minimum are 200, 400, 200, 300, 600
In my sheet the values for onHand are 270, 270, 920, 920, 1830

This means I should see one low stock email for the first set of values, one critical stock email for the second set of values and no emails for the last 3 sets of values.

If var howfar = 3, the script sends the two appropriate emails. If var howfar = 5, I get a third critical stock email for the fifth set of values that should not be sent. Interestingly the email body shows that it's referencing the correct set of values but else if should be false.

The body of the incorrect email reads:

itemNum itemName - Stock is Critical and will be depleted! Current stock is 1830. Minimum is 600.

Given my extensive background in not coding, I hope and assume this will be a simple fix, but any all help is greatly appreciated!

Answer 1

Any chance the values are treated as text in the spreadsheet? Note that the string "1830" is indeed < the string "600". If they are text in the spreadsheet (rather than numbers), then when Apps Script reads the values in, they will be kept as Strings.

edit: indeed, this is the source of your issue - you compare 2D arrays at the Array level:

Logger.log(minimum[i]); // "[600.0]"
Logger.log(typeof minimum[i]); // object

Logger.log(minimum[i][0]); // 600.0
Logger.log(typeof minimum[i][0]); // number

The simplest fix is then to simply access the desired element of the 2D array. Since you acquired only single columns, there is only 1 element in each inner array (at index 0). Thus, <array>[i][0] instead of <array>[i].

An extension to this, which will work for situations in which the sheet values may be text, is to explicitly cast to a number before comparing by using the JS function parseInt(val, radix). Assuming minimum and others are 2D arrays as they are in your question code:

for (var i = 0; i < onHand.length; i++) {
  var min = parseInt(minimum[i][0], 10),
      avail = parseInt(onHand[i][0], 10);
  if (avail < min) {
    // send critical stock email
  }
  else if (avail < min * overStock) {
    // send reorder email
  }
  else {
    // on hand amount is > needed
  }
}

For a blank string, e.g. parseInt("", 10), or other non-numeric inputs the return value is the number NaN which is neither > or < than actual numbers, so bad inputs should not result in an email being sent.

---

A different issue is that your script populates global variables with interface calls, which results in slower execution of any script. A better approach is to wrap the related setup in a function:

function getStockAmounts() {
  // Return an object of the inventory values.
  const stock = SpreadsheetApp.getActive().getSheetByName("some name");
  const numHeaders = 1,
        numVals = stock.getLastRow() - numHeaders;
  return [
    {p: 'minimum', col: 16},
    {p: 'onHand', col: 14},
    {p: 'itemName', col: 2},
    {p: 'itemNum', col: 1}
  ].reduce(function (obj, key) {
    obj[key.p] = stock.getRange(numHeaders + 1, key.col, numVals, 1)
      .getValues()
      // Return a 1-D array, rather than a 2-D array, since all these are single-column variables.
      .map(function (row) { return row[0]; });
    return obj;
  }, {'numVals': numVals});
}

and then call this from your script:

function foo() {
  const stocks = getStockAmounts();
  for (var i = 0; i < stocks.numVals; i++) {
    var min = stocks.minimum[i]; // 1D arrays, so only 1 index is needed.
    ...

• Range#getValues()
• Array#reduce