Installable Triggers to use multiple onEdit

Question

• I have created 2 functions that run onEdit (tested and working when named onEdit)

• The first one sets the value (new Date) of a row >7 in Column D onEdit of Column C.

function CompleteTime() { //Function to add a time stamp to Complete Time Column D
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveCell();
  var row = range.getRow();
  var col = range.getColumn()
  if(row>7 && col==3){
    sheet.getRange(row, 4).setValue(new Date());
    
  }
}

• The second one sets a formula in Row 8 Column E onEdit of Row 8 Column D

function Duration() { //Function to set formula in column E to calculate duration from start time to first complte time
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveCell();
  var row = range.getRow();
  var col = range.getColumn()
  if(row==8 && col==4){
    sheet.getRange(row, 5).setFormula("=SUM(D8-B3)");
    
  }
}

• I have created 2 installable triggers to run these functions onEdit, and the first one runs fine, but the second one doesn't trigger the first one. The value that is entered by the first function doesn't trigger the second function.

• Everything I've read suggests this is the way to get multiple onEdits to run in a single sheet, but I am stuck here.

Answer 1

One of the restrictions of triggers is that "Script executions and API requests do not cause triggers to run", meaning that you need to manually include the Duration() call after inserting the completion time.

The example below isn't the only way to accomplish this, but it should give you an idea of what I'm trying to describe.

function onEdit(e) {
  var row = e.range.rowStart;
  var col = e.range.columnStart;
  
  if (row == 8 && col == 4) {
    insertDurationFormula(e.range.offset(0, 1));
  } else if (row > 7 && col == 3) {
    insertCurrentTime(e.range.offset(0, 1));
    insertDurationFormula(e.range.offset(0, 2));
  }
}

function insertCurrentTime(cell) {
  cell.setValue(new Date());
}

function insertDurationFormula(cell) {
  cell.setFormula("=SUM(D8-B3)");
}

Also note that I'm using the event object included with edit triggers. Using the event object can help simplify your code a bit and reduce unnecessary calls.

Answer 2

It is not good practice to have multiple onEdit triggers in one spreadsheet

In case of simple onEdit triggers, it is not possible to have more than one per Apps Script project, in case of installable ones - it can cause to conflicts.

Instead, have only one function bound on a trigger and call from there on other function depending on the condition.

Sample:

function bindmeOnTrigger() {  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveCell();
  var row = range.getRow();
  var col = range.getColumn()
  if(row==8 && col==4){
    function1();    
  } else if(row>7 && col==3){
    function2();    
  }
}
function function1(){
  ...
}
function function2(){
  ...
}

Or simply:

function bindmeOnTrigger() {  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveCell();
  var row = range.getRow();
  var col = range.getColumn()
  if(row==8 && col==4){
    sheet.getRange(row, 5).setFormula("=SUM(D8-B3)");    
  } else if(row>7 && col==3){
    sheet.getRange(row, 4).setValue(new Date());    
  }
}