How to Build Custom Menus in Google Sheets with Apps Script

Every team that works heavily in Google Sheets eventually develops a set of scripts they run again and again. Highlight overdue rows. Export a filtered report. Send a summary email. Clear last week's data and start fresh. Running these scripts currently means navigating to Extensions > Apps Script, opening the editor, selecting the function from the dropdown, and clicking Run. That process takes thirty seconds and requires whoever is running it to know the script editor exists.

Custom menus eliminate that friction entirely. With a few lines of Apps Script, you can add your own menu to the Google Sheets toolbar that appears alongside File, Edit, and View. Your team members click the menu, select an action, and the script runs -- no editor required, no technical knowledge needed beyond knowing where to look.

This guide walks through everything you need to build useful custom menus: triggering them automatically when the spreadsheet opens, connecting menu items to real functions, building submenus for complex toolsets, making menus that respond dynamically to the spreadsheet's state, and adding UI dialogs and prompts that collect input from users before a script runs.

What Custom Menus Are and Why They Matter

A custom menu in Google Sheets is a top-level menu item you create through Apps Script that appears in the spreadsheet's menu bar when the file is open. It behaves identically to the built-in menus: you click it, a dropdown appears, and clicking any item in that dropdown calls a JavaScript function in your script.

The practical value is access. You might have a brilliant script that saves your team two hours a week, but if it lives in the Apps Script editor and only you know how to get there, the team will not use it. A clearly labelled menu item called "Generate Weekly Report" or "Mark Selected Rows Complete" gives non-technical users a reliable, discoverable way to run automation that would otherwise go unused.

For Australian businesses where Google Workspace is already the productivity foundation, custom menus are one of the fastest ways to turn a spreadsheet from a passive data container into an active working tool. A client tracker that sends follow-up emails. A job schedule that formats and exports itself. A stock list that flags low inventory. All of these can be triggered by a staff member clicking a menu item, with no training beyond "click that button."

The onOpen() Trigger: Your Starting Point

The foundation of every custom menu is the onOpen() function. Apps Script automatically runs any function named onOpen each time the spreadsheet is opened in a browser. This is where you tell Google Sheets to add your menu to the toolbar.

Here is the simplest possible custom menu:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('My Tools')
    .addItem('Say Hello', 'sayHello')
    .addToUi();
}

function sayHello() {
  SpreadsheetApp.getUi().alert('Hello from the custom menu!');
}

Open the script editor (Extensions > Apps Script), paste this code, and save it. Close the spreadsheet and reopen it. A new "My Tools" menu will appear in the toolbar. Click it, then click "Say Hello", and the alert dialog appears.

Breaking down what happens:

• SpreadsheetApp.getUi() gets a reference to the spreadsheet's user interface
• .createMenu('My Tools') creates a new top-level menu with that label
• .addItem('Say Hello', 'sayHello') adds one menu item; the first argument is the visible label, the second is the exact name of the function to call when clicked
• .addToUi() attaches the finished menu to the toolbar

The function name in addItem must match exactly. If your function is called generateReport, the second argument must be the string 'generateReport'. A typo here results in a silent failure -- the menu item appears but nothing happens when clicked.

A note on authorisation: The first time onOpen() runs for a new script, Apps Script executes it with limited permissions under a "simple trigger" model. If your onOpen() function itself tries to do anything requiring authorisation (like sending emails or accessing external services), it will fail silently. The solution is to keep onOpen() limited to building the menu structure, and put all authorised operations inside the functions the menu items call.

Adding Multiple Menu Items and Separators

A single menu item is rarely enough. Most practical toolsets need several actions grouped together. You can chain multiple addItem() calls and use addSeparator() to create visual dividers between logical groups:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Team Tools')
    .addItem('Highlight Overdue Rows', 'highlightOverdueRows')
    .addItem('Clear Highlights', 'clearHighlights')
    .addSeparator()
    .addItem('Send Weekly Summary Email', 'sendWeeklySummary')
    .addItem('Export to PDF', 'exportAsPdf')
    .addSeparator()
    .addItem('Reset All Data', 'resetData')
    .addToUi();
}

This creates a menu with five items and two dividers. The separators help users visually group related actions -- highlighting operations together, reporting operations together, and a destructive "reset" action clearly separated at the bottom.

Each named function must exist somewhere in your script project. It does not need to be in the same .gs file; Apps Script compiles all files in a project together, so you can keep menu setup in Code.gs and your actual functions in separate files named Reporting.gs, Formatting.gs, and so on. Keeping files organised this way makes large projects much easier to maintain.

Building Submenus for Complex Toolsets

When your toolset grows, a flat list of menu items becomes unwieldy. Submenus let you nest a second layer of items under a parent label, keeping the top-level menu clean while still providing access to many functions.

