Sheet Operations

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