Sheet Operations

Stay organized with collections Save and categorize content based on your preferences.

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
        }
      },
      ...
  ],
}