Class ClockTriggerBuilder: Specific Date and Time (only once) Trigger in Google Sheet Script keeps repeatedly executing every minute

user_14286897 0
Viewed 417

The following script, and trigger, run as intended at the first instance. But then it does not stop! The trigger keeps executing once every minute thereafter!

I only need it to run ONCE, as programmed, and that's it! How do I stop the trigger from executing after the first, intended execution? Any help will be greatly appreciated! PS: This is my first attempt at writing a Google Sheet Script, so please make the answer simple to understand!

function sendWAnow(){
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Reports');
var Date = sheet1.getRange(3,3).getValue();  
var subject = sheet1.getRange(4,3).getValue();
var emailAddress = sheet1.getRange(5,3).getValue();
var message = sheet1.getRange(6,3).getValue();
ScriptApp.newTrigger('sendWAnow')
           .timeBased()
           .at(Date)
           .create();  
MailApp.sendEmail(emailAddress, subject, message);
}
google-apps-script google-sheets datetime
2 Answers

Explanation:

Your code operates as an infinite loop of triggers. The first time you execute sendWAnow() it will create a trigger which will execute sendWAnow() and then a new trigger will be created again.

Solution:

You should split the two functions, the one should create a trigger for the other one.

Execute only createTrigger():

function createTrigger(){
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Reports');
var Date = sheet1.getRange(3,3).getValue(); 
ScriptApp.newTrigger('sendWAnow')
           .timeBased()
           .at(Date)
           .create();
}

that will trigger sendWAnow() at the particular time.

function sendWAnow(){
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Reports');
var Date = sheet1.getRange(3,3).getValue();  
var subject = sheet1.getRange(4,3).getValue();
var emailAddress = sheet1.getRange(5,3).getValue();
var message = sheet1.getRange(6,3).getValue();

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

Solution

You are recursively calling your function without an end condition.

Since you just want to run this function once the best solution would be create another function (maybe called main()) which only task is to set up the Time Driven trigger.

For example:

function main() {
    var ss = SpreadsheetApp.getActiveSpreadsheet()
    var sheet1=ss.getSheetByName('Reports');
    var Date = sheet1.getRange(3,3).getValue();  
    ScriptApp.newTrigger('sendWAnow')
           .timeBased()
           .at(Date)
           .create();  
}

Reference

Time-driven triggers

Recursion

user_74536560
Related