1

Google Scripts lets you create custom menus. For example:

function makeMenu(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Scripts")
  .addItem("My Script","myScript")
  .addToUi();

This works great if you have only ONE file you want to script.

Let's say, however, you have 50 files that you want to all have the same menu running the same functions.

You can easily update the functions themselves by putting them in a library and having those 50 files reference the library version HEAD:

function myScript(){
  MyLibrary.myScript();
}

Now, if you want to update the myScript() function, you simply update it in your library, and the changes propogate.

However, I have two problems:

  1. The only way I can see to add a NEW menu function to all 50 files is to manually go into each file and add:
function myNewScript(){
  MyLibrary.myNewScript();
}

Is there any way to update them all at once?

NOTE: While you could "library out" the "makeMenu" function, and the individual scripts would then have the new menu item, they will error out when the menu function is used, as the menu function will only call the function if it exists within the file itself (as opposed to the library).

  1. The only way I can see to use versioning with the library (so users can have a stable version while the developer works on the HEAD version) is, again, to manually go through all 50 files and update the library version.

Is there any way to use versioning without having to go into each file and manually update the library version it uses?

I did find this close-but-not-quite answer about dynamically updating menus; however, as far as I can tell, it will only work within a single script, and does not apply to libraries (see my NOTE for #1 above): Update app script executable functions menu as new functions are added

TheMaster
  • 45,448
  • 6
  • 62
  • 85
codepants
  • 53
  • 1
  • 8
  • You can definitely do it with Apps Script API. I find it pretty easy to parse the JSON of the script and parse the parse the function which I save as an ascii file which contain the JSON of the function and combine the two and update the function set and then reload the entire JSON back into the project. I'm not going to share the script because I know that invariably it will lead to problems for some users and I don't wish to feel compelled to help them through their problems. Work it out for yourself. – Cooper Oct 28 '21 at 17:21
  • See if [this](https://stackoverflow.com/questions/64383424/google-sheets-add-on-set-anonymous-dynamic-functions-to-menu-using-google-app-s/64384141#64384141) answers your question – TheMaster Oct 28 '21 at 17:34

2 Answers2

2
  • Create menus from library itself
  • Call functions in library from the including script using libraryIdentifier.libraryFunction

Library file.gs:

function mkMenu(libraryIdentifier="MyLibrary"){
 SpreadsheetApp.getActive()
 .addMenu('ClickMe!',[{name:'alert!',functionName:`${libraryIdentifier}.alertLib`}])
}

function alertLib() {
  SpreadsheetApp.getUi().alert('Library function running!');
}

Including script.gs:

function onOpen(){
  //You need to pass a specific library identifier, if it is different from `MyLibrary`
  MyLibrary.mkMenu();
};
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Why use `[{name:'alert!',functionName:'${libraryIdentifier}.alertLib'}]` instead of the simpler (but hard-coded) `LibraryIdentifier.myNewScript` ? – codepants Oct 29 '21 at 18:59
  • @codepants Even if you don't pass anything, it'll evaluate to `MyLibrary.myNewScript` as declared in `function mkMenu(libraryIdentifier="MyLibrary"){`, but yes, it makes it modular, in case you want to change the library name in one of those scripts or you have a conflicting library name in one of those 50 files.. – TheMaster Oct 29 '21 at 19:47
  • Okay, thanks. Any thoughts about versioning? – codepants Oct 30 '21 at 22:37
  • @codepants As written in my answer, only the api can help – TheMaster Oct 31 '21 at 09:00
0

I actually found a simpler way to do this based on TheMaster's answer (thank you!).

I had not thought to simply pass the library reference to Menu.addItem as part of the function name.

So within the library you have something like:

function makeMenu(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Scripts")
  .addItem("My New Script","Library.myNewScript")
  .addToUi();
}
function myNewScript(){
  SpreadsheetApp.getUi().alert("Hello world!");
}

And within the file that references the library all you need is:

function onOpen(){
  Library.makeMenu();
}

This removes the need to list function myNewScript within the file's script.

Maybe there is something about TheMaster's answer that makes it more modular that I am not understanding, but based on my current understanding, what I have just described is the simplest way to accomplish what I want.

Thanks again!

codepants
  • 53
  • 1
  • 8