Can a Google Sheets custom menu pass a variable to

Question

I'm creating a menu item in Google Sheets that will list a work directory and pull the email address for that user, to be later used to process employee records within Google Sheets. I'm trying to get it to work like this:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  var myapp = ui.createMenu('MyApp');
  var pullemp = myapp.addSubMenu(ui.createMenu('Pull Employee')
          .addItem('Nathaniel MacIver', menuItem2('[e-mail]')));
  myap.addToUi();
}

function menuItem2(email) {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
     .alert('That email address is '+email);
}

Now, when I open the spreadsheet, The item triggers immediately, and I get the result, below, which is what I want:

But I want it to trigger when I click the button in my menu. As it is, when I click on my menu button I get the error:

I know the link mentions that the function name needs to be a string value, but why would it work onload as I need it to and then fail when I press a button?

Answer 1

When you create a menu item with

.addItem('Nathaniel MacIver', menuItem2('[e-mail]'))

the function menuItem2 is called with the parameter '[e-mail]'. This results in the alert that you see. The return value of the function is undefined (as you don't return anything from it). So you end up with the same menu item as if it was

.addItem('Nathaniel MacIver', undefined)

which clearly isn't going to do anything.

The method addItem takes only a function name, it does not allow for passing parameters to that function. To do what you want, you'll need separate functions for each person, each with an email hardcoded inside that function.

Answer 2

Matthew Wolman's solution did not work in google sheets. Everything works at create time, typeof sees function. However not sure if it goes out of scope or there's another security policy in place, but when you invokve the menu with a dynamic function it gives error:

  Execution failed: Script function not found: 'dynfunc-item1'

Was able to get dynamic menus working in using globally scoped function defs.

Main difference between Matthew Wolman's answer and what worked is this bit:

/* generate all menuFunctions in immediatelly called closure */
(function (globalScope) {
for (var i = 0, l = items.length; i < l; i++)
    createMenuFunction(items[i])

// you need to generate menuFunction via another function,
// because that way you generate scope to keep param unchanged.
// If you'll generate menuFunction right inside for loop,
// all your menuFunctions will refer to same instance of var i,
// which will be undefined after this closure terminates.
function createMenuFunction(param) {
    // here we define new function inside global scope
    globalScope[MENU_ITEM_FUNCTION_PREFIX + param] = function() {
       alertNum(param);
    }
}
})(this) // here we passing global scope and call the closure

Answer 3

Although you can't pass functions that invoke with a variable directly through the .addItem() method, because it only accepts strings, you can take a couple of extra steps (extra steps relative to what you were trying to do) to dynamically create functions that are pre-set with dynamic variables. If you have a global array of all of your e-mail addresses, as such:

var emails = ["[e-mail]",
              "[e-mail]",
              "[e-mail]",
              "[e-mail]"]

You can create a custom function named exactly after that e-mail using the 'this' keyword.

The 'this' keyword is very important. 'this' is an object. This object (no pun intended) contains keys of which are each of the functions in your GAS project. For example, if you had three functions in your project named: function1, function2 and function3, the 'this' object would have three keys each named function1, function2 and function3 and each of those keys would be the function itself. So:

Loop through your array and create a function for each e-mail using the bracket notation. The bracket notation allows you to call object keys with strings that contain characters that the dot notation will not allow. I can't create an object by saying [e-mail] but I can create an object such as this["[e-mail]"]:

emails.forEach(function (email) {
   this[email] = function () {
    SpreadsheetApp.getUi().alert('That email address is '+email);
  }
});

This code will create a new function in your GAS project on the server side for each e-mail, with the function itself being named nothing more than the e-mail. For visual representation, you would in theory have in your project:

function [e-mail] () {
  SpreadsheetApp.getUi().alert('That email address is [e-mail]');
}

function [e-mail] () {
  SpreadsheetApp.getUi().alert('That email address is [e-mail]');
}

function [e-mail] () {
  SpreadsheetApp.getUi().alert('That email address is [e-mail]');
}

function [e-mail] () {
  SpreadsheetApp.getUi().alert('That email address is [e-mail]');
}

Although you won't actually see that, when a function is ran, this will be what is happening on the back-end when your code compiles. That being said, you can then dynamically pass those same e-mail names to the .addItem() method as a string (using a forEach loop on the same global "emails" array), which is the only type of variable you can pass into this method.

var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
var myapp = ui.createMenu('MyApp');
var pullemp = myapp.addSubMenu(ui.createMenu('Pull Employee'))

emails.forEach(function (email) {
 pullemp.addItem(email,email)
});

myapp.addToUi();

When any one of those items are clicked, the code will attempt to run a function called [e-mail] (or the 2nd, 3rd or 4th e-mail, whichever is clicked). Because you looped through the 'this' object and created four functions named after each e-mail, your project will successfully find that function by its name and run it.

---

EDIT: I realize that you originally wanted to create the menu with the persons's name rather than their e-mail. The menu above would show their e-mail in the menu itself. So, to create a menu with their names and respective e-mails as the functions, you would need an object. My recommendation would be to create an object that has the person's name as a key and their email being assigned to that key:

var emails = {};
emails["Nathaniel MacIver"] = "[e-mail]"
emails["John Doe"] = "[e-mail]"
emails["Jane Smith"] = "[e-mail]"

Now you can loop through this object by using the Object.keys(\\object name) method as such. Note that the Object.keys() method returns an array of the keys of the object. That is why the below forEach() method works:

Object.keys(emails).forEach(function (name) {
  this[emails[name]] = function () {
  SpreadsheetApp.getUi().alert('That email address is: '+emails[name])
 }
});

The reason why it is emails[name] for the e-mail is because 'name' itself is the actual name of the key, whereas emails[name] is the value stored in that key. So 'name' = "Nathaniel MacIver" and emails[name] = emails["Nathaniel MacIver"], which as you can see above, is assigned to his e-mail address.

Now you apply the same concept as mentioned above for creating the menu, but you loop through the object rather than just an array and assign menu items in the addItem() method as such:

var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
var myapp = ui.createMenu('MyApp');
var pullemp = myapp.addSubMenu(ui.createMenu('Pull Employee'))

Object.keys(emails).forEach(function (name) {
 pullemp.addItem(name,emails[name])
});

myapp.addToUi();

This will produce the exact same result as the first code sample, but provide the persons's name as the menu item rather than their e-mail address.

Please forgive me for the very long answer! I just found this out for solving my own Google Sheets Custom Menu issue. I hope this helps! Please feel free to reach out to me if you have any questions about this!