The Google Sheets API lets you manipulate data within spreadsheets in variousways. Most functionality that's available to users working with theSheets UI is also possible to do with theSheets API. The examples on this page illustrate how you can achievesome common spreadsheet operations with the Sheets 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.

Apply data validation to a range

The followingspreadsheets.batchUpdatecode sample shows how to use theSetDataValidationRequestto apply a data validation rule, where "value > 5", to every cell in the range A1:D10.

The request protocol is shown below.

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

{"requests":[{"setDataValidation":{"range":{"sheetId":SHEET_ID,"startRowIndex":0,"endRowIndex":10,"startColumnIndex":0,"endColumnIndex":4},"rule":{"condition":{"type":"NUMBER_GREATER","values":[{"userEnteredValue":"5"}]},"inputMessage":"Value must be > 5","strict":true}}}]}

Copy & paste cell formatting

The followingspreadsheets.batchUpdatecode sample shows how to use theCopyPasteRequestto copy the formatting only in the range A1:D10 and paste it to the F1:I10 rangein the same sheet. The method uses thePasteType enumwithPASTE_FORMAT to paste the formatting and data validation only. Theoriginal values in A1:D10 remain unchanged.

The request protocol is shown below.

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

{  "requests": [    {      "copyPaste": {        "source": {          "sheetId":SHEET_ID,          "startRowIndex": 0,          "endRowIndex": 10,          "startColumnIndex": 0,          "endColumnIndex": 4        },        "destination": {          "sheetId":SHEET_ID,          "startRowIndex": 0,          "endRowIndex": 10,          "startColumnIndex": 5,          "endColumnIndex": 9        },        "pasteType": "PASTE_FORMAT",        "pasteOrientation": "NORMAL"      }    }  ]}

Cut & paste cells

The followingspreadsheets.batchUpdatecode sample shows how to use theCutPasteRequest.It cuts the range A1:D10 and uses thePasteType enumwithPASTE_NORMAL to paste its values, formulas, formatting, and merges to theF1:I10 range in the same sheet. The original source range cell contents areremoved.

The request protocol is shown below.

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

{  "requests": [    {      "cutPaste": {        "source": {          "sheetId":SHEET_ID,          "startRowIndex": 0,          "endRowIndex": 10,          "startColumnIndex": 0,          "endColumnIndex": 4        },        "destination": {          "sheetId":SHEET_ID,          "rowIndex": 0,          "columnIndex": 5        },        "pasteType": "PASTE_NORMAL"      }    }  ]}

Repeat a formula over a range

The followingspreadsheets.batchUpdatecode sample shows how to use theRepeatCellRequestto copy the formula=FLOOR(A1*PI()) to the range B1:D10. The formula's rangeautomatically increments for each row and column in the range, starting with theupper left cell. For example, cell B1 has the formula=FLOOR(A1*PI()), whilecell D6 has the formula=FLOOR(C6*PI()).

The request protocol is shown below.

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

{  "requests": [    {      "repeatCell": {        "range": {          "sheetId":SHEET_ID,          "startRowIndex": 0,          "endRowIndex": 10,          "startColumnIndex": 1,          "endColumnIndex": 4        },        "cell": {          "userEnteredValue": {              "formulaValue": "=FLOOR(A1*PI())"          }        },        "fields": "userEnteredValue"      }    }  ]}

Sort a range with multiple sorting specifications

The followingspreadsheets.batchUpdatecode sample shows how to use theSortRangeRequestto sort the range A1:D10, first by column B in ascending order, then by column Cin descending order, then by column D in descending order.

The request protocol is shown below.

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

{"requests":[{"sortRange":{"range":{"sheetId":SHEET_ID,"startRowIndex":0,"endRowIndex":10,"startColumnIndex":0,"endColumnIndex":4},"sortSpecs":[{"dimensionIndex":1,"sortOrder":"ASCENDING"},{"dimensionIndex":2,"sortOrder":"DESCENDING"},{"dimensionIndex":3,"sortOrder":"DESCENDING"}]}}]}