CODEWITHSUNDEEP
google-sheetsscriptingspreadsheet
benthelemon
benthelemon

Reputation: 19

Send an email from sheets when values are below a set value

I'm trying to write a script for a google spreadsheet I use for inventory tracking. When the on hand value gets below the minimum inventory value, I want an email to trigger that includes the part number and the quantity I need to make to get back to our minimum inventory quantity, ie. "PN Needs # Made". I've had success with just sending an email for one value, but I cannot figure out how to have the script look over a range without repeating the script for every row. example table The image is a screenshot of what I'm working on. For example, since V1276 needs one more piece made to meet the min. inv. qt., I'd like the email to tell me "V1276 Needs 1 Made". The sample code I've been playing with:

    function CheckParts() {
    var minVal = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master List").getRange("H2");
    var valule = minVal.getValue();
    if (valule < minVal){
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("B2");
    var emailAddress = emailRange.getValues();
  
    // Send Alert Email.
    var message = 'You need to make ' + monthSales; // Second column
    var subject = 'Low Parts Alert';
    MailApp.sendEmail(emailAddress, subject, message);
    }
}

Upvotes: 0

Views: 96

Answers (1)

NightEye
NightEye

Reputation: 11214

It seems you want to run it manually. If you want, then have this modification:

Script:

function CheckParts() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("Master List");
  var lastRow = dataSheet.getLastRow();
  // Get A2:I values
  var values = dataSheet.getRange(2, 1, lastRow - 1, 9).getValues();
  var emailAddress = ss.getSheetByName("Sheet1").getRange("B2").getValue();
  // Filter rows where H > I
  values.filter(e => e[7] > e[8]).forEach(e => {
    var pN = e[0];
    var description = e[1];
    var minInv = e[7];
    var currInv = e[8];
    // Send a more detailed email
    var message = `Currently, we only have ${currInv} items of "${description}".\nTo reach minimum inventory quantity of ${minInv}, you need to make ${minInv-currInv} additional item.`;
    var subject = `Low Parts Alert for ${pN}`;
    MailApp.sendEmail(emailAddress, subject, message);
  });
}

Data Sample:

sample

Output:

output1 output2

Testing after changing I3 to 2:

output3

Note:

  • Script above will check all rows that satisfies the condition H > I. If it falls under that, then it will send an email for each row.
  • There is also an option to automatically send an email once it detects that you either edited H or I column values and it satisfies the condition (e.g. H > I). For that, we need an onEdit installed trigger. If you prefer this onEdit approach, mention it below so I can provide it for you.

Upvotes: 1

Related Questions