Overview: the workbook calculates an Overall Score for each Record (or row) based on the Category Scores for each Record. Category Scores are derived from computations performed on hundreds of data points for each Record. I compiled code to send an email onChange called checkComplete (below) based on several examples and tutorials I found online. checkComplete would only send an email if the status of the Record changed to "Complete." An onChange trigger is required since the workbook uses Importrange to load the raw data, and no human interaction takes place for an onEdit trigger.
The problem began when I noticed an email would be sent upon any change being made, not just the status change in column O from a blank cell to "Complete". But the code worked well enough until I made significant edits to the Google Sheets workbook that included:
Modifying computational formulas to display a set number of decimals
e.g. =if(round('2-Comp'!F14,4)=0,"",round('2-Comp'!F14,4))
Moving columns around
e.g. switching columns N and O, where N was the original column checked by the onChange trigger
Updating source data, which therefore updated scores.
e.g. a Record's data point changed from $52,420 to $54,323
Then the email bombardment began. The net result is that the script began to send an email for every Record starting with the first record, and would generate the following error messages in succession:
Service invoked too many times for one day: email. (line 47, file "checkComplete")
Exceeded maximum execution time
Service using too much computer time for one day
I have since commented out MailApp.sendEmail() to cease blowing up my inbox, but the script continues to generate the "Exceeded maximum execution time" and "Service using too much computer time for one day". My thought was that the script was working off all the changes described above, but now that this problem has persisted for over a week I decided to seek out help.
I thought the issue is with the array, specifically var oldValues, so I tried:
to call flush() function to clear out the array, which did not solve the issue.
to empty the array as per this thread - How do I empty an array in JavaScript?, but that did not resolve the issue either.
I now think the problem may be related to the logger, but to be honest I am stumped.
Below is the script's source code in its entirety:
checkComplete.gs
var admin_email='xxxxxxx@xxxxxxx.com'; //<- list of email recipients goes here
function checkComplete() {
var sh = SpreadsheetApp.getActiveSheet();
var values = sh.getRange('O14:O').getValues().join('-');
if(PropertiesService.getScriptProperties().getKeys().length==0){ // first time you run the script
PropertiesService.getScriptProperties().setProperty('oldValues', values);
return;
}
var oldValues = PropertiesService.getScriptProperties().getProperty('oldValues').split('-');
var valuesArray = values.split('-');
while (valuesArray.length>oldValues.length){
oldValues.push('x'); // if you append some rows since last exec
}
Logger.log('oldValues = '+oldValues)
Logger.log('current values = '+valuesArray)
for(var n=0;n<valuesArray.length;n++){
if(oldValues[n] != valuesArray[n]){ // check for any difference
sendMail(n+1,valuesArray[n]);
}
}
PropertiesService.getScriptProperties().setProperty('oldValues', values);
}
function sendMail(row,val){
Logger.log('value changed on row '+row+' value = '+val+' , mail sent');
var rank = SpreadsheetApp.getActiveSheet().getRange(row,1).getValue();
// other var that I deleted here to shorten this post
// MailApp.sendEmail(admin_email,'message that calls vars');
}
I expect to receive an email only upon the change of a Record's status, indicated in column O, from a blank cell to "Complete". All other data updates should not trigger any email. Any advice/help is appreciated. Thank you for taking the time to review my problem.
