I'm new to triggers, and I've been reading about total trigger runtime limits. I've tried to create the below onEdit(e) script to limit the amount of times the trigger is executed. Essentially the script calls if an edit is made to a specific sheet in a specific named range, but it looks like in the G-Suite dev hub it's tracking onEdit executions outside of those parameters.
Any expertise or advice would be greatly appreciated!
I've been monitoring my G-suite dev hub "My Executions" page. I don't know enough about triggers and can't find enough literature on them to make sure the script is optimized.
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
var ui = SpreadsheetApp.getUi();
//Row & column indexes of the active cell
var col = e.range.getColumn();
var row = e.range.getRow();
//account for undefined
if(e.oldValue != undefined)
var oldvalue = e.oldValue;
else
var oldvalue = '';
if(col != 3)
return;
if(sh.getName() === 'Sheet1') {
var myRange = ss.getRange('Sheet1NamedRange');
if (row < myRange.getRow() && row > myRange.getLastRow())
return;
if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow() && e.value != e.oldvalue) {
ui.alert('Changed Value','You have changed ' +oldvalue+ '. Use the undo command (CMD+Z or CTRL+Z) to revert the change!',ui.ButtonSet.OK);
return;
}
}
if(sh.getName() === 'Sheet2') {
var myRange = ss.getRange('Sheet2NamedRange');
if (row < myRange.getRow() && row > myRange.getLastRow())
return;
if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow() && e.value != e.oldvalue) {
ui.alert('Changed Value','You have changed ' +oldvalue+ '. Use the undo command (CMD+Z or CTRL+Z) to revert the change!',ui.ButtonSet.OK);
return;
}
}
if(sh.getName() === 'Sheet3') {
var myRange = ss.getRange('Sheet3NamedRange');
if (row < myRange.getRow() && row > myRange.getLastRow())
return;
if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow() && e.value != e.oldvalue) {
ui.alert('Changed Value','You have changed ' +oldvalue+ '. Use the undo command (CMD+Z or CTRL+Z) to revert the change!',ui.ButtonSet.OK);
return;
}
}
};
I haven't received any quota warning limits yet, but I'm just trying to plan ahead! Right now all onEdits are ranging from .071 - 5.8 seconds.