Is it possible to pass parameters to a function through a custom menu in Google Sheets?

Other Questions Google Sheets
1

I’m working on a Google Sheets project where I need to create a custom menu. The menu should list employees and when clicked, display their email address. Here’s what I’ve tried:

function setupMenu() {
  let ui = SpreadsheetApp.getUi();
  let customMenu = ui.createMenu('Staff Directory');
  let employeeSubmenu = customMenu.addSubMenu(ui.createMenu('Select Employee')
    .addItem('John Smith', showEmail('[email protected]')));
  customMenu.addToUi();
}

function showEmail(address) {
  SpreadsheetApp.getUi().alert('Employee email: ' + address);
}

When the spreadsheet loads, it works fine and shows the email. But when I click the menu item, I get an error saying the function name should be a string.

Why does it work on load but not when I click the menu item? How can I make this work as intended?

2

The problem lies in how you’re passing the function to the menu item. When defining menu items, Google Sheets expects a string representing the function name, not the actual function call.

Here’s a more effective approach:

function setupMenu() {
  let ui = SpreadsheetApp.getUi();
  let customMenu = ui.createMenu('Staff Directory');
  customMenu.addSubMenu(ui.createMenu('Select Employee')
    .addItem('John Smith', 'showEmailJohn'))
    .addToUi();
}

function showEmailJohn() {
  SpreadsheetApp.getUi().alert('Employee email: [email protected]');
}

This method creates a separate function for each employee, allowing you to hardcode the email address. For multiple employees, you’d create additional functions such as showEmailJane, showEmailMike, etc. It’s not the most scalable solution, but it works well for a small number of employees.

3

hey alice, i think the issue is with how ur passing the function. try this instead:

.addItem('John Smith', 'showEmail')

then in showEmail():

function showEmail() {
  SpreadsheetApp.getUi().alert('Employee email: [email protected]');
}

this should work better. lemme know if u need more help!

4

I’ve dealt with a similar issue before, and I found a workaround that might help you. Instead of trying to pass parameters directly, you can use a global variable to store the email address temporarily. Here’s how you could modify your code:

let currentEmail = '';

function setupMenu() {
  let ui = SpreadsheetApp.getUi();
  let customMenu = ui.createMenu('Staff Directory');
  customMenu.addSubMenu(ui.createMenu('Select Employee')
    .addItem('John Smith', 'showJohnEmail')
    .addItem('Jane Doe', 'showJaneEmail'))
    .addToUi();
}

function showJohnEmail() {
  currentEmail = '[email protected]';
  showEmail();
}

function showJaneEmail() {
  currentEmail = '[email protected]';
  showEmail();
}

function showEmail() {
  SpreadsheetApp.getUi().alert('Employee email: ' + currentEmail);
}

This approach allows you to keep your menu items simple while still displaying different emails for each employee. It’s more scalable than creating separate functions for each employee, especially if you have a lot of staff members.