根据单元格内容调整Google Sheet触发器执行频率

Got it, let's figure out how to adjust your Google Sheet trigger's execution frequency based on cell content in the Watchlist sheet. Here's a practical solution that integrates seamlessly with your existing code:

First, choose a dedicated cell in your Watchlist sheet to define the trigger frequency. For example, let's use cell B2—you can change this to any cell that makes sense for your workflow. Valid values here could be:

• every minute (or everyminute)
• every hour (or everyhour)
• daily (or every day)
• A positive number (e.g., 30 for every 30 minutes)

Replace your existing code with this enhanced version, which includes functions to read the cell value, delete old triggers, and create new ones matching your desired frequency:

var EMAIL_SENT = "EMAIL_SENT";
var TRIGGER_FUNCTION_NAME = "sendEmailsAdvanced";
var FREQUENCY_CELL = "B2"; // Adjust this to your chosen cell

function sendEmailsAdvanced() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Watchlist");
  var d = new Date();
  var timeStamp = d.getTime();
  var currentTime = d.toLocaleTimeString();
  var startRow = 8;
  // Fix: Calculate actual number of rows to process (from startRow to last row)
  var numRows = sheet.getLastRow() - startRow + 1; 
  var dataRange = sheet.getRange(startRow, 1, numRows, 19);
  
  // ... rest of your existing email-sending code here
}

function updateTriggerBasedOnCell() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Watchlist");
  var frequencyValue = sheet.getRange(FREQUENCY_CELL).getValue().trim().toLowerCase();
  
  // First, clean up any existing triggers for our email function
  deleteExistingTriggers();
  
  // Create new trigger based on the cell's value
  switch(frequencyValue) {
    case "every minute":
    case "everyminute":
      ScriptApp.newTrigger(TRIGGER_FUNCTION_NAME)
        .timeBased()
        .everyMinutes(1)
        .create();
      break;
    case "every hour":
    case "everyhour":
      ScriptApp.newTrigger(TRIGGER_FUNCTION_NAME)
        .timeBased()
        .everyHours(1)
        .create();
      break;
    case "daily":
    case "every day":
      ScriptApp.newTrigger(TRIGGER_FUNCTION_NAME)
        .timeBased()
        .everyDays(1)
        .create();
      break;
    // Handle custom minute intervals
    default:
      var customMinutes = parseInt(frequencyValue);
      if (!isNaN(customMinutes) && customMinutes > 0) {
        ScriptApp.newTrigger(TRIGGER_FUNCTION_NAME)
          .timeBased()
          .everyMinutes(customMinutes)
          .create();
      } else {
        // Show alert if the cell value is invalid
        SpreadsheetApp.getUi().alert(`Invalid frequency in cell ${FREQUENCY_CELL}. Use "every minute", "every hour", "daily", or a positive number (e.g., 30 for 30-minute intervals).`);
      }
  }
}

function deleteExistingTriggers() {
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    if (allTriggers[i].getHandlerFunction() === TRIGGER_FUNCTION_NAME) {
      ScriptApp.deleteTrigger(allTriggers[i]);
    }
  }
}

To make this fully automatic, set up an onChange trigger that runs updateTriggerBasedOnCell() whenever you edit the frequency cell. Run this setup function once manually:

function setupOnChangeTrigger() {
  ScriptApp.newTrigger("updateTriggerBasedOnCell")
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onChange()
    .create();
}

After running this, any change to your frequency cell will automatically update the trigger for sendEmailsAdvanced().

• Quota limits: Google Apps Script has trigger execution limits (e.g., maximum 1 trigger per minute, daily execution caps). Make sure your frequency choice stays within these limits.
• Debugging: Add Logger.log("sendEmailsAdvanced ran at " + currentTime); to your email function to verify when it's executing.
• Manual override: You can always run updateTriggerBasedOnCell() manually from the script editor if you need to force a trigger update.

内容的提问来源于stack exchange，提问作者stefan