How to Add a Universal Custom Menu to Multiple Google Workspace Apps

Google Sheets - Custom Menu with Apps Script

Adding a custom menu in Google Sheets, Docs, Slides, and Forms using Google Apps Script is straightforward.

As an illustration, the following code snippet adds a custom menu to the parent Google Sheet that reveals the spreadsheet name upon clicking.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('➯ Custom menu');
  menu.addItem('Show spreadsheet name', 'showName');
  menu.addToUi();
}

function showName() {
  const fileName = SpreadsheetApp.getActiveSpreadsheet().getName();
  SpreadsheetApp.getUi().alert(fileName);
}

The above code defines two functions: onOpen which executes when the app opens, and showName which is triggered when the menu item is clicked.

This approach can be applied to create custom menus in other Google Workspace apps, including Forms, Slides, and Google Docs.

Multiple Workspace Apps, One Custom Menu

The code above is specific to Google Sheets and will not work if you use it to add custom menus in Google Forms or Slides. That’s because you need to call SpreadsheetApp.getUi() to get the sheet’s UI instance while the Google Form’s UI can be accessed through a different FormApp.getUi() method.

This can be a problem because some Google add-ons, Document Studio for example, can be launched from multiple Google Workspace apps. How do you identify the currently active Workspace application (Sheets, Docs, Slides, or Forms) and add menu items that are specific to that app?

Identify the Current Workspace App

The getContainer function is your secret weapon for identifying the currently active Google Workspace application. It works by iterating through a list of known app classes, like DocumentApp and SpreadsheetApp.

For each class, it attempts to access the UI object. If the call is successful and doesn’t throw an exception, it indicates that the corresponding app is active and returns that app class.

const getContainer = () => {
  const apps = [DocumentApp, SpreadsheetApp, FormApp, SlidesApp];
  const activeApp = apps.find((app) => {
    try {
      app.getUi();
      return true;
    } catch (f) {
      return false;
    }
  });

  return activeApp;
};

Building the Universal Custom Menu

Now that you know the current Workspace application, we can modify the onOpen function to create a dynamic universal menu. The menu title contains the name of the active application and includes a menu item to display the app-specific file name.

const onOpen = () => {
  const app = getContainer();
  const ui = app.getUi();
  const appName = String(app).replace('App', '');
  const menu = ui.createMenu(`Custom menu in ${appName}`);
  menu.addItem(`Show ${appName} name`, 'showAppName');
  menu.addToUi();
};

The showAppName function uses a switch statement to determine the appropriate method for retrieving the file name based on the active app.

const showAppName = () => {
  const app = getContainer();
  let fileName;
  if (app === DocumentApp) {
    fileName = DocumentApp.getActiveDocument().getName();
  } else if (app === SpreadsheetApp) {
    fileName = SpreadsheetApp.getActiveSpreadsheet().getName();
  } else if (app === SlidesApp) {
    fileName = SlidesApp.getActivePresentation().getName();
  } else if (app === FormApp) {
    fileName = FormApp.getActiveForm().getTitle();
  }
  app.getUi().alert(`You are looking at ${fileName}`);
};

Post a Comment

0 Comments