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:
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.
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?
CodePudding user response：
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.
CodePudding user response：
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:
homepageTriggersare a global trigger type that runs when the user clicks the add-on icon.onFileScopeGrantedtriggers 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 simply do something in the background when the Sheet file is opened. It needs the user to at least open the card.
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.