CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Nautisful
Nautisful

Reputation: 1

Google script embedded in google sheet adding a menu item

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)

Upvotes: 0

Views: 1678

Answers (3)

Nautisful
Nautisful

Reputation: 1

The suggested answer from Cameron Roberts works as a workaround.

Although in my case the problem was that the script trigger for onOpen was missing. I had to edit the script trigger within the script I wrote. In the script editor go to "Edit" -> "Current projects triggers" and add a trigger for the onOpen function with an event "on open". Apparently that was missing in my case, after that edit, it worked like a charm.

Upvotes: 0

Cameron Roberts
Cameron Roberts

Reputation: 7367

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.

Upvotes: 1

Wicket
Wicket

Reputation: 38190

onOpen is a reserved word for a function to be called automatically when a Google Sheets spreadsheet is opened by the spreadsheet owner or editors, it will not run for viewers.

You should check that in the project there isn't any other function named onOpen otherwise another function declaration could be executed instead of the one that you expect.

Reference

Upvotes: 0

Related Questions