Google Script onChange trigger executes when no change made (overnight)

0
votes

I have a Google Script running the sendEmails() function with the installable onChange trigger. This works well but I'm seeing that it also executes overnight, when no changes are made to the spreadsheet.

Anyone know what may be happening/ how to prevent this?

Here's the code that's running in the script editor:

/**
 * Sends emails with data from the current spreadsheet.
 */

function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 1; // Number of rows to process
  // Fetch the range of cells A2:C2
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[4]; // Fifth column
    var message = row[0]; // First column
    var subject = row[3]; // Fourth Column
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

In the executions/trigger log I see that a trigger executed last night at 5:07 am and the night before at 1:16am, when no changes were made to the spreadsheet

screenshot of trigger log

2
google-apps-scripttriggers
Does anyone else have access to your account. You might want to change your password just to make sure. - Cooper
Have a look at the revision version history, to see if there was an actual change taking place that made the trigger fire. - ziganotschka
No one else has access to the account and there are no changes in the revision history. I changed my password yesterday and updated the code to that which @Cooper suggested below, but still see a trigger at 12:13am (without any corresponding changes). The script is running off a sheet using the =Importrange() function to query information from another sheet. Could there be something going on with sync/refresh settings in Google Drive? - Mark
As a last resort step just delete all of this projects triggers. If that doesn't work I would probably copy this code to another spreadsheet and delete the old spreadsheet and start all over again. - Cooper
If nothing helps - this might be a bug. You can file it on Google Issue Tracker: developers.google.com/issue-tracker - ziganotschka

2 Answers

0
votes

Try this to see if it will eliminate the overnight emails.

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var numRows = 1;
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);
  var data = dataRange.getValues();
  for (var i=0;i<data.length;i++) {
    var row = data[i];
    var emailAddress = row[4]; // Fifth column
    var message = row[0]; // First column
    var subject = row[3]; // Fourth Column
    if(emailAddress && message && subject) {
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}
0
votes

Wasn't able to figure out the underlying issue, but created code which prevents the script from sending an email (to Asana to create a task in this case) for any row where an email was previously sent. 

var TASK_SENT= "TASK_SENT";

/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = sheet.getLastRow();
  // Fetch the range of cells UPDATE
  var dataRange = sheet.getRange(startRow, 1, numRows, 48);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var message = row[45] //Next Steps Column
    var staff = row[1] // Your Name Column
    var internal_part = row[7] //Who was the Check-in with? Column
    var external_part = row[13] //What Site was your Check-in With? Column
    var check_dt = row[2] //Date of Contact Column
    var check_dt = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yy")
    var site_disc = row[8] //What site was discussed?
    var emailAddress = row[46]; // Email address column
    var taskSent = row[47]; // Task_Sent column
    if (taskSent != TASK_SENT) { // Prevents sending duplicates
      var subject = staff + " " + 'check-in w/' + " " + internal_part + external_part + " " + 're:' + " " + site_disc + " " + check_dt + " " + 'Next Steps';
      if (emailAddress == "") {
        continue;}
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 48).setValue(TASK_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}