CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptgoogle-sheets-api
Marketing Rigotti Consultoria
Marketing Rigotti Consultoria

Reputation: 11

Custom Menu Not Displaying in Google Sheets Add-on After Deployment

The problem:

I am developing a Google Sheets add-on using Google Apps Script. The goal is to create a custom menu in Google Sheets that allows users to apply specific formatting options. The add-on has been successfully deployed, but the custom menu does not appear in the Sheets interface after installation.

What I’ve Done:

  1. Developed the Script:

    function onOpen(e) {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu('Custom Tools')
        .addItem('Apply General Formatting', 'ApplyGeneralFormatting')
        .addToUi();
}

function ApplyGeneralFormatting() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const range = sheet.getDataRange();
    range.setHorizontalAlignment("center");
    range.setVerticalAlignment("middle");
    range.setWrap(true);
}

  2. Configured the Manifest:

    {
  "timeZone": "America/Sao_Paulo",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.container.ui"
  ],
  "addOns": {
    "common": {
      "name": "SheetFormatter",
      "logoUrl": "https://example.com/logo.png",
      "useLocaleFromApp": true
    },
    "sheets": {
      "homepageTrigger": {
        "runFunction": "onOpen"
      }
    }
  }
}

  3. Published the Add-on:

    • The add-on is visible in the Google Workspace Marketplace.
    • Installed it successfully in my account.

  4. Troubleshooting:

    • The add-on appears as installed under "Extensions > Add-ons > Manage Add-ons."
    • When I test the onOpen() function in the Apps Script editor, it logs the following error: 
      Exception: Cannot call SpreadsheetApp.getUi() from this context.

What I Need Help With:

Any guidance or insights would be greatly appreciated!

Upvotes: 1

Views: 59

Answers (1)

TheMaster
TheMaster

Reputation: 50761

It's important to note that there are two types of addons:

  • Editor addons
  • Workspace addons

Editor addons create menus and run from there. Workspace addons create homepages and cards. Triggers like onOpen run automatically on open when it is a editor addon. While for workspace adfons, you need to click the button on right panel. As you've seen, it's not possible for a homepageTriggered function to interact with the Ui. It seems you've created a workspace addon, while you're looking for functionality of editor addons. To convert to editor addons,

  • Remove addOns object from manifest
  • In Cloud console, remove Workspace addon from App Integrations.

Related:

Upvotes: 2

Related Questions