Spreadsheet on an open menu from the library

Question

I will distribute the spreadsheet with the onOpen menu among several users. I would like to keep the definitions and functionality of the menu in the library, so if I add some menu options or update some functions, everyone using this spreadsheet will automatically update.

So far in the spreadsheet script, I only had this simple code:

function onOpen() { myLib.loadMenu(); } 

The menu is loaded into the spreadsheet without problems, however, no matter what I called the menu call and the actual function (with or without myLib.), I always get the error message “Script function doSomething could not be found” when using the option from this menu .

Any idea on what I should call the function call in the menu and the actual function in the library for this approach to work.

Thanks Fausto

EDIT-1 . Let me give more details and sample code.

My goal is to add additional options to this menu from the library without updating each user table.

This is the sample code included in the script table, the library has not yet been used, and it works without problems.

 function onOpen() { testMenu(); } function testMenu() { SpreadsheetApp.getActiveSpreadsheet().addMenu( 'Testing', [ { name: 'Do Something #1', functionName: 'someFunction1' }, null, { name: 'Do Something #2', functionName: 'someFunction2' } ]); } function someFunction1() { SpreadsheetApp.getActiveSheet().getRange("A1").setValue("Hello from someFunction1"); } function someFunction2() { SpreadsheetApp.getActiveSheet().getRange("A2").setValue("Bye from someFunction2"); } 

I want to do the same, but split the code between the script table and the library, as follows

In a spreadsheet script

 function onOpen() { xsLib.testMenu(); } 

In library

 function testMenu() { SpreadsheetApp.getActiveSpreadsheet().addMenu( 'Testing', [ { name: 'Do Something #1', functionName: 'someFunction1' }, null, { name: 'Do Something #2', functionName: 'someFunction2' } ]); } function someFunction1() { SpreadsheetApp.getActiveSheet().getRange("A1").setValue("Hello from someFunction1"); } function someFunction2() { SpreadsheetApp.getActiveSheet().getRange("A2").setValue("Bye from someFunction2"); } 

In this separation approach, the menu created from the library call is displayed correctly in the spreadsheet, however when using the option from this testing menu I get an error message, for example: "Script function someFunction1 not found"

Answer 1

The last thing I checked cannot give trigger functions that are not contained in the script. Most likely you need to create a wrapper function in a script for your scpreadsheet. Something like that:

 function doSomething() { myLib.doSomething(); } 

EDIT: The functionality you are looking for is currently unavailable. To achieve the desired effect, you will need to indicate that the function you want to call belongs to the library, and this is not allowed.

There is a function request for this:

http://code.google.com/p/google-apps-script-issues/issues/detail?id=799

If you are interested in using this feature, vote on this issue to increase its priority.

Best

Anton

Answer 2

error like: "Script function someFunction1 not found" tring to call someFunction1 in the "local" code, not lib

cm; https://code.google.com/p/google-apps-script-issues/issues/detail?id=1346

you can get around this by adding a stub function to the "local" code.

Answer 3

Good news! This works in the new Google Sheets. This is April 4th, 2014, and you still need to go to your Google Drive settings and select the "Use new Google Sheets" checkbox. (Caution: Beta = Errors). This will only work on new sheets, if you are working on an old sheet, it will not work.

~~ in your library ~~

 var menu = SpreadsheetApp.getUi().createMenu('Magical Menu'); menu.addItem('Do The Thing', 'LibraryName.function_name'); menu.addToUi(); 

~~~