14

I've been trying to create a Google Spreadsheet plugin from some existing Google App Scripts that I have, and one important part of this app script is Custom Functions.

Though the documentation for the add-ons doesn't indicate that this is supported, the documentation for Custom Functions does indicate that you can.

https://developers.google.com/apps-script/guides/sheets/functions

Through testing, I have not once been able to get Custom Functions exposed through a add-on. Does anyone know the secret sauce to get this to work?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Bob Wold
  • 371
  • 1
  • 3
  • 5
  • 3
    Weird. I can get some custom functions to work in an addon, but not others. Experiment: create standalone script, with code from "Custom functions" sample in editor. Save. Test as an addon with any existing spreadsheet. `SAY_HELLO()` won't work, but all the other functions in the script do. I tried changing order of the functions, no change. Added other functions to add-on, they didn't work either. Renamed `INCREMENT` to `PIZZA`, it still worked as `INCREMENT` but not `PIZZA`. Something is very wrong here. – Mogsdad Jun 17 '15 at 03:23
  • 2
    @Mogsdad: Please star the following related GAS issues: https://code.google.com/p/google-apps-script-issues/issues/detail?id=5603, https://code.google.com/p/google-apps-script-issues/issues/detail?id=5590 – Rubén Dec 20 '15 at 13:24

1 Answers1

17

Answer

According to Eric Koleda in [Code.gs - date_add_and_subtract]( https://github.com/google/google-apps-script-samples/blob/master/date_add_and_subtract/Code.gs) to make available the custom fuctions in an add-on it's required to include at least one add-on menu, but this is not working at this time on testing mode.

NOTE: The originally referred page was removed but the Date add and substract add-on sample code is available at https://github.com/googlesamples/apps-script/tree/master/sheets/dateAddAndSubtract.

The "solution" to test a custom function add-on is to publish the add-on privately so you could avoid the Google review of an untested add-on. Related Q&A: Publish an add-on privately

NOTES:
  1. You have to create a Google Cloud Project, set OAuth Consent Screen, add the Google Workspaces Marketplace SDK, complete the configuration page, the listing page and to publish the add-on
  2. Setting the OAuth Consent Screen for Internal Use requires to a Google Workspace Account
  3. Setting the OAuth Consent Screen for External Use limited to test account only doesn't allow to publish the add-on to the GW Marketplace.

One scenario is to use different Google Cloud Projects for testing and production but use the same Google Apps Script project. Another scenario is to have two different Google Apps Script projects each one with their own Google Apps Project and someway copy the code from one to the other i.e. using Google Apps Script GitHub Assistant Chrome Extension, CLASP or the Google Apps Script API.

Remarks

There are a couple of related GAS issues, please star them:

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • 3
    I tried the date_add_subtract and that add on is working. Also, I tried creating my own custom functions and they are working. Same as the date_add_subtract linked code, I have added a menu in the addon, which basically does nothing except showing an alert. Then, once you click on that menu, after that the custom formulas are coming up in intellisense and working. Authentication mode used for Installation Config is 'installed & Enabled.' – Ankur Feb 21 '17 at 12:56
  • Not sure if this is a new restriction, but Google states "Warning: Once you save your visibility option during the publication process, you can't change your selection later. Make sure you know what visibility your add-on needs before you start the publication process" ([source](https://developers.google.com/workspace/add-ons/how-tos/publish-add-on-overview)). I guess this means if you publish your add-on as private, you'll have to create a new project to publish it as public. – thdoan Apr 18 '21 at 20:35
  • @thdoan Yes, this is a relatively new restriction and your guess is correct, more specifically a new GCP, the Google Apps Script project could be the same. – Rubén Apr 18 '21 at 20:39
  • Anyone found a solution to this? I cannot find a way to make it work using a TEST deployment of the addon. – M4hd1 Aug 26 '21 at 17:27
  • @M4hd1 The related issue is still open, so the workaround is , as is already mentioned in the answer, to publish the add-on (P.S: I updated my answer) – Rubén Sep 30 '21 at 04:41
  • https://stackoverflow.com/questions/75159727/enable-and-disable-custom-google-sheet-functions-using-google-apps-script can anyone help me with this? – Code Guy Jan 18 '23 at 13:09