0

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)

Improve this question
5
  • Is the script bound to spreadsheet? In this case menu should work fine. You might need to reload spreadsheet page for onOpen to trigger. Commented Jun 19, 2020 at 18:42
  • 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 Commented Jun 19, 2020 at 18:46
  • Is this a Google Sheets spreadsheet or an Excel file? Commented Jun 19, 2020 at 18:47
  • google sheet or table, however they call it Commented Jun 19, 2020 at 18:48
  • I hope that you know that the script editor doesn't save stuff automatically. You have to save it manually. Commented Jun 19, 2020 at 20:19

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

Improve this answer

2 Comments

onOpen is the only declaration in the script which is bound to the spreadsheet. This automatic call for onOpen should be performed without any changes to my spreadsheet nor script correct? To make this clear, creating a spreadsheet -> creating a script (Tools->scripteditor). And when I add an onOpen function, that function should be called whenever the spreadsheet is opened? There´s nothing more to it?
That's right. If you need further help, please edit the question to add more details like if you are able to reproduce the problem in a new spreadsheet and in such case include the steps you followed the name and version of the operative system and web browser that you are using, if you have signed in into a single account or into multiple account, etc.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.