How to send an email in google sheets if certain conditions exist

jrenforth

Reference - https://codingislove.com/send-email-google-sheets

If you don't mind me asking for help, I need to send an email if certain conditions exist within a google sheet:

Example:

Cell A1 contains a location for a meeting: "Room 202"

Cell A2 contains a room capacity number, i.e. "50"

Cell A5 contains the sum total of reservations received for a 4:00pm meeting = "30"

Cell A6 contains the sum total of reservations received for a 6:30pm meeting = "60"

4:00pm is fine, but 6:30pm is overbooked.

My email must go to [email protected] and state that "the 6:30pm meeting in Room 202 is overbooked".

Can you give me some direction please--thank you.

ranjithkumar10

@jrenforth Something like this should work assuming that timings are in B column (B5 and B6 in this case)

function conditionalMail(){
var sheet = SpreadsheetApp.getActiveSheet();
var maxBookings = sheet.getRange("A2").getValue();
var roomNum = sheet.getRange("A1").getValue();
var range = sheet.getRange("A5:B6");
var bookingData = range.getValues();
for (i in bookingData) {
  var row = bookingData[i]
  var bookingsCount = row[0];
  var timing = row[1];
  if (bookingsCount > maxBookings) {
      MailApp.sendEmail("[email protected]","Overbooked rooms","The " + timing + " meeting in " + roomNum + " is overbooked"); 
  }
}
}

jrenforth

This seems to run and complete, but the email message never arrives.

ranjithkumar10

Is the email delivering late or it isn't coming at all?