Custom Functions with add-ons?

Question

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?

Answer 1

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:

• "Test as add-on" from standalone project to Google Sheets doesn't link custom functions
• After activating an add-on, custom functions get stuck Loading... until spreadsheet refresh