I have a bunch of custom functions for Google Sheets, that I currently copy-paste around as a container-bound script. I'd like to package up into a standalone add-on. However, when I make a standalone Apps Script project, and deploy it (via the Deploy > Test Deployments > Test latest code > Install menu options), then while the add-on does get installed (eg, I see its icon in my sheets), it does not register any of my custom functions. How do I make a stand-alone Apps Script project that registers custom functions in spreadsheets where it is installed?

This question is related, but I don't think the discussion there is applicable. In particular, that answer suggests that the script has to add some custom menu item, presumably using code like

function onOpen() {
  SpreadsheetApp.getUi().createMenu('My Extension')
    .addItem('Enable', 'doNothing')
    .addToUi();
}

function doNothing() {
}

because the custom functions don't get registered until the user interacts with the script through such a menu item or something, for Unexplained Reasons™. However, that answer is from years ago, and doesn't seem to work.

In particular, when I attempt this, my stand-alone script doesn't even succeed in adding the Enable menu item. I've done a bit of poking around, and developed the hypothesis that I'm supposed to add some sort of "trigger" that tells Google to run the onOpen function whenever the user opens a spreadsheet, and indeed I can do this in a container-bound script (via the Triggers > Add Trigger > Select event source: From spreadsheet pathway), but my standalone script does not have From spreadsheet as an available event source, and so I'm not sure how to even get my menu-adding function to run onOpen in a stand-alone script. Which is to say, I don't even know how to straightforwardly add a menu item that the user can interact with in attempts to load the custom functions.

I was however able to go to the manifest file and configure addOns.common.homepageTrigger.runFunction, and name a function that produces a Card that contains a button that, when pressed, adds a menu item, on the hopes that either the press of the button, or failing that the interaction with the added menu item, would convince Google that it's time to load the custom functions.

But: no dice. The homepageTrigger works (producing a card with a button), and the button works (by adding a new menu item), and the new menu item works (by dutifully calling doNothing), but none of this results in the custom functions being made available in the Sheet. This shows both that I'm not completely failing to get my script running (it's definitely installed, and responsive to me, eg, changing the button text, showing that I'm not just stuck on an old version or w/e), and that the related question linked above is not applicable to my situation.

And, it goes without saying, my code works great as a container-bound script, so I'm fairly confident that I'm not flubbing the basics of adding custom functions (and registering them with the intellisense using @customfunction in the jsdocs). So, then, however do I register custom functions in Google Sheets from a stand-alone Apps Script add-on?

CodePudding user response：

Issues:

• You are mixing up Workspace add-ons, which work through card-based interfaces, and editor add-ons, which include the functionality for custom functions. Deploy > Test Deployments > Test latest code > Install refers to testing a Workspace add-on, but you need an editor add-on in order to use custom functions.
• Currently, custom functions only work for published editor add-ons, not while testing them (see "Test as add-on" from standalone project to Google Sheets doesn't link custom functions).

Solution:

You have to publish the editor add-on for this to work.