Spreadsheets can have multiple sheets, with each sheet having any number of rows or columns. A cell is a location at the intersection of a particular row and column, and may contain a data value. The Google Sheets API provides the spreadsheets.values collection to enable the simple reading and writing of values.
This page describes the basics of using the spreadsheets.values collection. If you need to update formatting or other properties in a sheet, you will need to use the spreadsheets collection, which is described in Updating Spreadsheets.
Methods
The spreadsheets.values collection provides the following methods for reading and writing values, each with a specific task in mind:
Range Access | Reading | Writing |
---|---|---|
Single range | spreadsheets.values.get | spreadsheets.values.update |
Multiple ranges | spreadsheets.values.batchGet | spreadsheets.values.batchUpdate |
Appending | spreadsheets.values.append |
In general, it is a good idea to combine multiple reads or updates with the batchGet and batchUpdate methods (respectively), as this will improve efficiency.
You can find examples of each of these methods in the Basic Reading and Basic Writing samples pages.
Reading
To read data from a sheet, you will need the spreadsheet ID and the range(s) A1 notation. For more information about spreadsheet IDs and A1 notation, see Key Concepts in the Google Sheets API. The format of the output is controlled by three optional parameters:
Format Parameter | Default Value |
---|---|
majorDimension | ROWS |
valueRenderOption | FORMATTED_VALUE |
dateTimeRenderOption | SERIAL_NUMBER |
Note that dateTimeRenderOption
is only used if the valueRenderOption
is not FORMATTED_VALUE
.
The singular and batch get methods are described below. For examples of basic read operations, see the Basic Reading recipes page.
Reading a single range
To read a single range of data out of a spreadsheet, use a spreadsheets.values.get request:
Apps Script
Java
JavaScript
Node.js
PHP
Python
Ruby
The response to this request is returned as a ValueRange object.
Reading multiple ranges
To read multiple discontinuous ranges, use a spreadsheets.values.batchGet, which lets you specify any number of ranges to retrieve:
Apps Script
Java
JavaScript
Node.js
PHP
Python
Ruby
The response to this request is a BatchGetValueResponse, object which contains the spreadsheet ID and a list of ValueRange objects.
Writing
To write to a sheet, you will need the spreadsheet ID, the range(s) in A1 notation, and the data you wish to write arranged in an appropriate request body object. For more information about spreadsheet IDs and A1 notation, see Key Concepts in the Google Sheets API.
Updates require a valid ValueInputOption parameter (for singular updates, this is a required query parameter; for batch updates, this parameter is required in the request body). The ValueInputOption controls whether input strings are parsed or not, as described in the following table:
ValueInputOption | Description |
---|---|
RAW |
The input is not parsed and is simply inserted as a string, so the input "=1+2" places the string "=1+2" in the cell, not a formula. (Non-string values like booleans or numbers are always handled as RAW .) |
USER_ENTERED |
The input is parsed exactly as if it were entered into the Google Sheets UI, so "Mar 1 2016" becomes a date, and "=1+2" becomes a formula. Formats may also be inferred, so "$100.15" becomes a number with currency formatting. |
The singular and batch update methods are described below. For examples of basic write operations, see the Basic Writing recipes page.
Writing to a single range
To write data to a single range, use a spreadsheets.values.update request:
Apps Script
Java
JavaScript
Node.js
PHP
Python
Ruby
The body of the update request must be a
ValueRange
object, though the only required field is values
. If range
is specified, it
must match the range in the URL. In the ValueRange, you can optionally specify
its majorDimension.
By default, ROWS is used. If COLUMNS is specified, each inner array is
written to a column instead of a row.
When updating, values with no data are skipped. To clear data, use an empty string ("").
Writing multiple ranges
If you want to write multiple discontinuous ranges, you can use a spreadsheets.values.batchUpdate request:
Apps Script
Java
JavaScript
Node.js
PHP
Python
Ruby
The body of the batchUpdate request must be a
BatchUpdateValuesRequest
object, which contains a ValueInputOption and a list of
ValueRange
objects (one for each written range). Each ValueRange object
specifies its own range
, majorDimension
, and the data to input.
Appending values
To append data after a table of data in a sheet, use a spreadsheets.values.append request:
Apps Script
Java
JavaScript
Node.js
PHP
Python
Ruby
The body of the update request must be a
ValueRange
object, though the only required field is values
. If range
is specified, it
must match the range in the URL. In the ValueRange, you can optionally specify
its majorDimension.
By default, ROWS is used. If COLUMNS is specified, each inner array is
written to a column instead of a row.
The input range is used to search for existing data and find a "table" within
that range. Values are appended to the next row of the table, starting with
the first column of the table. For example, consider a sheet Sheet1
that looks
like:
A | B | C | D | E | |
1 | x | y | z | ||
2 | x | y | z | ||
3 | |||||
4 | x | y | |||
5 | y | z | |||
6 | x | y | z | ||
7 |
There are two tables in the sheet: A1:C2
, and B4:D6
. Appended values
would begin at B7
for all the following range
inputs:
Sheet1
, because it will examine all the data in the sheet, determine that the table atB4:D6
is the last table.B4
orC5:D5
, because they're both in theB4:D6
table.B2:D4
, because the last table in the range is theB4:D6
table (despite it also containing theA1:C2
table).A3:G10
, because the last table in the range is theB4:D6
table (despite starting before and ending after it).
The following range
inputs would not start writing at B7
:
A1
would start writing atA3
, because that's in theA1:C2
table.E4
would start writing atE4
, because it's not in any table. (A4
would also start writing atA4
for the same reasons.)
Additionally, you can choose if you want to overwrite existing
data after a table or insert new rows for the new data. By default, the input
overwrites data after the table. To write the new data into new rows,
specify insertDataOption=INSERT_ROWS
.
To learn more about cell and row limits in Google Sheets, see Files you can store in Google Drive.