Introduction to the Google Sheets API

The Google Sheets API lets you read and modify any aspect of a spreadsheet. Spreadsheets have many settings that let you craft beautiful and functional sheets, so the API has many settings too. The API offers two main ways to interact with the spreadsheet:

Both of these collections are straightforward to use, although the spreadsheets collection provides many more options. For more about these collections and their use, see the reference documentation links above or the following developer guides:

Common terms

This section explains some of the terms you'll encounter when reading about the Sheets API.

Spreadsheet ID

Every API method requires a spreadsheetId parameter which is used to identify which spreadsheet is to be accessed or altered. This ID is the value between the "/d/" and the "/edit" in the URL of your spreadsheet. For example, consider the following URL that references a Google Sheets spreadsheet:

https://docs.google.com/spreadsheets/d/1qpyC0XzvTcKT6EISywvqESX3A0MwQoFDE8p-Bll4hps/edit#gid=0

The ID of this spreadsheet is 1qpyC0XzvTcKT6EISywvqESX3A0MwQoFDE8p-Bll4hps.

The spreadsheet ID is a string containing letters, numbers, and some special characters. The following regular expression can be used to extract the spreadsheet ID from a Google Sheets URL:

/spreadsheets/d/([a-zA-Z0-9-_]+)

If you're familiar with the Drive API, the spreadsheet_id corresponds to the ID of the File resource.

Sheet ID

Individual sheets in a spreadsheet have titles (which must be unique) and IDs. The sheetId is used frequently in the Sheets API to specify which sheet is being read or updated. In the Sheets UI, you can find the sheetId of the open sheet in the spreadsheet URL, as the value of the gid parameter. The following shows the structure of the URL and where sheetId can be found:

https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId

The sheet ID is numeric, and the following regular expression can be used to extract it from a Google Sheets URL:

[#&]gid=([0-9]+)

You can also fetch the IDs of sheets using the API — see the Determine sheet ID and other properties sample.

A1 notation

Some API methods require a range in A1 notation. This is a string like Sheet1!A1:B2, that refers to a group of cells in the spreadsheet, and is typically used in formulas. For example, valid ranges are:

  • Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1.
  • Sheet1!A:A refers to all the cells in the first column of Sheet1.
  • Sheet1!1:2 refers to the all the cells in the first two rows of Sheet1.
  • Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.
  • A1:B2 refers to the first two cells in the top two rows of the first visible sheet.
  • Sheet1 refers to all the cells in Sheet1.

Named ranges are also supported. When a named range conflicts with a sheet's name, the named range is preferred.

If the sheet name has spaces or starts with a bracket, surround the sheet name with single quotes ('), e.g 'Sheet One'!A1:B2. For simplicity, it is safe to always surround the sheet name with single quotes.

Date/Time serial numbers

Google Sheets, like most other spreadsheet applications, treats date/time values as decimal values. This lets you perform arithmetic on them in formulas, so you can increment days or weeks, add or subtract two date/times, and perform other similar operations.

Google Sheets uses a form of epoch date that is commonly used in spreadsheets. The whole number portion of the value (left of the decimal) counts the days since December 30th 1899. The fractional portion (right of the decimal) counts the time as a fraction of one day. For example, January 1st 1900 at noon would be 2.5, 2 because it's two days after December 30th, 1899, and .5 because noon is half a day. February 1st 1900 at 3pm would be 33.625.

Note that Google Sheets correctly treats the year 1900 as a common year, not a leap year.

Partial responses

Spreadsheets are big, and often you don't need every part of the spreadsheet. You can limit what's returned in a Google Sheets API response, using the fields URL parameter. This is especially useful in the spreadsheets.get method. For best performance, explicitly list only the fields you need in the reply. The format of the fields parameter is the same as the JSON encoding of a FieldMask. In a nutshell, multiple different fields are comma separated, and subfields are dot-separated. For convenience, multiple subfields from the same type can be listed within parentheses.

For example, to retrieve the spreadsheet's title, the sheet's properties, and the value and format of range A1:C10. you could use the following request:

GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId?ranges=A1:C10&fields=properties.title,sheets(sheetProperties,data.rowData.values(effectiveValue,effectiveFormat))

Send feedback about...