function onOpen() is not running

19
votes

My function includes adding a menu and toast to the document. I have verified that the trigger (onOpen) is set as well. It only works when a user goes into Tools, Script Manager, Run. We have too many users with too many backgrounds to expect then to know how to do this. Why isn't it working? (Using Chrome)

function onOpen()
{
  var menus = [{name: "Advance in Workflow", functionName:"sendEmail"}];

     SpreadsheetApp.getActiveSpreadsheet().addMenu("Auto Advance FG Workflow", menus);

  //sheet.toast(Notify/Remind users);
   sheet.toast("While you are here we kindly ask that you do not add, modify or remove     any columns.","Welcome - " + username,8);
 }

Thanks,

5
google-apps-scriptgoogle-sheetstriggers
Is this your actual code? Where does the 'username' come from? – Serge insas
This is the actual code. The script has defined var's throughout. Prior to the function onOpen, user name is defined as follows: var username = Session.getActiveUser().getUsername(); – user1817058
No. The onOpen function is still not running on open. Users are forced to go into Tools>Script Manager> select it, and click Run in order to get the menu and toast. – user1817058
Have you tried to add an installable trigger on the same onOpen() function... usually it does solve the issue. – Serge insas
Serge, the onInstall function is acting the same as the onOpen. With the trigger set to run the script when the document opens, it still will not show the menu item unless you run it through Tools>Script Manager. – user1817058

5 Answers

37
votes

I was having the same issue.

I realized, sometimes Google create some kind of cache of the scripts (I'm used to have a "test" script and I usually alter it's content, and, sometimes, the script runs as if I didn't).

So, what I did that solved the onOpen() not working was changing the function name and ading a trigger manually.

Go to "Resources -> Current script's triggers…"

Go to "Resources -> Current script's triggers…"

Choose the function to run on open

Choose the function to run on open

It worked like a charm here!

Updated Location Information: from tool bar or from menu bar

Then

trigger select

13
votes

This is an old post but I just had this problem and find out why it was not working correctly in my case: I had, at the top of my script file a variable that required some authorisations and that prevented the script to correctly run. I saw that OP called var username = Session.getActiveUser().getUsername(); (that requires authorisations, and it's may be the cause).

eg: this code won't work:

function onOpen(){
  SpreadsheetApp.getUi()
  .createMenu("Exportation")
  .addItem("Lancer l'exportation", "exportationMenu")
  .addToUi();
}
var stConsCons= SpreadsheetApp.openById(sgcid).getSheetByName("Consultant");

but this one will work:

function onOpen(){
  SpreadsheetApp.getUi()
  .createMenu("Exportation")
  .addItem("Lancer l'exportation", "exportationMenu")
  .addToUi();
}

function whatever(){
  var stConsCons= SpreadsheetApp.openById(sgcid).getSheetByName("Consultant");
...}
2
votes

In my case there was a reference error, that while did not stop the script entirely, it did stop the menu from appearing.

I was only able to detect that error after I run a debug on the script.

Google script debug option

2
votes

In my case, onOpen wasn't working because I had a variable, outside of a function, opening a sheet with SpreadsheetApp.openById() rather than SpreadsheetApp.getActiveSpreadsheet(). I guess onOpen doesn't work with openById() even if the sheet you are opening is bound to the script. onOpen() won't work with this kind of a variable outside of a function:

var sheet = SpreadsheetApp.openById("1b_PQD...").getSheetByName("demos")

If your script is bound to the sheet, you can solve this problem by using the getActiveSpreadsheet() function. Otherwise, you can solve it by putting your openById() call into a function.

0
votes

It looks like the problem may be that "sheet" isn't defined, which is why the toast is failing.