Change Google Sheet trigger frequency based on cell contents

Question

In my Google Sheet "Watchlist" I have the following Code:

    var EMAIL_SENT = "EMAIL_SENT";

function sendEmailsAdvanced() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Watchlist");  // To only handle the Watchlist sheet  
  var d = new Date();
  var timeStamp = d.getTime();
  var currentTime = d.toLocaleTimeString();
  var startRow = 8;  // First row of data to process
  var numRows = sheet.getLastRow()
  var dataRange = sheet.getRange(startRow, 1, numRows, 19)  // Fetch the range of cells
  // Fetch values for each row in the Range.
  var data = dataRange.getValues() ;
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    if (row[2] === "yes" && row[18] === "" ) {    // Trigger only if Column C is "Yes"
      var emailAddress = row[0];  // First column
      var message = row[1];       // Second column
      var emailSent = row[19];
     if (emailSent != EMAIL_SENT) {
      var subject = "Buy Trigger for " + row[3] + " has been reached! Last updated: " + currentTime; // Add "Yes" although by your trigger logic it will always say yes in the email
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 19).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();
     }
    }   
  }
}

This sends an email whenever the respective row in Column C contains "yes" and afterwards writes the text "EMAIL_SENT" into Column S. Once Column S contains "EMAIL_SENT" no further mails are sent. A time-driven trigger set to "every minute" calls this function.

Now I want to add that I can define the trigger frequency in the Google Sheet itself. Therefore I want to be able to define the hourly frequency in Cell B3 and the minute frequency in Cell B4. The script should then programmatically create a trigger using that information, something like: "If Cell H2 = "Yes" then Create trigger using B3 and B4 and send an email every x minute/x hour as long as Column C contains "Yes".

I found this snippet which programmatically creates a trigger but I have no idea how I can reference it to cell contents and overwrite the existing trigger which is set to "every minute":

ScriptApp.newTrigger('myFunction'):   create new trigger
         .timeBase()              :   build time-based trigger
         .everyHours(6)           :   every 6 hours
         .create()                :   creates the trigger

The respective Google Sheet can be found here: Watchlist Sheet

So I wrote this onEdit() function now in order to create an installable trigger via a simple onEdit trigger but whenever I change cell B4 this does not create the new trigger as the onEdit trigger appears not to be called. Any idea?

function onEdit() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Watchlist");  // To only handle the Watchlist sheet 

// Deletes all triggers in the current project.
 var triggers = ScriptApp.getProjectTriggers();
 for (var i = 0; i < triggers.length; i++) {
   ScriptApp.deleteTrigger(triggers[i]);
 }

// Create new trigger based on B4 minute information
ScriptApp.newTrigger('sendEmailsAdvanced')
    .timeBased()
    .everyMinutes(sheet.getRange("B4").getValue())
    .create();
}

Instead of a hard coded 6, use your value. You can use other values from your sheet to determine whether to call everyHours or some other time unit, e.g. if (b3value > 0) { ... — tehhowch
Thanks but how do I reference the value of B3 as I cannot just type .everyHours(b3)? — stefan
the code you posted shows how to acquire values from a given worksheet: someSheet.getRange("someRange").getValue(). Please review the Apps Script documentation on any methods that are unfamiliar. Some allow for multiple styles of input (technically phrased as 'have multiple method signatures'), e.g. Sheet.getRange accepts an "A1" style string, 2-4 numeric values, etc. These should appear in the editor's autocomplete functionality. — tehhowch
when you make the trigger, it is made from the value at that instant. It is not a perpertual link, where changes get reflected. If you change the value in b3, you will want to 1) delete the previous trigger you made, and 2) create a new one. — tehhowch
you could use a simple onEdit(e) function (look up "simple triggers" in apps script documentation) to call the time driven creator function when the event range is b3 or b4 on the desired configuration sheet. — tehhowch

Jared Pinkham Jared Pinkham · Accepted Answer · 2018-04-01T05:15:23

Simple triggers cannot do anything that requires permission, that includes modify triggers. You will need to use an installable onedit:

function myFunction() {
  ScriptApp.newTrigger('bar')
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()).onEdit().create();

}




function bar(e){
  var triggers = ScriptApp.getProjectTriggers();
 for (var i = 0; i < triggers.length; i++) {
   if(triggers[i].getEventType() != ScriptApp.EventType.ON_EDIT){
   ScriptApp.deleteTrigger(triggers[i]);
   }
 }

  ScriptApp.newTrigger('baz')
    .timeBased()
    .everyMinutes(
     SpreadsheetApp.getActiveSheet().getRange("a1").getValue())
    .create();
}

function baz(){}

the job of myFunction could be done in the trigger editor since it is onetime use

This will only work if A1 has a valid number of minutes for a minute trigger: 5,10,15,30 Hours like in your first function would be 1,2,4,6,8,12

With an invalid number the old trigger will be deleted but the new one won't be created and you won't know about it until you get the error email that i believe defaults to once a day at midnight.

You will likely need to make a function that evaluates the value in the range rather than using the range value directly:

...

    ScriptApp.newTrigger('baz')
      .timeBased().everyMinutes(
       time(SpreadsheetApp.getActiveSheet().getRange("a1").getValue()))
      .create();

...

function time(t){
  switch(t){
    case "A":
      return 5;
    case "B":
      return 10;
    case "C":
      return 15;
    default:
      return 30;
  }
}

Change Google Sheet trigger frequency based on cell contents

1 Answers