In Google Apps Script, how to send an email only one time if condition met

Question

I'm trying to make automatic email send when a certain cell has required value. I figured out how to do this continually, i.e. a trigger is set to fire each day at a certain time of day to check the condition and send email if it's met. But I'd like the trigger to stop working after email was sent once. I thought I could do it this way:

I have a function which sends an email if condition is met. If condition is not met, a trigger is created, which will run the function again, once. And so on, trigger will continue to be created until once a condition is met.

Here is a code I wrote:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssName = ss.getName();
var emailAddress = ss.getRange("Tab1!B4").getValue();
var Test1 = ss.getRange("Tab1!B6").getValue();

function SendEmailOnce(){
  if (Test1 <= 10) {
    MailApp.sendEmail({
      to: emailAddress,
      subject: "Sample subject: " + ssName,
      htmlBody: "Sample message<br/> Regards, robot",
    });
    }
    else {
      function createTriggerCheckAgain() {
      ScriptApp.newTrigger('SendEmailOnce')
      .timeBased()
      .after(60 * 1000)
      .create();
      }
    }
}

For some reason it doesn't work. When I run the function while Test1 value being 15, and then change the value to 5, no email is sent.

The parts of it work if they are separate: 1. The email is sent if the value is 5 when I run the function. 2. The email is sent after a minute after I run the function of creating that trigger while value is 5.

Maybe there is a simpler way to do this? I'm a newbie in JavaScript. Thanks in advance for any suggestions.

Douglas Gaskell Douglas Gaskell · Accepted Answer · 2016-04-28T22:31:01

When your trigger runs after 60 seconds, Test1 is no longer defined, so you cannot check if undefined is <= 10. The reason for this is because only the code in SendEmailOnce is executed, not the code outside of it. The same also goes for the emailAddress variable.

You either need to include your first four lines of code in a function that you can call, which returns the value of Test1 and emailAddress or include it in your SendEmailOnce function.

Modified Code:

function SendEmailOnce(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssName = ss.getName();
  var emailAddress = ss.getRange("Tab1!B4").getValue();
  var Test1 = ss.getRange("Tab1!B6").getValue();

  if (Test1 <= 10) {
    MailApp.sendEmail({
      to: emailAddress,
      subject: "Sample subject: " + ssName,
      htmlBody: "Sample message<br/> Regards, robot",
    });
  }
  else {
    function createTriggerCheckAgain() {
      ScriptApp.newTrigger('SendEmailOnce')
      .timeBased()
      .after(60 * 1000)
      .create();
    }
  }
}

In Google Apps Script, how to send an email only one time if condition met

1 Answers