Data Operations

The Sheets API allows you to manipulate data within spreadsheets in a number of ways. Most functionality that is available to users working with the Sheets UI at a keyboard is also possible to do with the API. The examples on this page illustrate how some common spreadsheet 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.

Apply data validation to a range

The following spreadsheets.batchUpdate request applies a data validation rule ("value > 5") to the range A1:D10.

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": [
    {
      "setDataValidation": {
        "range": {
          "sheetId": sheetId,
          "startRowIndex": 0,
          "endRowIndex": 10,
          "startColumnIndex": 0,
          "endColumnIndex": 6
        },
        "rule": {
          "condition": {
            "type": "NUMBER_GREATER",
            "values": [
              {
                "userEnteredValue": "5"
              }
            ]
          },
          "inputMessage": "Value must be > 5",
          "strict": true
        }
      }
    }
  ]
}

Copy and paste cell formatting

The following spreadsheets.batchUpdate request copies the formatting in range A1:D10 and pastes it to the F1:I10 range on the same sheet. The original values in A1:I10 remain unchanged.

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": [
    {
      "copyPaste": {
        "source": {
          "sheetId": sheetId,
          "startRowIndex": 0,
          "endRowIndex": 10,
          "startColumnIndex": 0,
          "endColumnIndex": 4
        },
        "destination": {
          "sheetId": sheetId,
          "startRowIndex": 0,
          "endRowIndex": 10,
          "startColumnIndex": 5,
          "endColumnIndex": 9
        },
        "pasteType": "PASTE_FORMAT",
        "pasteOrientation": "NORMAL"
      }
    }
  ]
}

Cut and paste cells

The following spreadsheets.batchUpdate request cuts the range A1:D10 and pastes its data, formats, formulas, and merges to the F1:I10 range on the same sheet. The original source range cell contents are removed.

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": [
    {
      "cutPaste": {
        "source": {
          "sheetId": sheetId,
          "startRowIndex": 0,
          "endRowIndex": 10,
          "startColumnIndex": 0,
          "endColumnIndex": 4
        },
        "destination": {
          "sheetId": sheetId,
          "rowIndex": 0,
          "columnIndex": 5
        },
        "pasteType": "PASTE_NORMAL"
      }
    }
  ]
}

Repeating a formula over a range

The following spreadsheets.batchUpdate request copies the formula =FLOOR(A1*PI()) to the range B1:F10. The formula's ranges automatically increment for each row and column in the range, starting with the upper left cell. For example, cell B1 has the formula =FLOOR(A1*PI()), while cell D6 has the formula =FLOOR(C6*PI()).

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": [
    {
      "repeatCell": {
        "range": {
          "sheetId": sheetId,
          "startRowIndex": 0,
          "endRowIndex": 10,
          "startColumnIndex": 1,
          "endColumnIndex": 6
        },
        "cell": {
          "userEnteredValue": {
              "formulaValue": "=FLOOR(A1*PI())"
          }
        },
        "fields": "userEnteredValue"
      }
    }
  ]
}

Sort a range with multiple sorting specifications

The following spreadsheets.batchUpdate request sorts the range A1:F10, first by column B in ascending order, then by column D in descending order, then by column E in descending order.

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": [
    {
      "sortRange": {
        "range": {
          "sheetId": sheetId,
          "startRowIndex": 0,
          "endRowIndex": 10,
          "startColumnIndex": 0,
          "endColumnIndex": 6
        },
        "sortSpecs": [
          {
            "dimensionIndex": 1,
            "sortOrder": "ASCENDING"
          },
          {
            "dimensionIndex": 3,
            "sortOrder": "DESCENDING"
          },
          {
            "dimensionIndex": 4,
            "sortOrder": "DESCENDING"
          }
        ]
      }
    }
  ]
}