Extending Google Sheets with add-ons

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 Google Workspace applications (like Google Slides).

You can see the Sheets add-ons others have built on the Google Workspace Marketplace.

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 Google Sheets 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.

Sheet structure

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 (such as Sheet and Range). You can use these classes to read and modify Sheets data and behavior.

Triggers

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.

Custom functions

Google Sheets has a number of built-in functions like SUM and 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 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.

Sheets macros

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.