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 might contain a data value. The
Google Sheets API provides the
spreadsheets.values
resource to enable the reading and writing of values.
This page describes the basics of using the spreadsheets.values
resource. If
you need to insert rows or update the formatting and other properties in a
sheet, you must use the
spreadsheets.batchUpdate
method described in Update spreadsheets.
Methods
The spreadsheets.values
resource
provides the following methods for reading and writing values, each for a
specific task:
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's a good idea to combine multiple reads or updates with the
batchGet
and batchUpdate
methods (respectively), as this improves
efficiency.
You can find examples of each of these methods on the Basic reading and Basic writing samples pages. To see all samples, refer to the samples overview page.
Read
To read data values from a sheet, you need the spreadsheet ID and the A1
notation for the range. Specifying the range without the sheet ID (A1:B2
)
means that the request executes on the first sheet in the spreadsheet. For more
information about spreadsheet IDs and A1 notation, see Google Sheets API
Overview.
Several optional query parameters control the format of the output:
Format Parameter | Default Value |
---|---|
majorDimension |
ROWS |
valueRenderOption |
FORMATTED_VALUE |
dateTimeRenderOption |
SERIAL_NUMBER |
Note that you should only use dateTimeRenderOption
if the valueRenderOption
isn't FORMATTED_VALUE
.
There's no explicit limit to the amount of data returned. Errors return no data. Empty trailing rows and columns are omitted.
The singular and batch get methods are described below. For samples of basic read operations, see Basic reading.
Read a single range
To read a single range of values from 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.
Read multiple ranges
To read multiple, discontinuous ranges of values from a spreadsheet, use a
spreadsheets.values.batchGet
request that lets you specify several ranges to retrieve:
Apps Script
Java
JavaScript
Node.js
PHP
Python
Ruby
The response to this request is returned as a
BatchGetValuesResponse
object that contains the spreadsheetId
and a list of
ValueRange
objects.
Write
To write to a sheet, you need the spreadsheet ID, the range of cells in A1 notation, and the data you wish to write within an appropriate request body object. For more information about spreadsheet IDs and A1 notation, see Google Sheets API Overview.
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
how input data should be interpreted and whether input strings are parsed or
not, as described in the following table:
ValueInputOption |
Description |
---|---|
RAW |
The input is not parsed and is inserted as a string. For example, the input "=1+2" places the string, not the formula, "=1+2" in the cell. (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 Sheets UI. For example, "Mar 1 2016" becomes a date, and "=1+2" becomes a formula. Formats can also be inferred, so "$100.15" becomes a number with currency formatting. |
The singular and batch update methods are described below. For samples of basic write operations, see Basic writing.
Write 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 ("").
Write 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 batch update 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 input data.
Append 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 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 2 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 and 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 wouldn't 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, use the
InsertDataOption
and specify insertDataOption=INSERT_ROWS
.
To learn more about cell and row limits in Sheets, see Files you can store in Google Drive.