Custom Functions with add-ons?

10
votes

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?

1
google-apps-scriptgoogle-sheetsgoogle-apps-script-addongoogle-sheets-custom-function
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
@Mogsdad: Please star the following related GAS issues: code.google.com/p/google-apps-script-issues/issues/…, code.google.com/p/google-apps-script-issues/issues/… – Rubén

1 Answers

13
votes

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

NOTE: Nowadays Google Cloud Projects visibility can't be changed but it's possible to change the Google Cloud Project linked to a Google Apps Script project.

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: