Basic Reading

The Sheets API allows you to read values from cells, ranges, sets of ranges and entire sheets. The examples on this page illustrate how some common read operations can be achieved with the spreadsheets.values collection of this API. You can also read cell values using the spreadsheets.get method, but in most cases using spreadsheets.values.get or spreadsheets.values.batchGet is easier to use.

In these examples, the placeholder spreadsheetId is used to indicate where you would 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"):

	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 stored in 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 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 stored in the range Sheet1!A1:D3 and returns them in the response, but organized to be 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 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 stored in 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 from the response.

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 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 the ranges Sheet1!B:B, and Sheet1!D:D. The ValueRenderOption UNFORMATTED_VALUE setting indicates that values will be calculated, but not formatted in the response. Empty trailing rows and columns are omitted from the response.

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_VALUES?majorDimension=COLUMNS

The response 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]
      ]
    }
  ]
}