Cleaner way to send my email messages from Sheets

Question

I currently have a working Google script that reads in the values on a dashboard in Google Sheets for swimming pool chemical monitoring. People will submit a form and if those numbers are greater than or less than a set number, it will send an email with a specific message.

This works fine but if there are multiple numbers that are off, I will receive multiple emails which gets annoying.

I am looking for one simple, clean output instead of multiple emails.

Here is part of my code (the rest is the same just different pools).

     function sendEmails() {
      var sheet = SpreadsheetApp.getActiveSheet();

    //----vvvvvPool
      var IDPpHReading = sheet.getRange('A6:A7').getValue();
      var IDPpHTest = sheet.getRange('B6:B7').getValue();
      var IDPORP = sheet.getRange('A9:A10').getValue();
      var IDPSetPoint = sheet.getRange('B9:B10').getValue();

      var IDPCL = sheet.getRange('A12:A13').getValue();
      var IDP_Filter1_INF = sheet.getRange('A16:A17').getValue();
      var IDP_Filter1_EFF =  sheet.getRange('B16:B17').getValue();
      var IDP_Filter2_INF = sheet.getRange('A20:A21').getValue();
      var IDP_Filter2_EFF =  sheet.getRange('B20:B21').getValue();

// insert email here
    var emailAddress = "[email protected]";  
//-------------------- IDP pH 
    if (IDPpHReading  > 7.8) {
      var subject = "High pH Reading IDP";
      var message = " An Aquatic Specialist recently logged a chemical reading that was too high for the Indoor Pool. Please check the situation asap.";

      MailApp.sendEmail(emailAddress, subject, message);
    }
  else if  (IDPpHReading  < 7.2) {
      var subject = "Low pH Reading IDP";
      var message = " An Aquatics Specialist recently logged a chemical reading that was too low for the Indoor Pool. Please check the situation asap.";

      MailApp.sendEmail(emailAddress, subject, message);
    }
   if (IDPpHTest  > 7.8) {
      var subject = "High pH Test IDP";
      var message = " An Aquatics Specialist recently logged a chemical reading that was too high for the Indoor Pool. Please check the situation asap.";

      MailApp.sendEmail(emailAddress, subject, message);
    }
  else if  (IDPpHTest  < 7.2) {
      var subject = "Low pH Test IDP";
      var message = " An Aquatics Specialist recently logged a chemical reading that was too low for the Indoor Pool. Please check the situation asap.";

      MailApp.sendEmail(emailAddress, subject, message);
    }

};

My go for this would be to make each message unique :

var message 1;
var message 2;
etc

and then have the email - MailApp.SendEmail(emailAddress,subject, message1+message2....);

But I feel like that is a sloppy way to do it.

Just set subject = '' and message='' at the start, and then concatenate extra sections to the subject/body whenever a particular reading applies. — halfer

Hink Hink · Accepted Answer · 2017-06-01T09:04:09

I have had to do this a few times and what I usually do is use string concatenation to build the message and then send it:

  var subject = "Abnormal pH Reading IDP";    
  var message = 'An Aquatics Specialist recently logged a chemical reading at the indoor pool and found:\n';
  var needsCheck = false;

    if (IDPpHReading  > 7.8) {

          var message = message + "\nA reading that was too high.";
          var needsCheck = true;

        }
      else if  (IDPpHReading  < 7.2) {
          var message = message + "\nA reading that was too low.";
          var needsCheck = true;

        }
       if (IDPpHTest  > 7.8) {
          var message = message + "\nA test that was too high.";
          var needsCheck = true;

        }
      else if  (IDPpHTest  < 7.2) {
          var message = message + "\nA test that was too low.";
          var needsCheck = true;
        }

  if (needsCheck){
    var message = message + "\n\nPlease check the situation asap.";
    MailApp.sendEmail(emailAddress, subject, message);
  }

Cleaner way to send my email messages from Sheets

1 Answers