Calling an onEdit function inside of an onEdit installable trigger. Will it error for users who cannot edit a different tab on the spreadsheet?

Question

Background: I'm attempting to develop a Google Sheet where a user inputs some process parameters and then initiates a macro which:

1) Copies the data to a Master Data Table on a different tab/sheet within the Google Sheets file.

2) Copies the data to a different tab/sheet in a format to feed some graphs to trend recent data.

3) Clears the Data Entry cells.

Note: All tabs/sheets in the file are protected with only me (owner) able to edit, except for the cells which require the user to enter data.

What I have so far: The macro worked flawlessly for me (owner) but other users were getting an error stating protection issues. I was using the onEdit function as a macro and it would be triggered by entering 'GO' into a target cell. This would call the custom function Transfer_Data(). I read more about installed triggers, discovered that the installed trigger would run under my permissions, and decided to try that. However, the installed trigger was set to run onEdit, which in turn runs the onEdit function built into the sheet. This means the onEdit function is activated twice, once as me (owner) and once as the user making the edit. The user initiated onEdit function on the spreadsheet will throw an error, but the one initiated by the installed trigger should run the code as intended. How do I break the vicious loop of continually calling the onEdit function twice as two different users for each spreadsheet edit?

function onEdit(e){
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('E16').setValue('BEBUG0');
  var editedRange = e.range;
 var editedRow = editedRange.getRow();
 var editedColumn = editedRange.getColumn();
 var editedCell = e.range.getA1Notation();
 var editedValue = e.range.getValue();

  if (editedCell == 'E15'){
    spreadsheet.getRange('E16').setValue('BEBUG1');
    if(editedValue == 'GO' || editedValue == 'Go' || editedValue == 'go' || 
editedValue == 'gO'){ 
      spreadsheet.getRange('E16').setValue('BEBUG2');
  Transfer_Data(); 
  }
  }
  else{
    spreadsheet.getRange('E16').setValue('YELLOW BOX NOT EDITED');
  }
}

Note: Setting range 'E16' in the above code is by way of working though debugging issues. I noticed it calling the onEdit function twice by making an edit to the sheet and noticing cell E16 looping through the text for the debug checks twice each time a single edit was made.

I think you'll want to rename your onEdit(e) function to something else and let it be the handler for your installable trigger. — Cooper

Rubén Rubén · Accepted Answer · 2019-01-11T19:45:54

How do I break the vicious loop of continually calling the onEdit function twice as two different users for each spreadsheet edit?

onEdit is a reserved function name to be used for simple triggers, by the other hand, installable triggers could run any any function. In order to avoid the described problem, change the name of your function. In other words

replace

function onEdit(e){

by something like

function installableOnEdit(e){

Calling an onEdit function inside of an onEdit installable trigger. Will it error for users who cannot edit a different tab on the spreadsheet?

1 Answers