Apps Script Course: Menus

You have the option to create custom menus for your Google Sheets document.

Then, when you click on one of the menu options, the corresponding function will be executed.

Creating a Menu

The function that creates and displays the menu only needs to be executed once when the document is opened, so you can place it in the onOpen function to use this trigger.

In this example, the goal is to create a menu that will group text functions (here to convert cells to uppercase or lowercase):

function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('Text')
    .addItem('Convert to uppercase', 'uppercase')
    .addItem('Convert to lowercase', 'lowercase')
    .addToUi();
}

In createMenu, enter the name of the menu to display:

.createMenu('Text')

And add one or more choices to the menu with addItem (the first value is the name of the choice and the second is the name of the function to execute):

.addItem('Convert to uppercase', 'uppercase')

Then execute the onOpen function to display the menu:

You can see that the menu has been successfully added.

It remains to add the functions uppercase and lowercase which will aim to convert the selected cell to uppercase or lowercase:

function uppercase() {

  const activeCell = SpreadsheetApp.getActiveSheet().getActiveCell();

  activeCell.setValue(activeCell.getValue().toUpperCase());
}

function lowercase() {

  const activeCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  
  activeCell.setValue(activeCell.getValue().toLowerCase());
}

After saving the changes, you will be able to use the added menu to convert the value of the active cell to uppercase or lowercase.

To go a little further and make these functions more practical, it is possible to apply these modifications to an entire range by retrieving the values of the active range and using the map method on each line and then on each value of the line:

function uppercase() {

  const activeRange = SpreadsheetApp.getActiveSheet().getActiveRange();

  activeRange.setValues(activeRange.getValues().map(line => line.map(value => value.toUpperCase())));
}

function lowercase() {

  const activeRange = SpreadsheetApp.getActiveSheet().getActiveRange();

  activeRange.setValues(activeRange.getValues().map(line => line.map(value => value.toLowerCase())));
}

If you are not comfortable with array methods, you can also do it with for loops. Here's an equivalent example for the uppercase function:

function uppercase() {

  const activeRange = SpreadsheetApp.getActiveSheet().getActiveRange();
  const activeRangeValues = activeRange.getValues();
  const numRows = activeRangeValues.length;
  const numCols = activeRangeValues[0].length;

  for (let row = 0; row < numRows; row++) {
    for (let col = 0; col < numCols; col++) {
      activeRangeValues[row][col] = activeRangeValues[row][col].toUpperCase();
    }
  }

  activeRange.setValues(activeRangeValues);
}

Adding a Submenu

You can also add submenus by using addSubMenu and then the menu as an argument.

Here's an example:

function onOpen() {

  const ui = SpreadsheetApp.getUi();
  
  ui.createMenu('Text')
    .addItem('Other', 'other')
    .addSubMenu(ui.createMenu('Uppercase / Lowercase')
      .addItem('Convert to uppercase', 'uppercase')
      .addItem('Convert to lowercase', 'lowercase'))
    .addItem('Various', 'various')
    .addToUi();
}

This results in:

Adding a Separator

You can also add separators to, for example, separate groups of functions with addSeparator:

function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('Text')
    .addItem('Convert to uppercase', 'uppercase')
    .addItem('Convert to lowercase', 'lowercase')
    .addSeparator()
    .addItem('Split into array', 'split')
    .addItem('Join an array', 'join')
    .addToUi();
}

The menu:

Adding an Icon

If you wish, you can add a special character such as an emoji directly into the text:

function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('Text')
    .addItem('🔺 Convert to uppercase', 'uppercase')
    .addItem('🔻 Convert to lowercase', 'lowercase')
    .addToUi();
}

The menu: