The Google Sheets API lets you create, clear, copy, and delete sheets, and alsocontrol their properties. The examples on this page illustrate how you canachieve some common Sheets operations with theSheets API.

These examples are presented in the form of HTTP requests to be languageneutral. To learn how to implement a batch update in different languages usingthe Google API client libraries, seeUpdatespreadsheets.

In these examples, the placeholdersSPREADSHEET_ID andSHEET_IDindicates where you would provide those IDs. You can find thespreadsheetID in the spreadsheet URL. You can getthesheet ID by using thespreadsheets.get method. Theranges are specified usingA1 notation. Anexample range is Sheet1!A1:D5.

Add a sheet

The followingspreadsheets.batchUpdatecode sample shows how to use theAddSheetRequestto add a sheet to a spreadsheet, while also setting the title, grid size, andtab color.

The response consists of aAddSheetResponse,which contains an object with the created sheet's properties (such as itsSHEET_ID).

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID: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 followingspreadsheets.batchUpdatecode sample shows how to use theUpdateCellsRequestto remove all values from a sheet while leaving the formatting unaltered.

Specifying theuserEnteredValue fieldwithout a corresponding value is interpreted as an instruction to clear valuesin the range. This setting can be used with other fields as well. For example,changing thefields value touserEnteredFormat removesall formatting supported by the Sheets APIfrom the sheet, but leaves the cell values unaltered.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate

{  "requests": [    {      "updateCells": {        "range": {          "sheetId":SHEET_ID        },        "fields": "userEnteredValue"      }    }  ]}

Copy a sheet from one spreadsheet to another

The followingspreadsheet.sheets.copyTocode sample shows how to copy a single sheet specified bySHEET_IDfrom one spreadsheet to another spreadsheet.

TheTARGET_SPREADSHEET_ID variable in the request body specifies thedestination spreadsheet. The copy retains all values, formatting, formulas, andother properties of the original. The title of the copied sheet is set to "Copyof [original sheet title]".

The response consists of aSheetPropertiesobject describing the properties of the created sheet.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/sheets/SHEET_ID:copyTo

{  "destinationSpreadsheetId": "TARGET_SPREADSHEET_ID"}

Delete a sheet

The followingspreadsheets.batchUpdatecode sample shows how to use theDeleteSheetRequestto delete a sheet specified bySHEET_ID.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate

{  "requests": [    {      "deleteSheet": {        "sheetId":SHEET_ID      }    }  ]}

Read sheet data

The followingspreadsheets.get code sampleshows how to getsheet property informationfrom aspreadsheet, specified bySHEET_IDandSPREADSHEET_ID. This method is often used to determine themetadata of sheets within a specific spreadsheet, so additional operations cantarget those sheets. Thefields query parameter specifies only sheet propertydata should be returned (as opposed to cell value data or data related to theentire spreadsheet).

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?&fields=sheets.properties

The response consists of aSpreadsheetresource, which contains aSheet object withSheetPropertieselements. If a given response field is set to the default value, it's omittedfrom the response.

{  "sheets": [    {      "properties": {        "sheetId":SHEET_ID,        "title": "Sheet1",        "index": 0,        "sheetType": "GRID",        "gridProperties": {          "rowCount": 100,          "columnCount": 20,          "frozenRowCount": 1        }        "tabColor": {          "blue": 1.0        }      },      ...  ],}