Use .addSubMenu() in place of .addItem() to attach a submenu. The argument to addSubMenu() is itself a menu object created with createMenu():

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  var reportingMenu = ui.createMenu('Reports')
    .addItem('Weekly Summary', 'generateWeeklySummary')
    .addItem('Monthly Overview', 'generateMonthlyOverview')
    .addItem('Custom Date Range...', 'generateCustomReport');

  var formattingMenu = ui.createMenu('Formatting')
    .addItem('Apply Status Colours', 'applyStatusColours')
    .addItem('Clear All Formatting', 'clearAllFormatting')
    .addItem('Highlight Overdue', 'highlightOverdue');

  ui.createMenu('Project Manager')
    .addSubMenu(reportingMenu)
    .addSubMenu(formattingMenu)
    .addSeparator()
    .addItem('Settings...', 'openSettings')
    .addToUi();
}

This creates a "Project Manager" menu with two submenus ("Reports" and "Formatting"), a separator, and a "Settings" item directly in the parent menu. Hovering over either submenu label opens its nested items.

Submenus are particularly useful for tools built for specific roles -- a "Finance" submenu for accounts staff, an "HR" submenu for people managers -- within a spreadsheet shared across multiple teams. Everyone sees the same menu, but the structure makes it easy to find the actions relevant to your role.

Dynamic Menus: Changing Items Based on Spreadsheet State

Static menus are useful, but dynamic menus are powerful. You can conditionally include or exclude menu items based on the spreadsheet's current state -- who is logged in, what sheet is active, what values exist in the data.

Here is an example that shows different menu items depending on whether the active sheet is the "Master" sheet or another sheet:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var activeSheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

  var menu = ui.createMenu('Work Tools');

  // Always show these items
  menu.addItem('Refresh Data', 'refreshData');
  menu.addSeparator();

  // Show admin-only items only on the Master sheet
  if (activeSheetName === 'Master') {
    menu.addItem('Archive Old Records', 'archiveOldRecords')
        .addItem('Rebuild Summary Tables', 'rebuildSummaryTables')
        .addSeparator();
  }

  // Always show these items last
  menu.addItem('Help', 'showHelp');

  menu.addToUi();
}

You can also use Session.getActiveUser().getEmail() to personalise menus for specific users:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var userEmail = Session.getActiveUser().getEmail();
  var adminEmails = ['admin@yourbusiness.com.au', 'ops@yourbusiness.com.au'];

  var menu = ui.createMenu('Company Tools')
    .addItem('View My Tasks', 'viewMyTasks')
    .addItem('Log Time Entry', 'logTimeEntry');

  // Only show admin tools to specified users
  if (adminEmails.indexOf(userEmail) !== -1) {
    menu.addSeparator()
        .addItem('Export All Records', 'exportAllRecords')
        .addItem('Manage Team Access', 'manageTeamAccess');
  }

  menu.addToUi();
}

Note that Session.getActiveUser().getEmail() only returns the user's email when the script runs within a Google Workspace domain where the user is signed in. In personal Google accounts or when viewed by external users, it may return an empty string. Always handle the empty case gracefully.

Dynamic menus rebuild every time the spreadsheet is opened, so the menu always reflects the current state. If you need the menu to update without reopening the file -- for example, after completing a workflow step -- you can call your menu-building function programmatically to redraw the toolbar.

UI Dialogs and Prompts: Collecting Input Before Running

Some script actions need information before they can run. Which date range should the report cover? What status should be applied to selected rows? What email address should the summary be sent to? Rather than hardcoding these values in your script, you can collect them interactively using Apps Script's UI dialog methods.

Alert Dialogs

The simplest dialog is an alert -- a message box the user must dismiss before the script continues. You have already seen this in earlier examples. Alerts are useful for confirming that an action completed, or for displaying a message that requires acknowledgement:

function runWithConfirmation() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert(
    'Confirm Reset',
    'This will clear all data in the current sheet. Are you sure?',
    ui.ButtonSet.YES_NO
  );

  if (response === ui.Button.YES) {
    clearCurrentSheet();
    ui.alert('Sheet cleared successfully.');
  } else {
    ui.alert('Reset cancelled.');
  }
}

The ui.ButtonSet.YES_NO argument gives the dialog Yes and No buttons instead of the default OK. ui.Button.YES is the constant you compare the response against. ui.ButtonSet.OK_CANCEL and ui.ButtonSet.YES_NO_CANCEL are the other available button sets.

Prompt Dialogs

A prompt dialog adds a text input field where the user can type a value before clicking OK:

function sendEmailWithSubject() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt(
    'Email Subject',
    'Enter the subject line for the summary email:',
    ui.ButtonSet.OK_CANCEL
  );

  // Check which button was clicked
  if (result.getSelectedButton() === ui.Button.OK) {
    var subject = result.getResponseText();

    if (subject.trim() === '') {
      ui.alert('Subject cannot be empty. Email not sent.');
      return;
    }

    sendSummaryEmail(subject);
    ui.alert('Email sent with subject: ' + subject);
  }
}

