0

I'm trying to create a Google Sheets add-on that runs in a sidebar. This is my first and I'm having a hard time working through the Auth modes.

This is the doc I'm using to reference.

When someone in my org installs the script, they see the menu title, but the add-on fails to load the menu items. The logging shows that Exception: You do not have permission to call getScriptProperties at .... This is from a library that I wrote and am referencing in my script. When they use it on a doc where I've used it before, they're able to see the menu items.

Based on this chart, it looks like that user is in AuthMode.NONE since they cannot access Properties.

enter image description here

This is the code that I have at the top of my file.

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

function onOpen(e) {
    console.info('onOpen(e) fired')
    var menu = SpreadsheetApp.getUi().createAddonMenu(); // Or DocumentApp or FormApp.

    if (e && e.authMode == ScriptApp.AuthMode.NONE) {
        console.info("(e && e.authMode == ScriptApp.AuthMode.NONE)")
        // Add a normal menu item (works in all authorization modes).
        menu.addItem("Show Sidebar", "showSidebar");
        menu.addToUi();
    } else {
        // Add a new menu (doesn't work in AuthMode.NONE).
        log("not (e && e.authMode == ScriptApp.AuthMode.NONE)")
        var topUI = SpreadsheetApp.getUi();
        topUI.createMenu("Test Set Creator")
            .addItem("Show Sidebar", "showSidebar")
            .addToUi();
    }
    console.info(`The user is ${e.user}`)
    console.info(`The user's auth mode is ${e.authMode}`)
    console.info(`The source is ${e.source}`)
}

So essentially, I want a user to install the add-on and then be able to use it on any spreadsheet they want in the org's domain, not just where it's been used before. I'm having a hard time wrapping my mind around how the different Auth Modes work and how they change per user/per sheet. If the user has to authorize the add on when they install it, why wouldn't they have access to the add on in a new spreadsheet they open?

Here are some other resources I've tried to use: Google Doc onOpen will not fire when spreadsheet is accessed

pcdr
  • 183
  • 2
  • 12

1 Answers1

0

Considerations

  1. Published Add-Ons will append menu items to the corresponding addon menu automatically:

Caution: Unpublished add-ons can also create custom top-level menus, but these are automatically moved under the Add-ons menu if the add-on is published and may not result in the user experience you intended. If you intend to publish the add-on, always use Ui.createAddonMenu() to define the add-on menu.

  1. onOpen() function is designed to run in AuthMode.NONE the first time for every document. If you already authorized the Add On on another document, opening a new document won't prompt you for authorization again. However, it won't allow you to run the first onOpen() function in AuthMode.LIMITED. This means that you should always trigger auth requiring operations by Add On menu items.

Keep in mind that the second time you open your document the onOpen() function will run in AuthMode.LIMITED, this means that refreshing the page will do the trick. That said, if you found a way to trigger the onOpen() function again this behavior could be obtained automatically.

My advise here is to create a menu voice in your add-on that basically starts up your add-on functionalities, builds the proper menu items and UIs. In this way you won't create confusion between new users and the ones who already concluded the authorization flow successfully.

References

Add-on Menus

Authorization lifecycle

Alessandro
  • 2,848
  • 1
  • 8
  • 16