2

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?

So8res
  • 9,856
  • 9
  • 56
  • 86
  • Here is the "SO version" of the linked WebApps -> https://stackoverflow.com/q/30878247/1595451. Long Story short: the related issue is still open -> [Issue #36763437. "Test as add-on" from standalone project to Google Sheets doesn't link custom functions](https://issuetracker.google.com/issues/36763437) (rel. https://stackoverflow.com/a/61887467/1595451) P.S. Have you already tried to publish your add-on? – Rubén Sep 30 '21 at 03:38
  • Thanks! It's not obvious to me that the "new" deployment workflow (which involves routing through a google cloud platform project, and which has different notions of public and private iiuc) matches the old "test as add-on" workflow to which that comment applies. In particular, I wasn't able to get custom functions working even when I made a non-test "versioned" deployment. So if that old state still applies, there's still a question as to what the new workaround is, and how to use it in the new deployment workflow. – So8res Sep 30 '21 at 04:00
  • Have you already tried to publish your add-on? (it's very likely that you have to focus your efforts on that P.S. don't forget to include a custom menu so to help users on enable the add-on on the spreadsheets so the custom functions will be available on them) – Rubén Sep 30 '21 at 04:11

1 Answers1

2

Issues:

Solution:

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

Iamblichus
  • 18,540
  • 2
  • 11
  • 27
  • 1
    I'm confused about the difference between workspace and editor add-ons. Some notes for posterity, in case others come along and share my issue: it looks like I can only test editor add-ons through the "legacy editor", and many of the above links make more sense in the legacy editor. – So8res Sep 30 '21 at 14:26
  • Publishing my add-on is prohibitively time-expensive, so I haven't personally verified that the above is the answer, but it seems sufficiently plausible that I'm marking the answer as accepted. – So8res Sep 30 '21 at 14:35
  • 2
    @So8res Yes, currently editor add-ons can only be tested with the legacy editor. – Iamblichus Oct 01 '21 at 07:07
  • The coding of an add-on takes minutes - the script reference is great. Making it installable by others in your company takes days of documentation cruising, much of which uses links to explain terms that end up linking in circles. Hence the store is full of apps that charge you for functionality that take a couple lines of JS. I've done this before but too long ago to remember all the app types, terms and steps. I now know I need something called "an editor add-on", I am sitting looking at my working code, having tried the above links, none the wiser. Current: deploy, convert type, dead end. – Chris May 22 '23 at 07:53