Creating custom menus in Google Sheets - Dashboard Building Guide

Dashboard Mode - Creating custom menus

Goal

Create a custom menu in Google Sheets to run a script that highlights all cells with values above a threshold. This helps quickly spot important data.

Sample Data

Item	Sales
Apples	120
Bananas	80
Cherries	150
Dates	60
Elderberries	200

Dashboard Components

• Custom Menu: Adds a menu named "Highlight" with an item "Highlight High Sales" that runs a script.
• Script Function: Checks sales values in column B and highlights cells with values > 100 in yellow.
• Formula Example: No formula needed here, but script uses getRange and setBackground methods.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Highlight')
    .addItem('Highlight High Sales', 'highlightHighSales')
    .addToUi();
}

function highlightHighSales() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange('B2:B6');
  const values = range.getValues();
  for (let i = 0; i < values.length; i++) {
    if (values[i][0] > 100) {
      range.getCell(i + 1, 1).setBackground('yellow');
    } else {
      range.getCell(i + 1, 1).setBackground(null);
    }
  }
}

Dashboard Layout

+---------------------------+
|        Google Sheet       |
| +-----------------------+ |
| | Item   | Sales        | |
| |--------|--------------| |
| | Apples | 120          | |
| | Bananas| 80           | |
| | Cherries| 150         | |
| | Dates  | 60           | |
| | Elderb.| 200          | |
| +-----------------------+ |
|                           |
| Custom Menu: "Highlight"  |
|   - Highlight High Sales  |
+---------------------------+

Interactivity

When the sheet opens, the onOpen function adds the custom menu. Clicking Highlight High Sales runs the script that colors sales above 100 in yellow. This updates the sheet visually to show important data.

Self Check

If you change the sales value for "Bananas" to 130 and run Highlight High Sales again, which cells will be highlighted?

• Answer: Cells for Apples, Bananas, Cherries, and Elderberries will be highlighted because their sales are above 100.

Key Result

Custom menu in Google Sheets to highlight sales above 100 with a script.