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 removes all formatting
supported by the Sheets API from the sheet, 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 a given sheetId from one spreadsheet to another. The targetSpreadsheetId variable in the request body specifies the destination spreadsheet. The copy retains all values, formatting, formulas, and other properties of the original. The title of the copied sheet is set to "Copy of [original sheet title]".
The response to this method call is a SheetProperties
object describing the properties of the newly created sheet copy.
POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/sheets/sheetId:copyTo
{
"destinationSpreadsheetId": targetSpreadsheetId
}
Delete a sheet
The following spreadsheets.batchUpdate request deletes a sheet with a given 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": [
{
"deleteSheet": {
"sheetId": sheetId
}
}
]
}
Determine a sheet ID and other properties
The following spreadsheets.get
request gets property information about a given sheet through a spreadsheetId. This method
is often used to determine the IDs of sheets in a specific
spreadsheet, so additional operations can target those sheets. The fields
query parameter specifies only sheet property data should be returned
(as opposed to cell data or data related to the entire spreadsheet).
The response to this method call is a Spreadsheet
object that contains an array of Sheet
objects each having a SheetProperties
element. If a given response field is set to the default value, it's
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
}
},
...
],
}