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, see Updatespreadsheets.
In these examples, the placeholders SPREADSHEET_ID and SHEET_IDindicates where you would provide those IDs. You can find the spreadsheetID in the spreadsheet URL. You can getthe sheet ID by using thespreadsheets.get method. Theranges are specified using A1 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 the fields 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 by SHEET_IDfrom one spreadsheet to another spreadsheet.
The TARGET_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 by SHEET_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 get sheet property informationfrom a spreadsheet, specified by SHEET_IDand SPREADSHEET_ID. This method is often used to determine themetadata of sheets within a specific spreadsheet, so additional operations cantarget those sheets. The fields 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.propertiesThe 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 } }, ... ],} 