Sheets Add-ons can define Sheets custom functions. When a user installs the add-on, any defined custom functions included with the add-on become available immediately. It is possible for an add-on to consist of only custom function definitions. Custom function definitions are primarily shared with others by publishing an add-on containing the definitions.
Creating add-on custom functions
Any function defined in an add-on script project can be used as a custom
function. Once the function is implemented and the add-on is installed, you
can call the custom function like any other built-in Sheets function: in a
Sheet cell, enter the
= followed by the name of the function and any required
parameters. If there are no errors, the result returned by the function is
placed in the Sheet cell, overflowing to neighboring cells as necessary.
When creating custom functions in an add-on you should follow the general custom function guidelines:
- Function naming guidelines
- Defining function arguments
- Defining the function return value
- Custom function data types
- Enabling autocomplete using JSDoc
- Services custom functions can use
- Optimizing custom functions
In addition, custom functions defined in add-ons have some special considerations:
- When naming your function, try to create a unique name, perhaps related to the name of your add-on. If two installed add-ons define custom functions with the same name, only one can be used.
- Your add-on should clearly communicate what custom functions it provides. Be sure to provide accurate JSDoc comments for your custom functions so that Apps Script can present autocomplete information to the user. In addition, consider providing additional documentation of the custom functions either in the add-on itself or on an add-on support web page.
- Custom functions that don't complete in under 30 seconds fail with an
Internal error executing the custom functionerror. Build a good user experience by limiting the amount of processing you do in a custom function. Optimize the function where you can.
- Custom functions can't use Apps Script services that require authorization,
and fail with a
You do not have permission to call X serviceerror if this is attempted. Only use the permitted services in your custom function.
- Each custom function in a sheet results in a separate call to the Apps Script servers. If a user attempts to use custom functions in too many cells, the functions may execute slowly. To mitigate this, keep your custom functions as simple as possible. If you need the function to perform complex or extended processing, don't use a custom function—provide that functionality via a menu item, dialog, or sidebar interaction instead.