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

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 dialog and sidebar 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.

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. Custom functions are Apps Script functions you define that can be called in the same way.

See Custom functions in Sheets Add-ons for more information on implementing and using custom funtions in add-ons.

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.

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.