How to send an email in google sheets if certain conditions exist
-
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.
-
@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"); } } }
-
This seems to run and complete, but the email message never arrives.
-
Is the email delivering late or it isn't coming at all?