The Google Sheets allows you to read values from cells, ranges, sets of ranges, and entire sheets. The examples on this page illustrate some common read operations with the spreadsheets.values collection. You can also read cell values using the spreadsheets.get method, but usually spreadsheets.values.get or spreadsheets.values.batchGet is easier.
In these examples, the placeholder spreadsheetId indicates where you should provide the spreadsheet ID, which can be discovered from the spreadsheet URL. The ranges to read from are specified using A1 notation in the request URL.
For these examples, assume the spreadsheet being read has the following data in its first sheet ("Sheet1"). To view examples of how to read from other sheets in your spreadsheet, see A1 notation.
| A | B | C | D | |
| 1 | Item | Cost | Stocked | Ship Date |
| 2 | Wheel | $20.50 | 4 | 3/1/2016 |
| 3 | Door | $15 | 2 | 3/15/2016 |
| 4 | Engine | $100 | 1 | 3/20/2016 |
| 5 | Totals | $135.5 | 7 | 3/20/2016 |
Read a single range
The following spreadsheets.values.get request reads the values from the range Sheet1!A1:D5 and returns them in the response. Empty trailing rows and columns are omitted.
The request protocol is shown below. The Reading and Writing Values guide shows how to implement reads in different languages using the Google API client libraries.
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A1:D5
The response to this method call is a
ValueRange
object that describes the range values:
{
"range": "Sheet1!A1:D5",
"majorDimension": "ROWS",
"values": [
["Item", "Cost", "Stocked", "Ship Date"],
["Wheel", "$20.50", "4", "3/1/2016"],
["Door", "$15", "2", "3/15/2016"],
["Engine", "$100", "1", "30/20/2016"],
["Totals", "$135.5", "7", "3/20/2016"]
],
}
Read a single range grouped by column
The following spreadsheets.values.get request reads the values from the range Sheet1!A1:D3 and returns them in the response, but grouped by column. Empty trailing rows and columns are omitted.
The request protocol is shown below. The Reading and Writing Values guide shows how to implement reads in different languages using the Google API client libraries.
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A1:D3?majorDimension=COLUMNS
The response to this method call is a
ValueRange
object that describes the range values:
{
"range": "Sheet1!A1:D3",
"majorDimension": "COLUMNS",
"values": [
["Item", "Wheel", "Door"],
["Cost", "$20.50", "$15"],
["Stocked", "4", "2"],
["Ship Date", "3/1/2016", "3/15/2016"]
],
}
Read a single range with rendering options
The following
spreadsheets.values.get
request reads the values from the range Sheet1!A1:D5 and returns them in
the response, but uses rendering options to choose how that information is
returned. The
ValueRenderOption FORMULA
setting indicates that formulas are to be returned instead of the calculated
value, and the
DateTimeRenderOption
SERIAL_NUMBER setting indicates that dates are to be returned as numbers.
Other settings are possible as well. Empty trailing rows and columns are
omitted.
The request protocol is shown below. The Reading and Writing Values guide shows how to implement reads in different languages using the Google API client libraries.
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A1:D5?
valueRenderOption=FORMULA&dateTimeRenderOption=SERIAL_NUMBER
The response to this method call is a
ValueRange
object that describes the range values:
{
"range": "Sheet1!A1:D5",
"majorDimension": "ROWS",
"values": [
["Item", "Cost", "Stocked", "Ship Date"],
["Wheel", "$20.50", "4", "42430"],
["Door", "$15", "2", "42444"],
["Engine", "$100", "1", "42449"],
["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
],
}
Read multiple ranges
The following
spreadsheets.values.batchGet
request reads values from ranges Sheet1!B:B and Sheet1!D:D and returns them in
the response. The
ValueRenderOption
UNFORMATTED_VALUE setting indicates that values are calculated, but not
formatted in the response. Empty trailing rows and columns are omitted.
The request protocol is shown below. The Reading and Writing Values guide shows how to implement reads in different languages using the Google API client libraries.
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values:batchGet?
ranges=Sheet1!B:B&ranges=Sheet1!D:D&valueRenderOption=UNFORMATTED_VALUE?majorDimension=COLUMNS
The response to this method call consists of an object with the spreadsheet ID
and an array of
ValueRange
objects corresponding to each requested range, listed in the order they were
requested. For example:
{
"spreadsheetId": spreadsheetId,
"valueRanges": [
{
"range": "Sheet1!B1:B1000",
"majorDimension": "COLUMNS",
"values": [
["Cost",20.5,15,100,135.5]
]
},
{
"range": "Sheet1!D1:D1000",
"majorDimension": "COLUMNS",
"values": [
["Ship Date",42430,42444,42449,42449]
]
}
]
}