Can't create a onOpen() trigger Google App Script

Question

Hello I'm trying to create a script that runs when I open a new spreadsheet but right now I keep getting this error message with my onOpen() trigger Unexpected error while getting the method or property forSpreadsheet on object ScriptApp.TriggerBuilder. I have created this as a "Workspace Add-On" with all the proper auth but for some reason keep getting this error. Would love any suggestions :)

Here is my code

// [START apps_script_triggers_onopen]
function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive()
  console.log(ss)
  ScriptApp.newTrigger('myFunction')
      .forSpreadsheet(ss)
      .onOpen()
      .create();
}

You really don't want to create an onOpen installable trigger because then you will have a simple trigger named onOpen and an installable trigger named onOpen and that may be the issue that you have. Try naming your function onMyOpen or whatever you want but not onOpen and go into the project triggers and delete the other one if it's still there. — Cooper
@Cooper Thanks for the tip! but I don't have any triggers in the project triggers. And which function were you suggesting I rename the createSpreadsheetOpenTrigger or the myFunction? — Luke
According to an existing answer at stackoverflow.com/a/66320971/15384825, the error shows up because your script is a standalone one & there's no active spreadsheet. You can try the answer from this exiting post about "Install trigger for google app script in custom addon" at stackoverflow.com/a/61321007/15384825. — Irvin Jay G.
Apparently the code is incomplete. Please add a minimal reproducible example. Include details of your project like if it's a bounded or a standalone project, how the function to create the triggers is executed, ....). Also include a brief description of your search efforts as is suggested in How to Ask. P.S. Does the function to be called by the installable on open trigger is named "myFunction"? — Rubén

GoranK GoranK · Accepted Answer · 2021-08-09T20:09:24

Before you create a new trigger programmatically, you must ensure that previous are deleted. Maybe you have too many triggers created and this is why you get the error? Try with the snippet below.

const HANDLER_FUNCTION = 'myFunction'        
const existingTriggers = ScriptApp.getProjectTriggers().filter((t) => t.getEventType() === ScriptApp.EventType.ON_OPEN && t.getHandlerFunction() === HANDLER_FUNCTION)
existingTriggers.forEach((t) => ScriptApp.deleteTrigger(t))    ScriptApp.newTrigger(HANDLER_FUNCTION).forSpreadsheet(SpreadsheetApp.getActive()).onOpen().create()

Can't create a onOpen() trigger Google App Script

1 Answers