CODEWITHSUNDEEP
emailgoogle-apps-scriptgoogle-sheetsautomation
user12447927
user12447927

Reputation: 11

Google Sheets: Automated email when cell value is less than another

I am trying to make an inventory sheet where an automated email will be sent out when the inventory falls below a specific limit. I have set it so B2 is < C2, but I am not getting an email.

function sendEmailAlert() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var rangeA = sheet.getRange('A2:A8');

  var item = rangeA.getValues();

  var rangeB = sheet.getRange('B2:B8');

  var inventory = rangeB.getValues();

  var rangeC = sheet.getRange('C2:C8');

  var limit = rangeC.getValues();

  var toEmail = '[email protected]';

  var subject = 'Inventory to Order';

  var body = 'Item:' + item + 'needs to be ordered';

  for (i in item){

   if(inventory <= limit ) {

     MailApp.sendEmail(toEmail,subject, body);

    }
  }
}

Upvotes: 1

Views: 46

Answers (1)

Cooper
Cooper

Reputation: 64100

Try this:

function sendEmailAlert() {
  var sheet=SpreadsheetApp.getActiveSpreadsheet();
  var rangeA=sheet.getRange('A2:A8');
  var item=rangeA.getValues();
  var rangeB=sheet.getRange('B2:B8');
  var inventory=rangeB.getValues();
  var rangeC=sheet.getRange('C2:C8');
  var limit=rangeC.getValues();
  var toEmail='[email protected]';
  var subject='Inventory to Order';
  for (var i=0;i<item.length;i++){
   if(inventory[i][0]<=limit[i][0]) {
     var body='Item:' + item[i][0] + 'needs to be ordered';
     MailApp.sendEmail(toEmail,subject, body);
    }
  }
}

You could also add something like this if you want to avoid sending duplicates

function sendEmailAlert() {
  var sheet=SpreadsheetApp.getActiveSpreadsheet();
  var rangeA=sheet.getRange('A2:A8');
  var item=rangeA.getValues();
  var rangeB=sheet.getRange('B2:B8');
  var inventory=rangeB.getValues();
  var rangeC=sheet.getRange('C2:C8');
  var limit=rangeC.getValues();
  var rangeD=sheet.getRange('D2:D8');
  var sent=rangeD.getValues();
  var toEmail='[email protected]';
  var subject='Inventory to Order';
  for (var i=0;i<item.length;i++){
   if(inventory[i][0]<=limit[i][0] && sent[i][0]!="sent") {
     var body='Item:' + item[i][0] + 'needs to be ordered';
     MailApp.sendEmail(toEmail,subject, body);
     sent[i][0]="sent";
    }
  }
  rangeD.setValues(sent);
}

Upvotes: 1

Related Questions