Trying to send automated email for low values, need script to send for multiple ranges

Kathryn 0
Viewed 45

I'm creating an inventory spreadsheet and want to get an automated email anytime the percentage hits below 20 that I have formulated in Column H, but I'm only able to get the script to send for the first Range (H2), and it won't send an email for the remaining Ranges (H3:H100). How do I get it to send an automated email anytime any cell in Column H hits below 20, not just the first?

This is what I have so far:

function CheckInventory() {

  // Fetch Low Inventory

  var InventoryStat = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Current Inventory List").getRange("H2:H100"); 

  var CheckInv = InventoryStat.getValue();

  var ui = SpreadsheetApp.getUi(); 


  // Check Low Inventory

  if (CheckInv < 20) {
// Fetch the email address
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet5").getRange("B2");

var emailAddress = emailRange.getValue();

// Send Alert Email.

var message = 'Low Stock - Inventory Percentage at ' + CheckInv; // Second column

var subject = 'Low Inventory Alert';

MailApp.sendEmail(emailAddress, subject, message);} 

}
google-sheets google-apps-script
1 Answers

Try this:

function CheckInventory() {
  const ss = SpreadsheetApp.getActive();
  const vs = ss.getSheetByName("Current Inventory List").getRange("H2:H100").getValues().flat();
  const emailAddress = ss.getSheetByName("Sheet5").getRange("B2").getValue();
  const subject = 'Low Inventory Alert';
  var ui = SpreadsheetApp.getUi();
  vs.forEach(h => {
    if (h < 20) {
      let message = `Low Stock - Inventory Percentage at ${h}`;
      MailApp.sendEmail(emailAddress, subject, message);
    }
  });
}
user30hu0
Related