Create a custom menu with grouped actions

When Northwind has a handful of useful scripts sitting in a Sheet — a Stripe puller, a weekly digest, a dashboard exporter — non-developers should not need to open the Apps Script editor to run them. The custom menu turns the spreadsheet itself into a small control panel: a top-level “Northwind” entry with grouped actions underneath, exactly where the team is already working.

This script registers the menu on every open. Grouping by job — sync, report, admin — keeps the menu shallow and predictable, which matters once there are more than three or four entries. The mechanics are simple; the discipline is in keeping the menu boring and stable so people learn it once and trust it.

What you’ll need

• A Google Sheet that already hosts the scripts you want to expose (Stripe puller, digest, dashboard export — whichever you have).
• The exact function names of those scripts. The menu wires labels to function names, so a typo here means a silently broken menu item.
• Edit access to the Sheet’s bound Apps Script project.

The script

// One place to declare what the menu offers. Editing this array is the
// only thing a non-coder needs to do to add or rearrange items —
// onOpen below walks it without caring what is in it.
const MENU_TITLE = 'Northwind';

const MENU_GROUPS = [
  {
    label: 'Sync',
    items: [
      { label: 'Pull Stripe charges', fn: 'pullStripeCharges' },
      { label: 'Pull GitHub issues', fn: 'syncIssues' },
    ],
  },
  {
    label: 'Report',
    items: [
      { label: 'Weekly unreplied digest', fn: 'weeklyUnrepliedDigest' },
      { label: 'Monthly dashboard PDF', fn: 'emailDailyDashboard' },
    ],
  },
];

// Top-level items shown below the groups, after a separator.
const TOP_LEVEL = [
  { label: 'Help', fn: 'showHelp' },
];

/**
 * Apps Script runs onOpen automatically whenever the Sheet is opened by
 * a user who can edit it. That makes it the only safe place to build
 * the menu — it is not available from anywhere else.
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu(MENU_TITLE);

  // Build one sub-menu per group. Splitting the menu config out of the
  // wiring keeps onOpen short and the menu shape obvious at a glance.
  MENU_GROUPS.forEach((group) => {
    const sub = ui.createMenu(group.label);
    group.items.forEach((item) => sub.addItem(item.label, item.fn));
    menu.addSubMenu(sub);
  });

  if (TOP_LEVEL.length) {
    menu.addSeparator();
    TOP_LEVEL.forEach((item) => menu.addItem(item.label, item.fn));
  }

  menu.addToUi();
}

/**
 * Simple help dialog wired to the menu. Replace the URL with whatever
 * documents the actions on offer — a Doc, a Notion page, internal wiki.
 */
function showHelp() {
  SpreadsheetApp.getUi().alert(
    'Northwind tools',
    'See the internal runbook at appscript.dev for what each action does.',
    SpreadsheetApp.getUi().ButtonSet.OK
  );
}

How it works

1. MENU_GROUPS and TOP_LEVEL describe the menu as data. To add an item, a non-coder edits the array — they do not need to touch the wiring.
2. onOpen is the trigger Apps Script fires every time a user with edit access opens the Sheet. It is the only place you can mutate the menu safely; doing it from a button or another function will not stick.
3. The function creates a top-level menu, walks MENU_GROUPS to add one sub-menu per group, then adds a separator and any top-level items below it.
4. addToUi() is what actually registers the menu — without it, you have built a menu object but the user sees nothing.

Example run

After saving the script and reloading the Sheet, the menu bar gets a new “Northwind” entry. Click it and you see:

Menu path	Triggers
Northwind → Sync → Pull Stripe charges	pullStripeCharges()
Northwind → Sync → Pull GitHub issues	syncIssues()
Northwind → Report → Weekly unreplied digest	weeklyUnrepliedDigest()
Northwind → Report → Monthly dashboard PDF	emailDailyDashboard()
Northwind → (separator)
Northwind → Help	showHelp()

Clicking “Pull Stripe charges” runs that function as the current user. The first click on any item triggers the usual authorisation prompt, then it just works.

Trigger it

onOpen is a simple trigger — Apps Script wires it automatically once the function exists by that exact name. No setup in the Triggers UI, no clasp deploy. The first time you save the script, reload the Sheet to see the menu appear.

For actions that need scopes the simple onOpen cannot request (sending email, fetching URLs, calling other services), set up an installable trigger instead:

1. Triggers → Add trigger.
2. Function onOpen, event source From spreadsheet, event type On open.
3. Save and approve the broader authorisation prompt.

The menu now appears with full authorisation regardless of who opens the Sheet.

Watch out for

• onOpen only runs for users who can edit. Viewers will not see the menu — if your audience is read-only, expose actions through buttons drawn on the Sheet instead, each bound to the same functions.
• The label and the function name are independent strings. If you rename a function, the menu silently breaks until you update the entry. Search for the old name across the project after every rename.
• Keep the menu shallow. Two levels (group → action) is the limit before users get lost. If you need a third, that is a sign the Sheet is doing too many jobs and wants splitting.
• Apps Script caches the menu per session. Edits do not appear until the user reloads the Sheet — warn first-time users about that or they will assume the script is broken.
• The simple onOpen has limited scope. Anything that needs Gmail, UrlFetchApp, or Drive must run from a menu item the user clicks (which executes under their own broader auth), not from inside onOpen itself.