Google Sheets Adds-on open automatically when user

Question

I created an google sheets add-on. However, it is a bit hidden in the adds-on menu. Is there a way to enable the Adds-on automatically when user opens a sheet or when user clicks a link in a sheet? I searched on Google Sheets documentation but found nothing.

EDIT1:

Since opening an addon sidebar automatically seem to be a bad user experience, how about opening by clicking a link in a sheet? Let user choose to open the sidebar by clicking a link in a sheet that is inserted by an addon.

Answer 1

Sure, you can get a UI component for an add-on to open automatically when a document is opened. This is HORRIBLE behavior for an add-on, and it should never be published because it would interfere with other add-ons. But still, it can be done.

There are some restrictions, though.

• The add-on must be enabled for that document. Refer to Add-on Authorization Lifecycle.
• Depending on the operations the add-on performs, it may need to authorized by the user. For example, if UserA and UserB share a document, and UserA installs and authorizes the add-on, UserB will see the add-on enabled as well, but would need to authorize it separately to allow it to run on their account.

It could as simple as adding a call to showSidebar() in your onOpen() function, for an add-on that doesn't require any kind of authorization.

function onOpen(e) {
  var ui = SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Show sidebar', 'showSidebar')
      .addItem('Show dialog', 'showDialog')
      .addToUi();

    // Display sidebar
    showSidebar();
}

But what if we want to have the option to have that behaviour enabled and disabled? Instead of blindly opening the add-on sidebar, the onOpen() could have a menu item to control behavior, and only open the add-on when enabled.

/**
 * Adds a custom menu with items to show the sidebar and dialog.
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  var ui = SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Show sidebar', 'showSidebar')
      .addItem('Show dialog', 'showDialog')
      .addSeparator()
      .addItem(autoEnabled_()?"Disable auto-sidebar":"Enable auto-sidebar", "autoToggle_")
      .addToUi();

    // Display sidebar if auto-sidebar is enabled
    if (autoEnabled_()) showSidebar();
}

Now we've introduced two functions related to control of the auto-sidebar, autoEnabled_() and autoToggle_(). The first appears to tell us what the enabled state is for the auto-sidebar, while the second appears to change state, and is provided as a string parameter in a menu item (... so it cannot accept a parameter).

Since the auto-display only matters when a document is being opened, we will need some way to remember the user's setting between uses of the document. That can be addressed using the PropertyService. We need to be careful, though, because that service requires authorization. Since this is an add-on which will run with ScriptApp.AuthMode.NONE when first installed, we can't rely on being able to access the service. So, wrap it up in a try...catch block in that case.

To keep the code easy to maintain, it's best if we keep "messy" functions like the PropertyService in one place. "Messy", because it relies on string maps for storage and retrieval of data, and simple typos in our code would introduce bugs that would be difficult to find. To reduce this long-term quality cost, then, we can combine the TWO functions we need into one, by making the state toggle a sub-case of reading the current setting. Here's the resulting autoEnabled_() function, with autoToggle_() simply providing a parameter back to autoEnabled_() to change state.

/**
 * Get status of auto-sidebar, and optionally change it.
 *
 * @var {any} optSet  (optional) Any truthy value will change the setting.
 *
 * @returns {Boolean} Returns true if enabled, false if not.
 *                    Always returns false if ScriptApp.AuthMode.NONE.
 */
function autoEnabled_(optSet) {
  try {
    var autoState = PropertiesService.getUserProperties().getProperty('autoState');
  }
  catch (e) {
    // Called with ScriptApp.AuthMode.NONE
    return false;
  }

  if (optSet) {
    autoState = (autoState == 'enabled')?'disabled':'enabled';
    PropertiesService.getUserProperties()
                     .setProperty('autoState',autoState);
    // Re-run the onOpen function to update menu
    onOpen({authMode:ScriptApp.AuthMode.LIMITED});
  }

  return autoState == 'enabled';
}

/**
 * Toggle state of auto-sidebar.
 */
function autoToggle_() {autoEnabled_('toggle');}  // remove underscore and call from debugger to enable logs w/o UI

The complete snippet appears below. To try it out, start with the generic "Add on" script provided as a template in the editor, and replace the original onOpen() with the code from the snippet.

/**
 * Adds a custom menu with items to show the sidebar and dialog.
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  var ui = SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Show sidebar', 'showSidebar')
      .addItem('Show dialog', 'showDialog')
      .addSeparator()
      .addItem(autoEnabled_()?"Disable auto-sidebar":"Enable auto-sidebar", "autoToggle_")
      .addToUi();

    // Display sidebar if auto-sidebar is enabled
    if (autoEnabled_()) showSidebar();
}

/**
 * Get status of auto-sidebar, and optionally change it.
 *
 * @var {any} optSet  (optional) Any truthy value will change the setting.
 *
 * @returns {Boolean} Returns true if enabled, false if not.
 *                    Always returns false if ScriptApp.AuthMode.NONE.
 */
function autoEnabled_(optSet) {
  try {
    var autoState = PropertiesService.getUserProperties().getProperty('autoState');
  }
  catch (e) {
    // Called with ScriptApp.AuthMode.NONE
    return false;
  }
  
  if (optSet) {
    autoState = (autoState == 'enabled')?'disabled':'enabled';
    PropertiesService.getUserProperties()
                     .setProperty('autoState',autoState);
    // Re-run the onOpen function to update menu
    onOpen({authMode:ScriptApp.AuthMode.LIMITED});
  }

  return autoState == 'enabled';
}

/**
 * Toggle state of auto-sidebar.
 */
function autoToggle_() {autoEnabled_('toggle');}  // remove underscore and call from debugger to enable logs w/o UI

Acknowledgement

Thanks to Serge for presenting this idea to me some time back, when we were first collaborating on an add-on idea!