Stock Inventory - Send email when cell value < 2 (Google Spreadsheet)

Question

I currently trying to create for stock inventory of some products that are frequently used in my workplace using google spreadsheet. Moreover, I'm trying to come up with a script that would send me an email when a certain product reaches a value below 2 so that I would know that a certain product needs to be restock. I'm do not know the basics of coding, but here's what I got so far:

function readCell() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");  
var ProductA = sheet.getRange("B2").getValue();
var Product B = sheet.getRange("B3").getValue();
var min = 2
if (ProductA<min) MailApp.sendEmail('n********@googlegroups.com', 'LOW REAGENT STOCK',     'Attention! Your stock of ProductA is running low. Please proceed to restock.');
if (ProductB<min) MailApp.sendEmail('n********@googlegroups.com', 'LOW REAGENT STOCK',     'Attention! Your stock of ProductB is running low. Please proceed to restock.');
}

I put the trigger on onEdit to run the script and I intent to expand the list with more products. The thing is that if one product as already reached a value below 2 and if a change another one, the script will send email for both of them. With more products, this becomes a nuisance, because I would received a bunch of emails if other values remain below 2. Can someone help me out with this? I couldn't find any solution to this so far and I would truly appreciate some help.

Thank you!

Answer 1

When the "onEdit" trigger fires, it receives the event object as parameter containing some useful information about the context, in which the edit action occurred.

For example,

    function onEdit(e) {

        // range that was edited
        var range = e.range; 

        //value prior to the edit action
        var oldValue = e.oldValue;

        //new value
        var value = e.value;

        //sheet the action came from
        var sheet = range.getSheet(); 

        //cell coordinates (if edited range is a single cell)
        //or the upper left boundary of the edited range

        var row = range.getRow(); 
        var col = range.getColumn();


      }

You can inspect the event object to get the cell that was edited and see if it's in column B.

var productsColIndex = 1; //column A index;
var inventoryColIndex = 2; //column B index

var range = e.range;
var value = e.value;
var sheet = range.getSheet();


var editedRow = range.getRow();
var editedCol = range.getColumn();

var productName = sheet.getRange(editedRow, productsColIndex).getValue();



//checking if 
//1) column B was edited 
//2) the product exists in column A
//3) new value is less than 2

if ((editedCol == inventoryColIndex) && productName && value < 2) {

    //code for sending notification email. 

 }

Finally, because simple triggers like onEdit() can't call services that require authorization, it's better to create a function with a different name and then set up the installable trigger manually. In your Script Editor, go to "Edit" -> "Current project's triggers" -> "Add a new trigger" , select your function name from the dropdown list, and pick the following options: "From spreadsheet", "On edit".