CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
stefan
stefan

Reputation: 115

Change Google Sheet trigger frequency based on cell contents

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();
}

Upvotes: 0

Views: 948

Answers (1)

Jared Pinkham
Jared Pinkham

Reputation: 501

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;
  }
}

Upvotes: 1

Related Questions