CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
J.E.T
J.E.T

Reputation: 27

Single onEdit function works - multiple of the same function dont

I am using Google Sheets and I have two functions onEdit and onCommsEdit that work great and add a timestamp to their respective sheets. If I copy and paste one of them exactly and give a new function name - the additional functionality will not seem to work.

Am I ordering things incorrectly? Or should I be naming all sheets in the same function? (I had created duplicates because the write to columns varied by the sheet.)

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name:"Last Row", functionName:"goto_last"}];
  sheet.addMenu("Shortcuts", entries);
   myFunction();
};

function goto_last() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getActiveSheet();

  var lastrow = mysheet.getLastRow();

  mysheet.setActiveCell(mysheet.getDataRange().offset(lastrow-1, 0, 1, 1));
}; 

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Primary Log" ) { 
  var r = s.getActiveCell();
  var user = Session.getActiveUser().getEmail();
  if( r.getColumn() != 13 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-08:00", "yyyy-MM-dd, hh:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange('M' + row.toString()).setValue(time);
    SpreadsheetApp.getActiveSheet().getRange("N" + row.toString()).setValue(user);
  }
  };
 };

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getActiveSheet();

  var lastrow = mysheet.getLastRow();

  mysheet.setActiveCell(mysheet.getDataRange().offset(lastrow-1, 0, 1, 1));
}; 

function onCommsEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Received Report" ) { 
  var r = s.getActiveCell();
  var user = Session.getActiveUser().getEmail();
  if( r.getColumn() != 11 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-08:00", "yyyy-MM-dd, hh:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange('K' + row.toString()).setValue(time);
    SpreadsheetApp.getActiveSheet().getRange("L" + row.toString()).setValue(user);
  }
  };
 };

Upvotes: 1

Views: 77

Answers (1)

tehhowch
tehhowch

Reputation: 9862

You must create a trigger in order for a function to be called automatically.

It is not recommended to have multiple functions all called with an onEdit condition, as the execution order is not well defined. I also do not recommend naming a function that requires an installed trigger such that it matches the corresponding simple trigger -- in other words, a function that requires authorization scopes and is to be run after the spreadsheet values are modified should not be called onEdit(e), as that is the name of function which activates the simple trigger. If your function does not require authorization scopes, then you can use the simple trigger.

As far as your program design, you should use flow control. Rather than multiple functions, simply branching is sufficient:

function calledOnEdit(e) {
  if(!e) { throw new Error("This function requires an event object"); }

  // Use the event object rather than lookups that assume the active cell was the edited cell:
  var s = e.range.getSheet();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  if( s.getName() == "Primary Log" && col != 13 ) { //checks the column
    logTimestamp(s, "M" + row + ":N" + row);
  } else if( s.getName() == "Received Report" && col != 11 ) { //checks the column
    logTimestamp(s, "K" + row + ":L" + row);
  }
}
function logTimestamp(sheet, a1ref) {
  var user = Session.getActiveUser().getEmail();
  var time = Utilities.formatDate(new Date(), "GMT-08:00", "yyyy-MM-dd, hh:mm:ss");
  sheet.getRange(a1ref).setValues([[time, user]]);
}

Upvotes: 1

Related Questions