How can I make an installable onEdit trigger on button press?-CodePudding

So, I know the onEdit functions triggers when there is an Edit in the document. Does it also trigger when a script edits the document?

I specifically need an installed.onEdit function, but I only want it to trigger when a button was pressed. To test it, I assigned to a drawing a script that inserts a timestamp in the active cell - then the onEdit functions checks if that cell value equals the date and if so, it should colour that cell in a certain background.

Problem is, it doesn't work. Here's the code - help me out, please!

function buton() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getActiveCell();
  var column = cell.getColumnIndex();
  var value = cell.getDisplayValue();
  const date = new Date();

  if(column == 1){
    cell.setValue(date);
  }
}

function installedOnEditTrigger (e) {
  if(value == date){
    cell.setBackground('#cfdaaa');
  }
}

I tried to create a separate onEdit function that would check everytime there was an edit whether the value of a cell is a specific one - if so, would change colour of the background. The script only inserts the timestamp in the cell, but does not colours the background.

This is just a try-out, the actual reason I need the installed.onEdit function is because I need the script to always run as the owner, as to allow the script to remove from editing privileges other users that press the button.

CodePudding user response：

There are a couple of issues with the approach you describe.

You can install a trigger in the buton() function, but that trigger will run under the account of the user who clicked the button, rather than under the account of the owner of the spreadsheet.
No events get sent when a script writes to the spreadsheet, so installedOnEditTrigger(e) does not get run when the user clicks the button.

Since you have kindly made it plain that this is an XY question, I would propose a different solution. Use a checkbox instead of a button and have a regular installable on edit trigger watch that checkbox. Reset the checkbox to false in the trigger to let another user tick it afterwards.

This way, you do not need to dynamically install and remove triggers. One "permanently" installed trigger that runs under the account of the spreadsheet owner is enough.