Create dynamic custom menus with Apps Script

Remember the petition you and I wrote to the management team at our company regarding their recent decision to discontinue the work-from-home policy and have everyone back at the office? Well, I have bad news and good news: The bad news is that management would not relent: all employees must return to the office full-time. But the good news is that management has decided to institute a new performance-based bonus program!

Now, our payroll manager needs our help in automating parts of the bonus program. She has a Google sheet where all the employees are listed with their names, email addresses, and other data. The manager is asking us to create a custom menu in the spreadsheet that will list all the employees alphabetically. She wants to be able to choose an employee in the menu and have our automation kick-off a bonus payment for that employee.

The custom menu must list all the employees that are in the spreadsheet, when the spreadsheet is opened. The custom menu options must all trigger the same bonus payment function for the chosen employee.

You and I think that what she wants makes sense, but there's a catch: Apps Script doesn't let us pass arguments to the function that we name in the custom menu. So how can we possibly help our payroll manager? Well, a bit of Javascript magic will take care of that.

To get started, create a new Google Sheet with a tab named 'Employees'. In the tab, list a few employees – one per row. The column order is: First name, Last name, Job title, Email address, and Hire date.

To orient ourselves, let's create a generic onOpen function to add a static custom menu to our sheet:

function onOpen(e) {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('🟢 Automation');
  menu.addItem('Example option', 'exampleFunction').addSeparator();
  menu.addToUi();
}

Above, we get the UI, create a menu, and add an item with a separator, and add the menu to the UI. We won't actually work with this menu option – we enter it merely to demonstrate our problem: We can see that "exampleFunction" is specified as a string: there's no way to pass an argument like an employee's information to it.

Let's break this problem into parts. The first thing we need to do is to extract the employees' information from the sheet. Well, that's easy enough:

function getEmployees() {
 const ss = SpreadsheetApp.getActive();
 const sh = ss.getSheetByName('Employees');
 let employees = sh.getDataRange().getValues();
 employees.splice(0, 1);
 employees.sort();
 employees = employees.map((row) => [`${row[0]} ${row[1]}`, row[3]]);
 return employees;
}

Above, we get the spreadsheet and the Employees tab. We pull out the data and splice out the header row, since we don't need it. We sort the list alphabetically for the menu. Then we merge the first name and the last name of each employee and get their email address by using a map to get the data in the format that we need. Finally, we return the modified data array.

The solution to our pesky menu problem is to create menu functions dynamically. Each function, when invoked, will call our bonus-payment function, passing to it the employee's information. We'll build these functions dynamically by iterating over our data array, creating a menu option for each employee, and associating the menu option with the newly created function.

First, we'll create a global object that will hold the functions, since we don't want them to pollute the global scope. Then we'll create the functions and attach them to our object:

const g = {};

function createFunctions() {
 const employees = getEmployees();
 employees.forEach((employee) => {
   const funcName = employee[1].replace(/[@\.]/g, '_');
   g[funcName] = function () {
     startBonus(employee);
   };
 });
}

createFunctions();

Inside createFunctions, we get the employees data. Since email addresses are unique, we use them as the basis of our functions. But we need to remove the @ sign and the periods in the address to have valid function names. To do so, we use a regular expression to look for an at sign or a period globally and replace them with underscores. We attach the function name to the global object, and have it point to an anonymous function that is going to call our upcoming bonus payment function. We pass both columns of the employee information to the bonus function.

Finally, we invoke createFunctions. It's super-duper important that the function executes in the global scope. It basically needs to run every time the script does something: whether when the spreadsheet opens or when the menu item is selected, this function needs to run in order for Google Apps Script to have the functions that were created in memory. Otherwise, you will get a "Function not found" error message.

Now we can go back to our onOpen function and have it dynamically populate the custom menu with our employees:

function onOpen(e) {
 const ui = SpreadsheetApp.getUi();
 const menu = ui.createMenu('🟢 Automation');
 menu.addItem('Example option', 'abc').addSeparator();
 const subMenu = ui.createMenu('Bonus program');
 const employees = getEmployees();
 employees.forEach(([option, email]) => {
   subMenu.addItem(
     `Pay bonus to ${option}`,
     `g.${email.replace(/[@\.]/g, '_')}`
   );
 });
 menu.addSubMenu(subMenu).addToUi();
}

Above, we create a Sub menu with a "Bonus program" option. We get the employee information and iterate over it. We pull out the name as "option" and email. We add an item to the sub menu using the option, and the email-turned-function pattern that we saw before. Note that it's super important to reference the "g" object in the function name so that Apps Script can find it. We add the sub-menu to the menu, and add the menu to the UI.

Let's create our bonus payment function:

function startBonus([name, email]) {
 const ui = SpreadsheetApp.getUi();
 const result = ui.alert(
   'Please confirm',
   `Are you sure you want to give bonus to ${name} (${email})`,
   ui.ButtonSet.YES_NO
 );
 if (result == ui.Button.YES) {
   // Do the actual bonus payment for the given employee...
 } else {
   ui.alert('Bonus payment canceled');
 }
}

In the function above, we unpack the employee argument. We get the UI. We display a confirmation dialog that includes the employee information. If the payroll manager clicks YES then whatever code is needed to dole out the bonus will execute.

Reload the spreadsheet and select the custom menu options a few times. Then head to the Executions tab in the Apps Script IDE. You'll notice that the functions that we created dynamically are showing up there, including the "g." and the underscores.

Now this is just an example of how to achieve custom menu options. You could speed things up and make this script more efficient by caching the details of the employees so that the script is not reading from the Google Sheets every time it runs. There are other things that you could do, but at least this gives you an idea of how to build the custom menu dynamically, and how to call the same function with different arguments, so that you can execute the function for different menu items.