3

I have some trouble understanding this. In Editor Addons it is easy to run a function for example to create custom menu items by using onOpen trigger. But I cannot figure out how I would achieve the same result with Google Workspace addons. There are no simple triggers available so I am left with 2 options which do not satisfy my needs:

  1. I use a manifest homepageTrigger

    "sheets": {
    "homepageTrigger": {
        "runFunction": "initializeMenu"
    }
}

The problem with this is that this function doesn't run until you click the Addon icon in the panel on the right.

  1. I create an installable trigger

    ScriptApp.newTrigger("initializeMenu")
.forSpreadsheet("XYZ")
.onOpen()
.create()

The problem with this one is that I need to provide a specific Spreadsheet ID. But I want my addon to work on EVERY Google Spreadsheet after it is installed.

Am I missing something here?

marketergeek
  • 67
  • 4

2 Answers2

2

After checking out the documentation, I believe this cannot be done the way you want with a Google Workspace Add-on.

As you mentioned in your post, you can use onOpen() triggers with editor add-ons to add the menu on all spreadsheets, however, Workspace add-ons have a different set of triggers. Among the list, the most relevant to us would be these:

  • homepageTriggers are a global trigger type that runs when the user clicks the add-on icon.
  • onFileScopeGranted triggers are specific to the editor apps (which includes Sheets), and they fire when the add-on gains access to the current file's scope, but it also requires user input to run.

Those are the only triggers available in Sheets if you check out the manifest file definition. Essentially the add-on cannot act by itself in the background with just manifest triggers, it needs to use installable triggers to do this, and to generate installable triggers you need the user to at least open the card once.

It is possible to create installable triggers, but the problem is that you still need one of the regular add-on triggers to fire to create these installable triggers. For example, you can create an onOpen trigger when the user opens the add-on card within a Sheet by defining it within the homepageTrigger function. Something like dobleunary's answer works for this:

Manifest:

