Google Script onChange trigger executes when no change made (overnight)
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
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();
}
}
}