Google Sheets is a cloud-based spreadsheet solution with real-time collaboration and powerful tools to visualize, process and communicate data.
You can extend Sheets with add-ons that build customized workflow improvements, establish connectivity to third-party systems, and integrate your Sheets data with other G Suite applications (like Google Slides).
You can see the Sheets add-ons others have built in the Sheets add-on store.
What you can do
Here are a few things you can do with an add-on that extends Google Sheets:
- You can read, edit, visualize, and format data in Google Sheets spreadsheets using the built-in Apps Script Spreadsheet service. The service also lets you create and modify conditional formatting and data validation rules.
- You can use the Apps Script advanced Sheets service to access the Sheets REST API directly.
- You can create custom menus and define multiple custom dialogs and sidebars interfaces using standard HTML and CSS.
- You can include custom function definitions in your add-on.
- You can use add-ons triggers that run specified functions when certain triggering events occur.
Sheets add-ons are built using Apps Script. To learn more about how to access and manage Google Sheets with Apps Script, see Extending Google Sheets.
A Google Sheets spreadsheet consists of one or more sheets. Each sheet is essentially a 2D grid of cells into which text, numbers, links, or other values can be stored. A group of one or more adjacent cells is called a range.
The Apps Script Spreadsheet service
provides several classes to represent organizational structures in Sheets
Range). You can use these
classes to read and modify Sheets data and behavior.
Apps Script triggers let a script project execute a specified function when certain conditions are met, such as when a spreadsheet is opened or when an add-on is installed.
See Add-on triggers for more information on what triggers can be used with Sheets add-ons and what restrictions apply to their use.
Google Sheets has a number of
AVERAGE that can be invoked from within a Google Sheet cell.
Sheets add-ons can define additional
custom functions to supplement these
built-in 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 or more installed add-ons define custom functions with the same name, users can only use one of them.
- 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.
Macros let you record actions taken in Google Sheets and repeat them later with a keyboard shortcut. When a macro is created in a sheet, it is added as a macro function in an Apps Script project bound to that sheet. For more information about macros, see Google Sheets macros.
Unfortunately, Sheets macros can't be distributed with add-ons. If you include a macro definition in an add-on's manifest, it is unavailable to users of that add-on.