result.getResponseText() returns whatever the user typed. Always validate this input before using it. Check for empty strings, check that numbers are actually numbers if that is what you expect, and handle the case where the user clicks Cancel (in which case getSelectedButton() returns ui.Button.CANCEL and getResponseText() returns an empty string).

HTML Service Dialogs

For more complex user interfaces -- multi-field forms, dropdown selects, checkboxes -- Apps Script's HTML Service lets you build a full HTML dialog rendered inside the spreadsheet:

function showSettingsDialog() {
  var html = HtmlService.createHtmlOutput(`
    <html>
      <body>
        <h3>Report Settings</h3>
        <label>Date range (days):</label><br>
        <input type="number" id="days" value="30" min="1" max="365"><br><br>
        <label>Send report to:</label><br>
        <input type="email" id="email" placeholder="name@company.com.au"><br><br>
        <button onclick="submitSettings()">Save & Run</button>
        <button onclick="google.script.host.close()">Cancel</button>
        <script>
          function submitSettings() {
            var days = document.getElementById('days').value;
            var email = document.getElementById('email').value;
            google.script.run
              .withSuccessHandler(function() { google.script.host.close(); })
              .runReportWithSettings(parseInt(days), email);
          }
        </script>
      </body>
    </html>
  `)
  .setWidth(350)
  .setHeight(220)
  .setTitle('Configure Report');

  SpreadsheetApp.getUi().showModalDialog(html, 'Configure Report');
}

function runReportWithSettings(days, email) {
  // Called by the HTML dialog when the user clicks Save & Run
  Logger.log('Generating ' + days + '-day report for: ' + email);
  // ... your report logic here
}

google.script.run is the bridge that lets client-side JavaScript in your HTML call server-side Apps Script functions. withSuccessHandler() specifies a function to run after the server function returns successfully. google.script.host.close() closes the dialog.

HTML dialogs are the right choice when a prompt cannot capture everything you need, or when you want to provide a more polished experience for a frequently used tool.

Putting It Together: A Complete Working Toolset

Here is a complete, practical custom menu implementation for a client tracking spreadsheet. It includes the menu structure, a confirmation dialog, a prompt, and all the underlying functions wired up:

// =============================================
// MENU SETUP
// =============================================

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  var statusMenu = ui.createMenu('Update Status')
    .addItem('Mark Selected as Active', 'markAsActive')
    .addItem('Mark Selected as Inactive', 'markAsInactive')
    .addItem('Mark Selected as Follow-Up', 'markAsFollowUp');

  ui.createMenu('Client Tools')
    .addItem('Highlight Overdue Follow-Ups', 'highlightOverdue')
    .addItem('Clear All Highlights', 'clearHighlights')
    .addSeparator()
    .addSubMenu(statusMenu)
    .addSeparator()
    .addItem('Send Follow-Up Reminders...', 'promptAndSendReminders')
    .addItem('Export Active Clients to PDF', 'exportActiveToPdf')
    .addToUi();
}

// =============================================
// HIGHLIGHTING
// =============================================

function highlightOverdue() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var today = new Date();
  var overdueColour = '#fce8b2'; // amber
  var clearColour = '#ffffff';

  for (var i = 2; i <= lastRow; i++) {
    var followUpDate = sheet.getRange(i, 4).getValue(); // Column D: Follow-Up Date
    var status = sheet.getRange(i, 5).getValue();       // Column E: Status
    var rowRange = sheet.getRange(i, 1, 1, 6);

    if (followUpDate instanceof Date && followUpDate < today && status !== 'Complete') {
      rowRange.setBackground(overdueColour);
    } else {
      rowRange.setBackground(clearColour);
    }
  }

  SpreadsheetApp.getUi().alert('Overdue follow-ups highlighted in amber.');
}

function clearHighlights() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  sheet.getRange(2, 1, lastRow - 1, 6).setBackground('#ffffff');
}

// =============================================
// STATUS UPDATES
// =============================================

function setStatusForSelectedRows(statusValue) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var selection = sheet.getActiveRange();
  var startRow = selection.getRow();
  var numRows = selection.getNumRows();

  for (var i = 0; i < numRows; i++) {
    sheet.getRange(startRow + i, 5).setValue(statusValue); // Column E: Status
  }

  SpreadsheetApp.getUi().alert(numRows + ' row(s) updated to: ' + statusValue);
}

function markAsActive()   { setStatusForSelectedRows('Active'); }
function markAsInactive() { setStatusForSelectedRows('Inactive'); }
function markAsFollowUp() { setStatusForSelectedRows('Follow-Up'); }

