Google Apps Script lets you do new and cool things with Google Sheets. You can use Apps Script to add custom menus, dialogs, and sidebars to Google Sheets. It also lets you write custom functions for Sheets, as well as integrate Sheets with other Google services like Calendar, Drive, and Gmail.
Most scripts designed for Google Sheets manipulate arrays to interact with the cells, rows, and columns in a spreadsheet. If you're not familiar with arrays in JavaScript, Codecademy offers a great training module for arrays. (Note that this course wasn't developed by and isn't associated with Google.)
For a quick introduction to using Apps Script with Google Sheets, see the 5-minute quickstart guide for Macros, Menus, and Custom Functions.
Get started
Apps Script includes special APIs to let you programmatically create, read, and edit Google Sheets. Apps Script can interact with Google Sheets in two broad ways: any script can create or modify a spreadsheet if the script's user has appropriate permissions for the spreadsheet, and a script can also be bound to a spreadsheet, which gives the script special abilities to alter the user interface or respond when the spreadsheet is opened. To create a bound script, select Extensions > Apps Script from within Google Sheets.
The Spreadsheet service treats Google Sheets as a grid, operating with two-dimensional arrays. To retrieve the data from the spreadsheet, you must get access to the spreadsheet where the data is stored, get the range in the spreadsheet that holds the data, and then get the values of the cells. Apps Script facilitates access to the data by reading structured data in the spreadsheet and creating JavaScript objects for them.
Reading data
Suppose you have a list of product names and product numbers that you store in a spreadsheet, as shown in the image below.
The example below shows how to retrieve and log the product names and product numbers.
function logProductInfo() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
for (var i = 0; i < data.length; i++) {
Logger.log('Product name: ' + data[i][0]);
Logger.log('Product number: ' + data[i][1]);
}
}
View logs
To view the data that has been logged, at the top of the script editor, click Execution log.
Writing data
To store data, such as a new product name and number to the spreadsheet, add the following code to the end of the script.
function addProduct() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}
The above code appends a new row at the bottom of the spreadsheet, with the values specified. If you run this function, you'll see a new row added to the spreadsheet.
Custom menus and user interfaces
You can customize Google Sheets by adding custom menus, dialog boxes, and sidebars. To learn the basics of creating menus, see the guide to menus. To learn about customizing the content of a dialog box, see the guide to HTML service.
You can also attach a script function to an image or drawing within a spreadsheet; the function will execute when a user clicks on the image or drawing. To learn more, see Images and Drawings in Google Sheets.
If you're planning to publish your custom interface as part of an add-on, follow the style guide for consistency with the style and layout of the Google Sheets editor.
Connecting to Google Forms
Apps Script allows you to connect Google Forms with Google Sheets through
Forms and
Spreadsheet services. This feature
can automatically create a Google Form based on data in a spreadsheet.
Apps Script also enables you to use triggers, such as
onFormSubmit to perform a specific action after a user responds to the form.
To learn more about connecting Google Sheets to Google Forms, try the Managing
Responses for Google Forms 5-minute quickstart.
Formatting
The Range class has methods like
setBackground(color)
to access and modify the format of a cell or range of cells. The following
example shows how you can set the font style of a range:
function formatMySpreadsheet() {
// Set the font style of the cells in the range of B2:C2 to be italic.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange('B2:C2');
cell.setFontStyle('italic');
}
Data validation
Apps Script lets you access existing data-validation rules in Google Sheets or create new rules. For instance, the following sample shows how to set a data-validation rule that allows only numbers between 1 and 100 on a cell.
function validateMySpreadsheet() {
// Set a rule for the cell B4 to be a number between 1 and 100.
var cell = SpreadsheetApp.getActive().getRange('B4');
var rule = SpreadsheetApp.newDataValidation()
.requireNumberBetween(1, 100)
.setAllowInvalid(false)
.setHelpText('Number must be between 1 and 100.')
.build();
cell.setDataValidation(rule);
}
For more details on working with data-validation rules, see
SpreadsheetApp.newDataValidation(),
DataValidationBuilder,
and Range.setDataValidation(rule)
Charts
Apps Script lets you embed charts in a spreadsheet that represent the data in a
specific range. The following example generates an embedded bar chart, assuming
you have chartable data in cells A1:B15:
function newChart() {
// Generate a chart representing the data in the range of A1:B15.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var chart = sheet.newChart()
.setChartType(Charts.ChartType.BAR)
.addRange(sheet.getRange('A1:B15'))
.setPosition(5, 5, 0, 0)
.build();
sheet.insertChart(chart);
}
To learn more about embedding a chart into your spreadsheet,
see EmbeddedChart and
specific chart builders, such as
EmbeddedPieChartBuilder.
Custom functions in Google Sheets
A custom function is similar to a
built-in spreadsheet function like =SUM(A1:A5) except that you define the
functions’s behavior with Apps Script. For example, you could create a custom
function, in2mm(), that converts a value from inches to millimeters, then use
the formula in your spreadsheet by typing =in2mm(A1) or =in2mm(10) into a
cell.
To learn more about custom functions, try the Menus and Custom Functions 5-minute quickstart, or take a look at the more in-depth guide to custom functions.
Macros
Macros are another way of executing Apps Script code from the Google Sheets UI. Unlike custom functions, you activate them with a keyboard shortcut or through the Google Sheets menu. For more information, see Google Sheets Macros.
Add-ons for Google Sheets
Add-ons are specially packaged Apps Script projects that run inside Google Sheets and can be installed from the Google Sheets add-on store. If you've developed a script for Google Sheets and want to share it with the world, Apps Script lets you publish your script as an add-on so other users can install it from the add-on store.
Triggers
Scripts that are bound to a Google Sheets file
can use simple triggers like the functions
onOpen() and onEdit() to respond automatically when a user who has edit
access to the spreadsheet opens or edits the spreadsheet.
Like simple triggers, installable triggers let Google Sheets run a function automatically when a certain event occurs. Installable triggers, however, offer more flexibility than simple triggers and support the following events: open, edit, change, form submit, and time-driven (clock).