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.


  • administrators

    @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.


  • administrators

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


Log in to reply
 

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.