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 was not developed by and is not associated with Google.)

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
Tools > Script editor 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.

To view the data that has been logged, choose View > Logs from the script
editor. (If you don't remember how to create and run a script, see the
5-minute quickstart guide for
Macros, Menus, and Custom Functions).

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.

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 or
UI 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:

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);
}

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.

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).

Known issues

Even though Apps Script gives you lots of control over Google Sheets, it can't
control every aspect of Sheets. The list of
known issues in the new Google Sheets
identifies a few changes since we introducted a newer version of Sheets in
early 2014.