"sheets": {
"homepageTrigger": {
  "runFunction": "onEditorsHomepage"
},

Sheets.gs

function onEditorsHomepage() {
 //probably some logic to check for duplicates
ScriptApp.newTrigger("initializeMenu")
                     .forSpreadsheet(SpreadsheetApp.getActive())
                     .onOpen()
                     .create()
}

In this situation the user clicks the add-on menu, the trigger is created for the sheet, and on subsequent runs the trigger creates the menu without needing to open the add-on again. This works and I was able to create triggers for over 30 different sheets so the 20 triggers/user/script quota may apply in a different way or maybe it counts each sheet as a different script file. This may warrant some testing to figure out the real limit since the docs do not specify.

I would recommend to just build an editor add-on instead and opt for simple triggers. You probably don't want to burden users with triggers for each of their files and even Google's comparison between add-on types emphasizes editor add-ons as the main tools to create custom menus. If you really must build a Google Workspace add-on, then you may have to just live with needing to have the users use the add-on card rather than a menu at the top.

Sources:

Daniel
  • 3,157
  • 2
  • 7
  • 15
  • Thank you for checking this and extensive explanation. I need to go with Workspace addon since it will integrate with Sheets, Calendar and Gmail and it is much easier to create 1 addon instead of 3. As you suggest I will probably put my settings in card. My first intuitive choice was custom menu because when I tested my addon it feelt faster. Cards look nice but to me they tend to lag sometimes and take couple of seconds to load. Custom menu after it loads is concise and fast. But like I said it's not a big deal, I'll just use cards. Just wanted to make sure what options I have :) – marketergeek Jan 05 '23 at 21:11
  • 1
    I'm glad it helped. Indeed a menu will usually feel faster, but I hope you find a good balance. Google has a [best practices](https://developers.google.com/apps-script/add-ons/guides/workspace-best-practices) page for add-ons, which may help with performance. Good luck! – Daniel Jan 05 '23 at 21:40
  • The [documentation](https://developers.google.com/apps-script/add-ons/concepts/workspace-triggers#installable_triggers) seems to suggest it is doable: _"In addition to manifest triggers, Google Workspace Add-ons can also use Apps Script installable triggers."_ – doubleunary Jan 05 '23 at 23:10
  • @doubleunary, yes, in my post I agree with your answer that the workaround with installable triggers is possible, the problem is that the users necessarily have to open the card to add the trigger, which is what the OP was trying to avoid. – Daniel Jan 06 '23 at 03:02
  • An installable trigger only needs to be installed one time. Once installed, the trigger will run automatically every time the spreadsheet is opened. – doubleunary Jan 06 '23 at 08:30
  • @doubleunary I’m aware, and that’s what I already said in my answer, read it closely. The user will still have to open the card at least once to install the trigger, afterwards it does work on its own, but it’s still not great UX, may run into limits and will fill the user account with triggers for each file. – Daniel Jan 06 '23 at 12:06
  • My comment is because of this phrase in the answer: _"Essentially the add-on cannot simply do something in the background when the Sheet file is opened."_ which I think is not true. An installable "on open" trigger should run fine. Have not tested though. – doubleunary Jan 06 '23 at 12:24
  • @dobleunary I see what you mean. I tested it and it does run fine, but what I meant was that to create the installable trigger the user still needs to interact with the add-on card once, so they pretty much have to remember to do it for each one of their files. I edited my post based on your suggestion to differentiate between the manifest and installable triggers to make it clearer. – Daniel Jan 06 '23 at 13:30
1

I need to provide a specific Spreadsheet ID

Instead of using a hard-coded spreadsheet ID, use the ID of the active spreadsheet, or a direct reference to the active spreadsheet object, like this:

function installOnOpenTrigger() {
  const functionNameToTrigger = 'initializeMenu';
  const ss = SpreadsheetApp.getActive();
  if (ss) {
    try {
      ScriptApp.newTrigger(functionNameToTrigger)
        .forSpreadsheet(ss)
        .onOpen()
        .create();
    } catch (error) {
      ;
    }
  }
}

SpreadsheetApp.getActive() will return the current spreadsheet whenever there is an active spreadsheet.

From the documentation:

Each add-on can only have one trigger of each type, per user, per document. For instance, in a given spreadsheet, a given user can only have one edit trigger, although the user could also have a form-submit trigger or a time-driven trigger in the same spreadsheet. A different user with access to the same spreadsheet could have their own separate set of triggers.

Add-ons can only create triggers for the file in which the add-on is used. That is, an add-on that is used in Google Doc A cannot create a trigger to monitor when Google Doc B is opened.

doubleunary
  • 13,842
  • 3
  • 18
  • 51
  • Thank you for the answer @doubleunary I think i did just that but what I achieved was that the trigger was properly installed on the first Sheet that was used. If a user opens another Sheet, the trigger already exists with the reference to the previous Sheet and it will not work in the new Sheet. Or I did something wrong... I don't know I need to re-check it once I am back at my PC. But i think that the trigger would have to have a different name for every Sheet. Which limits number of triggers to 20 per user as far as I remember – marketergeek Jan 05 '23 at 15:58
  • And yes it is possible to add custom menus :) at least I was able to do it in testing environment. I Could run `SpreadsheetApp.getUi().createMenu('Custom Menu')` from my Google Worksapce Addon test deployment and menu was successfuly created but it only appears once you visit a Homepage Card. It's like you need to initialize it every time you open a new Sheet – marketergeek Jan 05 '23 at 16:09
  • Hmm... yes, it may be tricky to test whether a trigger already exists. Perhaps it is easier to just delete all ON_OPEN triggers, then create a new one. Alternatively, you could use the `Properties` service to store a flag that tells that a trigger has already been installed. Hope you can make it work. Let us know how it goes. – doubleunary Jan 05 '23 at 16:30