0

I'm doing everything as prescribed in the 'Defining Spreadsheet Menus' tutorial. The menu is modified using the onOpen event handler.

I have made this 'Text to Columns' script available publicly in the 'Script Gallery' but I'm concerned that users who download may be confused when it takes a long time for the custom menu to pop up.

Getting the script to load the first time is proving to be a pain. Much of the time the onOpen trigger is missed altogether. It appears that the trigger isn't being set correctly because manually resetting the onOpen trigger will fix it.

For personal use I'd consider this a minor annoyance but for a shared script it becomes a support issue.

Note: Every subsequent load consistently takes about 7 seconds to appear which is OK but far from ideal.

Here's the onOpen handler:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({ name:"Text to columns", functionName:"textToColumns" });
  menuEntries.push({ name:"Text to columns (custom separator)", functionName:"textToColumnsCustom" });
  menuEntries.push(null);
  menuEntries.push({ name:"Columns to Text", functionName:"columnsToText" });
  menuEntries.push({ name:"Columns to Text (custom separator)", functionName:"columnsToTextCustom" });
  ss.addMenu("Advanced", menuEntries);
}

Note: This was tested on a new (ie empty) spreadsheet with only one user.

Evan Plaice
  • 13,944
  • 6
  • 76
  • 94
  • How about creating the menu with few-or-no items in the onOpen, then using the [new relative-time triggers](https://developers.google.com/apps-script/class_clocktriggerbuilder#after) to have another function update the menu with additional items? – Mogsdad Feb 01 '13 at 19:44
  • Interesting, it's worth a try. – Evan Plaice Feb 01 '13 at 20:29
  • @Mogsdad I did some reading and that may complicate the issue even more since there is a quota for the number of times a time trigger can be called in a day and this is a publicly shared script. It looks like the issue initial loading problem is caused by the script failing to trigger onOpen. It can be kludged to work by manually adding an onOpen trigger to the script. As for subsequent loading time, 7 seconds appears to be the consistent norm. – Evan Plaice Feb 01 '13 at 21:14
  • Another option is to abandon the menu, and instead just put clickable buttons in the sheet. See [How do you add UI inside cells in a google spreadsheet using app script?](http://stackoverflow.com/questions/6876819/how-do-you-add-ui-inside-cells-in-a-google-spreadsheet-using-app-script/6966272). That may not fit your particular application, but in general it would appear faster. – Mogsdad Feb 01 '13 at 22:04
  • Providing this feature as a menu item is kinda the point. The script is implemented as a polyfill for the 'Text to Columns' feature that many users migrating from Excel may depend on. Either way, I appreciate the suggestions. – Evan Plaice Feb 01 '13 at 22:22
  • I wonder about the trigger quotas... if onEdit runs as the USER, and it installs another trigger, wouldn't that trigger also count against the USER, not the author? The documenation is thin, and the quota chart is [currently broken](http://code.google.com/p/google-apps-script-issues/issues/detail?id=2388), so it's hard to take guidance from. I've been annoyed by slow menu loads myself, so I'm thinking out loud -- apologies if it's not helping solve your immediate problem. – Mogsdad Feb 01 '13 at 22:36

1 Answers1

0

i'm not sure but the user have to log in i guess, if not, i've got this problem once and solved it with making tow or more triggers all of them are on open and the same function,however try to get internet speed more than 30KB/s to make sure that the problem is from the code , not from your devices

Majed DH
  • 1,251
  • 18
  • 35