The Sheets API allows you to create sheets, delete sheets, and control their properties. The examples on this page illustrate how some common sheet operations can be achieved with the API.
In these examples, the placeholders spreadsheetId and sheetId are used to indicate where you would provide those IDs. The spreadsheet ID can be discovered from the spreadsheet URL; the sheet ID can be obtained from the spreadsheet.get method.
Add a sheet
The following spreadsheets.batchUpdate request adds a sheet to a spreadsheet, while also setting the title, size, and tab color. This request returns an AddSheetResponse, consisting of an object with the created sheet's properties (such as its sheetId).
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"addSheet": {
"properties": {
"title": "Deposits",
"gridProperties": {
"rowCount": 20,
"columnCount": 12
},
"tabColor": {
"red": 1.0,
"green": 0.3,
"blue": 0.4
}
}
}
}
]
}
Clear a sheet of all values while preserving formats
The following spreadsheets.batchUpdate
request removes all values from a sheet while leaving any formatting unaltered.
Specifying userEnteredValue in fields without providing a corresponding
value is interpreted as an instruction to clear values in the range. This can
be used with other fields as well. For example, changing the fields value to
userEnteredFormat and making the request clears the sheet of all formatting,
but leaves the cell values untouched.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"updateCells": {
"range": {
"sheetId": sheetId
},
"fields": "userEnteredValue"
}
}
]
}
Copy a sheet from one spreadsheet to another
The following spreadsheet.sheets.copyTo request copies a sheet with the given sheetId from one spreadsheet to another; the destination spreadsheet is specified by the targetSpreadsheetId variable in the request body. The copy retains all values, formatting, formulas and other properties of the original. The title of the sheet copy is set to be "Copy of [original sheet title]".
The response to this method call is a SheetProperties
object that describes the properties of the newly created sheet copy.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/sheets/api/sheetId:copyTo
{
"destinationSpreadsheetId": targetSpreadsheetId
}
Delete a sheet
The following spreadsheets.batchUpdate request deletes the sheet with the given ID.
The request protocol is shown below. The Updating Spreadsheets guide shows how to implement a batch update in different languages using the Google API client libraries.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate
{
"requests": [
{
"deleteSheet": {
"sheetId": sheetId
}
}
]
}
Determine sheet ID and other properties
The following spreadsheets.get
request gets property information about a given sheet. This method
is often used to determine the IDs of sheets in a specific
spreadsheet, so that additional operations can target those sheets. The fields
query parameter specifies that only the sheet property data should be returned
(as opposed to cell data and data related to the entire spreadsheet).
The response to this method call is a Spreadsheet
object, which contains an array of Sheet
objects each having a SheetProperties
element. If a given response field is currently set to the default value, it is
omitted from the response.
GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId?&fields=sheets.properties
{
"sheets": [
{
"properties": {
"sheetId": 867266606,
"title": "Sheet1",
"index": 0,
"sheetType": "GRID",
"gridProperties": {
"rowCount": 100,
"columnCount": 20,
"frozenRowCount": 1
}
"tabColor": {
"blue": 1.0
}
},
...
],
}