Adding Menus to Google Sheets Add-on in the Extensions menu (not custom menus)-CodePudding

I can not get menus to show up in the Extensions menu after publishing a Google Sheet add-on. Watch the video to understand what is needed. Just wanting to get this into the test deployment phase.

https://vimeo.com/681119106/b7628ebe1b

The error I keep getting is

Exception: Cannot call SpreadsheetApp.getUi() from this context. onOpen @ Common.gs:6 onInstall @ Common.gs:2

FYI: Line 6 is var menu = SpreadsheetApp.getUi().createAddonMenu();

I've stripped out the unrelated code.

My Manifest file: appscript.json.

{
  "timeZone": "America/New_York",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.locale"
  ],
  "runtimeVersion": "V8",
  "addOns": {
    "common": {
      "name": "BasicTestMenu",
      "logoUrl": "https://www.gstatic.com/images/icons/material/system/1x/pets_black_48dp.png",
      "useLocaleFromApp": true,
      "homepageTrigger": {
        "runFunction": "onHomepage",
        "enabled": false
      },
      "universalActions": [
        {
          "label": "Learn PPCAdlab Scripts",
          "openLink": "https://dummy.com"
        }
      ]
    },
    "sheets": {
      "homepageTrigger": {
        "runFunction": "onOpen"
      }
    }
  }
}

And my script file: Common.gs

function onInstall(e){
  onOpen(e);
}

function onOpen(e) {
  var menu = SpreadsheetApp.getUi().createAddonMenu(); // Or DocumentApp or SlidesApp or FormApp.
  if (e && e.authMode == ScriptApp.AuthMode.NONE) {
    // Add a normal menu item (works in all authorization modes).
    menu.addItem('Start workflow', 'startWorkflow');
  } else {
    // Add a menu item based on properties (doesn't work in AuthMode.NONE).
    var properties = PropertiesService.getDocumentProperties();
    var workflowStarted = properties.getProperty('workflowStarted');
    if (workflowStarted) {
      menu.addItem('Check workflow status', 'checkWorkflow');
    } else {
      menu.addItem('Start workflow', 'startWorkflow');
    }
    // Record analytics.
    UrlFetchApp.fetch('http://www.example.com/analytics?event=open');
  }
  menu.addToUi();
}

CodePudding user response：

As you have taken the sample from here the following step, publishing the extension, is outlined here. Here is also the Docs for testing an Editor-addon. If you confirm you have done these steps, then we can have another whack at it.

CodePudding user response：

I was able to test it on my end and it works just fine, custom menu items are created successfully after publishing the add-on but not in Test Deploy. However in order to make it work, take the following into consideration:

The app must be published as Sheets add-on on the Marketplace SDk configuration in Cloud console
Since you are using UrlFetchApp.fetch() from your code, make sure to include the urlFetchWhitelist array to your manifest file. Please refer to this and this page for guidelines on how to do it.

Regarding the inability for Simple Triggers to properly run on Add-on Test Deployments, there is a long standing report of issues related to this behavior.

In conclusion, some discrepancies of Add-on behavior are currently expected when using the Add-on Test Deploy.