Google script embedded in google sheet adding a menu item

Question

What I tried is adding some functionality to my google sheet (creating events and pushing them to the google calendar). Everything works, but when I close the script editor my menu disappears. I created the menu items like so:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sync to Calendar')
      .addItem('Create Events Now', 'CreateEvent')
      .addItem('Delete All Events in Calendar', 'DeleteAllEvents')
      .addToUi();
}

That works just fine, but I would like to close the script and also when I share the spreadsheet, the member shall be presented with the menu to click and execute the functions. Anyone has a solution how to make that permanent? (without publishing the script which would be an overkill in my opinion)

Is the script bound to spreadsheet? In this case menu should work fine. You might need to reload spreadsheet page for onOpen to trigger. — roma
Yes, in my understanding it would be bound as long as I open the script directly from the google sheet Tools->scripteditor. It´s there and when I rerun the onOpen() function the menu appears again. Seems like this is session bound. Can I set onOpen as a trigger when the spreadsheet is being opened? because this function is on the last lines of my code, which means, the spreadsheet can´t know which function to execute when opening the google sheet — Nautisful
I hope that you know that the script editor doesn't save stuff automatically. You have to save it manually. — Cooper

Cameron Roberts Cameron Roberts · Accepted Answer · 2020-06-19T20:26:48

In some cases OnOpen doesn't run automatically unless the editor has authorized the script. In this case, you need to give editors a way to trigger the script, so they may authorize it, before onOpen will run automatically. You can either give them instructions to do this manually through the script editor, or you can insert a "button" into the sheet.

To do this "button" - insert an drawing into the sheet (the "button"), bind the drawing to your script, and have first time users click the drawing.

In your spreadsheet, click "Insert" -> "Drawing"
Draw a "button" image with useful text for end users ("Show custom menu" or "No menu? Click here!", etc.)
Place the drawing on your spreadsheet in a visible/convenient location.
Click the "three dots" on the drawing and select "Assign Script"
Enter the name of the function (eg OnOpen)

If a user opens the sheet and doesn't see your menu, they can click this "button" to activate the script. They'll be prompted to authorise the script as needed, then the menu will show from that point forward. They should only need to click the button the very first time they open the sheet, unless the scopes change or they manually remove authorization in account settings.

Google script embedded in google sheet adding a menu item

3 Answers