Custom functions in Sheets Add-ons

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:

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 function error. 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 service error 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.