CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Andrew Harrell
Andrew Harrell

Reputation: 63

Installable Triggers to use multiple onEdit

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());
    
  }
}

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)");
    
  }
}

Upvotes: 0

Views: 734

Answers (2)

Diego
Diego

Reputation: 9581

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.

Upvotes: 1

ziganotschka
ziganotschka

Reputation: 26836

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());    
  }
}

Upvotes: 1

Related Questions