// =============================================
// EMAIL REMINDERS
// =============================================

function promptAndSendReminders() {
  var ui = SpreadsheetApp.getUi();

  var confirm = ui.alert(
    'Send Follow-Up Reminders',
    'This will email all contacts with overdue follow-up dates. Continue?',
    ui.ButtonSet.YES_NO
  );

  if (confirm !== ui.Button.YES) {
    return;
  }

  var result = ui.prompt(
    'Reminder Message',
    'Enter a personalised note to include in each reminder (optional):',
    ui.ButtonSet.OK_CANCEL
  );

  if (result.getSelectedButton() !== ui.Button.OK) {
    return;
  }

  var note = result.getResponseText().trim();
  var sentCount = sendOverdueReminders(note);
  ui.alert('Done! ' + sentCount + ' reminder email(s) sent.');
}

function sendOverdueReminders(note) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var today = new Date();
  var sentCount = 0;

  for (var i = 2; i <= lastRow; i++) {
    var clientName  = sheet.getRange(i, 1).getValue(); // Column A
    var email       = sheet.getRange(i, 2).getValue(); // Column B
    var followUpDate = sheet.getRange(i, 4).getValue(); // Column D
    var status      = sheet.getRange(i, 5).getValue(); // Column E

    if (followUpDate instanceof Date && followUpDate < today && status !== 'Complete') {
      var body = 'Hi ' + clientName + ',\n\nThis is a follow-up reminder regarding your account.\n';
      if (note) {
        body += '\n' + note + '\n';
      }
      body += '\nPlease get in touch with us at your earliest convenience.\n\nKind regards,\nThe Team';

      MailApp.sendEmail(email, 'Follow-Up Reminder', body);
      sentCount++;
    }
  }

  return sentCount;
}

This complete example shows how all the pieces -- onOpen(), submenus, status updates, confirmation dialogs, prompts, and email sending -- come together into a coherent tool that a non-technical staff member can use entirely through the menu.

Common Issues and How to Fix Them

The menu does not appear after saving the script. Close the spreadsheet completely and reopen it. onOpen() only runs when the file opens, not when you save the script. If the menu still does not appear, check for JavaScript syntax errors in the Apps Script editor's execution log.

Clicking a menu item does nothing. Check that the function name in addItem() matches the actual function name exactly, including capitalisation. Open the Apps Script editor and run the function directly from the dropdown to see if it produces an error.

The menu appears but a function fails with a permissions error. The first time a menu item calls a function that requires permissions (Gmail, Drive, external URLs), Google will ask for authorisation. This authorisation prompt needs the user to click through it once. After that, the function runs without interruption.

Session.getActiveUser().getEmail() returns an empty string. This happens when the spreadsheet is accessed by someone outside your Google Workspace domain, or when viewed in certain sharing modes. Always check for the empty string case before using the email value in logic.

The menu works for you but not for others on the team. Each user's onOpen() runs under their own account. If your menu functions require authorisation, other users will need to authorise them separately the first time they run them. Consider deploying the script as a shared library or add-on if you need consistent authorisation across a team.

Affiliate & Partner Programs

If you are not yet on Google Workspace, or you are looking to upgrade your plan to unlock the full capabilities of Apps Script (including higher quotas and advanced features), the official Google Workspace referral programme is worth reviewing:

• Google Workspace: https://referworkspace.app.goo.gl/ — Business Starter starts from approximately AUD $10 per user per month. Business Standard (AUD $17/user/month) and above include increased Apps Script quotas, shared drives, and more storage, making them the right choice for teams building scripts that run frequently or process large volumes of data.

All Google Workspace plans include Apps Script at no additional cost. There is no separate subscription, API key, or developer account required to start building custom menus and automations.

Conclusion

Custom menus are one of the most practical things you can build with Apps Script. They take automation that only lives in the script editor and surface it directly in the spreadsheet interface where your team already works. A well-designed menu transforms a spreadsheet from something people passively fill in into something that actively helps them do their job.

The building blocks are straightforward: onOpen() to create the menu when the file opens, addItem() to connect labels to functions, addSubMenu() to organise complex toolsets, conditional logic to make menus respond to context, and UI dialogs to collect input before scripts run. Each piece is independently useful and they combine naturally as your toolset grows.

Start with a single menu and a single function. Find one repetitive action your team performs in a spreadsheet -- something you do at least a few times a week -- and put a menu item in front of it. Once it is working and people are actually using it, add the next function, then the next.

The friction you remove by putting a function behind a menu item is not just the thirty seconds of navigating to the script editor. It is the mental overhead of remembering that the automation exists, knowing how to access it, and trusting that it will do what it is supposed to do. A clearly labelled menu item removes all three barriers at once.

Need help with your Google Workspace setup? Contact our team for a free consultation.