Google Sheets Add-On, custom menu not being added?

2
votes

I'm currently trying to make a Google Sheets add-on for use by my office. I've gone through the work of getting it published privately in the marketplace and distributing it domain-wide, but I'm running into the issue of the menu items not being added properly. I've been reading about AuthMode, and from what I understand, my users will all be initially AuthMode.NONE, which should be able to add simple menus. Maybe my understanding of "simple menus" is different. I really need to get this working within a week before we switch all of our computers to ChromeOS (not my decision, IT's decision)

Here's what I assume should be all of the relevant code. Each function is properly defined as well.

function onOpen(e) {
    SpreadsheetApp.getUi().createAddonMenu('Usage Macros')
        .addItem('Summarize', 'firstRun')
        .addSubMenu(SpreadsheetApp.getUi().createAddonMenu('Rate')
            .addItem('PG&E', 'pge')
            .addItem('CARE', 'care'))
        .addSubMenu(SpreadsheetApp.getUi().createAddonMenu('Proposal Type')
            .addItem('SunRun 0%', 'sunRun0')
            .addItem('SunRun 1.9%', 'sunRun19')
            .addItem('SunRun 2.5%', 'sunRun25')
            .addItem('SunRun 2.9%', 'sunRun29')
            .addItem('SunRun 3.5%', 'sunRun35')
            .addItem('SunPower 0%', 'sunPower0')
            .addItem('SunPower 2%', 'sunPower2')
            .addItem('SunNova 0%', 'sunNova0')
            .addItem('SunNova 0.9%', 'sunNova09')
            .addItem('SunNova 1.9%', 'sunNova19')
            .addItem('SunNova 2.9%', 'sunNova29')
            .addItem('Cash', 'cash')
            .addItem('GreenSky', 'greenSky')
            .addItem('EnerBank', 'enerBank'))
        .addItem('Finalize','kleanUp')
        .addToUi();
}

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

I should also mention that the "Summarize" menu item and "firstRun" were added because originally the onOpen function called 2 other functions that changed some formatting and snipped useless info from the documents being opened. I removed that from the onOpen, and added a menu item for them, hoping it would help. Alas it did not.

2
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-apps-script-addon
So, the menu is being added? Just not structured the way you want? – Alan Wells
Sorry, I just noticed how I wrote part of that does make it seem like that's what I said. But no, none of my menus are being added. The name of the add-on appears in the add-on menu, but the only thing in there is "Help" – Joshua Dietrich
Try putting the onInstall() function at the very top of code.gs. I know that I've experienced some strange quirk in the past, but can't remember what it was. – Alan Wells
I just tried that, and it's still not working when I test it as an add-on. – Joshua Dietrich
"If the script is published as an add-on, the caption parameter is ignored and the menu is added as a sub-menu of the Add-ons menu, equivalent to createAddonMenu()" source – ExtractTable.com

2 Answers

1
votes

Based on the document:

If an add-on tries to create a top-level menu using the createMenu(name) syntax shown above, the name argument is ignored and the script is given an entry in the Add-ons menu under the add-on's published name.

Here what I have done to your code:

Using the createAddonMenu()

function onOpen(e) {
var menu = SpreadsheetApp.getUi().createAddonMenu()

menu.addItem('Summarize', 'firstRun')
.addSubMenu(menu
.addItem('PG&E', 'pge')
.addItem('CARE', 'care'))
.addSubMenu(menu
.addItem('SunRun 0%', 'sunRun0')
.addItem('SunRun 1.9%', 'sunRun19')
.addItem('SunRun 2.5%', 'sunRun25')
.addItem('SunRun 2.9%', 'sunRun29')
.addItem('SunRun 3.5%', 'sunRun35')
.addItem('SunPower 0%', 'sunPower0')
.addItem('SunPower 2%', 'sunPower2')
.addItem('SunNova 0%', 'sunNova0')
.addItem('SunNova 0.9%', 'sunNova09')
.addItem('SunNova 1.9%', 'sunNova19')
.addItem('SunNova 2.9%', 'sunNova29')
.addItem('Cash', 'cash')
.addItem('GreenSky', 'greenSky')
.addItem('EnerBank', 'enerBank'))
.addItem('Finalize','kleanUp')
.addToUi();
}

Result

enter image description here

Using the createMenu()

function onOpen(e) {
var menu = SpreadsheetApp.getUi()

menu.createMenu('Usage Macros')
.addItem('Summarize', 'firstRun')
.addSubMenu(menu.createMenu('Rate')
.addItem('PG&E', 'pge')
.addItem('CARE', 'care'))
.addSubMenu(menu.createMenu('Proposal Type')
.addItem('SunRun 0%', 'sunRun0')
.addItem('SunRun 1.9%', 'sunRun19')
.addItem('SunRun 2.5%', 'sunRun25')
.addItem('SunRun 2.9%', 'sunRun29')
.addItem('SunRun 3.5%', 'sunRun35')
.addItem('SunPower 0%', 'sunPower0')
.addItem('SunPower 2%', 'sunPower2')
.addItem('SunNova 0%', 'sunNova0')
.addItem('SunNova 0.9%', 'sunNova09')
.addItem('SunNova 1.9%', 'sunNova19')
.addItem('SunNova 2.9%', 'sunNova29')
.addItem('Cash', 'cash')
.addItem('GreenSky', 'greenSky')
.addItem('EnerBank', 'enerBank'))
.addItem('Finalize','kleanUp')
.addToUi();
}

Result

enter image description here enter image description here

Hope this helps!

0
votes

You probably need to add an onInstall() function that calls your onOpen() function.

The most common use of onInstall(e) is simply to call onOpen(e) to add custom menus. After all, when an add-on is installed, the file is already open, and thus onOpen(e) doesn't run on its own unless the file is reopened.

So:

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