onEdit(e) "total trigger runtime" - Google Sheets

Question

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.

Answer 1

Not sure if you can manage to do appx. 900 calls with 6 sec. execution a day (e.g. 90min trigger runtime for consumers), but if you are worried, you can reduce the number of calls by removing all of the getRow(), getLastRow(), getLastColumn() and writing them into variables outside the scope of your if statements (currently you unnecessarily invoke this methods over myRange).

For example, your first call can look like this:

var rowIdx, lRow; //initialize variables once for all checks;
if(sh.getName() === 'Sheet1') {
  var myRange = ss.getRange('Sheet1NamedRange');

  rowIdx = myRange.getRow();
  lRow   = myRange.getLastRow();

  if (row<rowIdx && row>lRow)
  return;

  if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= rowIdx && row <= lRow && 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;
  }
}

Also, your source spreadsheet is already written to source event object parameter, you don't need the getActiveSpreadsheet(), just reference it via e.source.

Furthermore, alert() dialog suspends server-side function (thus adding to execution runtime). Since your only purpose is to inform the user, use the showModalDialog() method instead.

Useful links

1. showModalDialog() reference;
2. onEdit() event object reference;
3. Quotas for Google Services;