Spreadsheet onOpen menu from a Library

Question

I will distribute a spreadsheet with onOpen menu among several users. I would like to keep the menu definitions and functionality within a Library, so if I add some menu options or update some functions, everyone using that spreadsheet will get updated automatically.

So far, on the spreadsheet script I have only this simple code:

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

The menu loads on the spreadsheet without a problem, however no matter how I named the call on the menu and the actual function (with or without myLib.) I always get an error "Script function doSomething could not be found" when using an option from that menu.

Any idea on how should I name 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 being able to add more options to that spreadsheet menu from the library, without having to update every user's spreadsheet.

This is sample code all included in the spreadsheet script, no library been used yet and it works without problem

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");
}

What I want to do is get the same functionality, but splitting the code between the spreadsheet script and a library, as follow

On the spreadsheet script

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

On the 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");
}

On this splitting approach the menu built from the library call shows correctly on the spreadsheet, however when using an option from that Testing menu, I get a message error like: "Script function someFunction1 could not be found"

Answer 1

Last I checked you cannot give to a trigger functions that are not contained within the script. What you most likely need to do is to create a wrapper function in the script for your scpreadsheet. Something like this:

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

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

There is a feature request for this:

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

If you are interested in having this functionality, please go and vote on this issue to increase its priority.

Best,

Anton

Answer 2

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

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

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

Answer 3

Good news! This works in the new Google Sheets. It's Apr 4, 2014 and you still have to go to your Google Drive settings and check "Use the new Google Sheets." (Caution: Beta = Bugs). This will only work in the new Sheets, if you're working on an Old Sheet it won't work.

~~in your library~~

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

~~~

Answer 4

This is how I solved my works

// ----myLibrary----
function loadMenu() {
  var menuList = [
    { name: 'New', functionName: 'myLibrary.addData' },
    { name: 'Modify', functionName: 'myLibrary.modifyData' },
    { name: 'Delete', functionName: 'myLibrary.deleteData' },
  ];

  return menuList;
}

function modifyData() {
  Logger.log('called by modifyData');
}

function addData() {
  Logger.log('called by addData');
}

function deleteData() {
  Logger.log('called by deleteData');
}
// ----myLibrary----

// -----Client-------
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuList = myLibrary.loadMenu();
  ss.addMenu('Custom Menu', menuList);
}
// -----Client-------