Is there an alternative to onEdit(e)?

Question

Disclaimer: I'm a relative newb.

I'm grabbing a user's calendar events and displaying them on a Google Sheet. One column gets partially filled in based on code. Users can then add their own values. Unfortunately, when they run my code again all their user-entered values will, of course, be lost.

Last night I decided to "save" the user-entered values on a new sheet by associating them with the event IDs. I tried to implement this using onEdit(e) but the trigger isn't working. (I am guessing because I'm grabbing the user's calendar?)

Is there an alternative to onEdit(e)? (It would be pretty easy to keep track of which values were generated by code -- and I could tell the added ones that way -- but to save their values users would have to click an extra button, which doesn't feel very user friendly...)

Answer 1

Creating an onEdit trigger programatically

The alternative to simple triggers is installable triggers. Simple triggers cannot perform events that require permission. Installable triggers can if the user using it provides authorization.

Since I'm always working on other peoples code I have a couple of helper functions that make creating the installable onEdit trigger easier to create.

This is what you will see in my code sometimes. It allows me to create the onEdit functions quickly while at the same time performing all of the house keeping chores that I want it to perform.

function createOnEditTrigger() {
  createOnEditTriggerForSpreadsheet('MyFunctionName');
}

But behind the scenes there's another helper function that takes that funcname and checks to make sure that there isn't already a trigger assigned to that function name. If there is then it won't create another one.

function createOnEditTriggerForSpreadsheet(funcname) {
  var ssid=SpreadsheetApp.getActive().getId();
  if(!isTrigger(funcname)) {
    ScriptApp.newTrigger(funcname).forSpreadsheet(ssid).onEdit().create();
  }   
}

This is the function that checks all the other triggers to make sure that there isn't another one with the same name.

function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

ScriptApp.newTrigger()

ScriptApp.getProjectTriggers()

While this is really handy for me. I would still recommend that you go into the triggers panel and set the notifications to immediate so that you can get a quick email from Google while debugging your new code. They will report errors immediately via email. It's extremely handy.

Animation: