Creating custom menus in Google Sheets - Practice Exercises

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output when the custom menu is added?

You add this script to your Google Sheets project:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('My Menu')
    .addItem('Say Hello', 'sayHello')
    .addToUi();
}

function sayHello() {
  SpreadsheetApp.getUi().alert('Hello!');
}

What happens when you open the spreadsheet?

Google Sheets

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('My Menu')
    .addItem('Say Hello', 'sayHello')
    .addToUi();
}

function sayHello() {
  SpreadsheetApp.getUi().alert('Hello!');
}

A A new menu named 'My Menu' appears with an item 'Say Hello'. Clicking it shows an alert 'Hello!'.

B The spreadsheet shows an error because 'onOpen' is not a valid function name.

C Nothing happens because custom menus require manual activation.

D The menu 'My Menu' appears but clicking 'Say Hello' does nothing.

💡 Hint

Think about what the onOpen function does in Google Sheets scripts.

The onOpen function runs automatically when the spreadsheet opens. It adds a custom menu called 'My Menu' with an item 'Say Hello'. Clicking that item runs sayHello, which shows an alert.

❓ Function Choice

intermediate

Which function correctly adds a submenu to a custom menu?

You want to add a submenu called 'Options' with two items under your custom menu. Which function code correctly does this?

A

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Main Menu')
    .addItem('Options', 'funcOptions')
    .addToUi();
}

B

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Main Menu')
    .addSubMenu('Options', ['Option 1', 'Option 2'])
    .addToUi();
}

C

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Main Menu')
    .addSubMenu('Options')
    .addToUi();
}

D

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Main Menu')
    .addSubMenu(SpreadsheetApp.getUi().createMenu('Options')
      .addItem('Option 1', 'func1')
      .addItem('Option 2', 'func2'))
    .addToUi();
}

💡 Hint

Remember that addSubMenu requires a Menu object, not just a string.

Option D correctly creates a submenu by calling createMenu('Options') and adding items to it, then passing it to addSubMenu. Other options misuse the method or pass wrong arguments.

🎯 Scenario

advanced

You want a custom menu item to run a function that changes cell A1 to 'Done'. Which code snippet achieves this?

Choose the correct function that will be triggered by the custom menu item to set cell A1 to the text 'Done'.

A

function markDone() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1').setValue('Done');
}

B

function markDone() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  sheet.getCell(1, 1).setValue('Done');
}

C

function markDone() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1).setText('Done');
}

D

function markDone() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1').setText('Done');
}

💡 Hint

Check the correct method to set a cell's value and how to get the active sheet.

Option A uses getActiveSheet() and getRange('A1').setValue('Done'), which is the correct way. Option A uses a non-existent method getCell. Options C and D use setText, which is not a valid method.

📊 Formula Result

advanced

What error occurs if you call a non-existent function from a custom menu?

You add this menu item:

.addItem('Run Task', 'nonExistentFunction')

What happens when you click 'Run Task'?

A Nothing happens, the menu item is ignored silently.

B A popup error saying 'Exception: The function nonExistentFunction was not found'.

C The spreadsheet crashes and reloads automatically.

D The menu item runs but does nothing.

💡 Hint

Think about what happens if the script tries to run a function that does not exist.

Google Sheets shows an error popup indicating the function was not found. It does not silently ignore or crash the spreadsheet.

❓ data_analysis

expert

How many custom menu items are added by this script?

Consider this script:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('Tools');
  menu.addItem('Item 1', 'func1');
  menu.addItem('Item 2', 'func2');
  var subMenu = ui.createMenu('More');
  subMenu.addItem('SubItem 1', 'func3');
  subMenu.addItem('SubItem 2', 'func4');
  menu.addSubMenu(subMenu);
  menu.addToUi();
}

How many clickable menu items appear under the 'Tools' menu when the spreadsheet opens?

Google Sheets

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('Tools');
  menu.addItem('Item 1', 'func1');
  menu.addItem('Item 2', 'func2');
  var subMenu = ui.createMenu('More');
  subMenu.addItem('SubItem 1', 'func3');
  subMenu.addItem('SubItem 2', 'func4');
  menu.addSubMenu(subMenu);
  menu.addToUi();
}

A 2

B 5

C 4

D 3

💡 Hint

Count both top-level items and submenu items visible after clicking the submenu.

The 'Tools' menu has 2 direct items ('Item 1' and 'Item 2') plus one submenu 'More' which contains 2 items. The submenu counts as 1 clickable item at top level, but inside it has 2 more items. So total clickable items are 2 (top-level) + 1 (submenu) + 2 (submenu items) = 5. But the question asks for items under 'Tools' menu, which includes submenu as one item plus its 2 items inside. So total clickable items visible after opening 'Tools' and expanding 'More' is 4: 'Item 1', 'Item 2', 'SubItem 1', 'SubItem 2'. The submenu itself is a container, not a clickable item that triggers a function. So the answer is 4.