Row and Column Operations

The Sheets API allows you to insert, remove, and manipulate rows and columns in sheets. The examples on this page illustrate how some common row and column 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.

Adjust column width or row height

The following spreadsheets.batchUpdate request updates the width of column A to 160 pixels. A second request updates the row height of the first three rows to be 40 pixels.

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": [
    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": sheetId,
          "dimension": "COLUMNS",
          "startIndex": 0,
          "endIndex": 1
        },
        "properties": {
          "pixelSize": 160
        },
        "fields": "pixelSize"
      }
    },
    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": sheetId,
          "dimension": "ROWS",
          "startIndex": 0,
          "endIndex": 3
        },
        "properties": {
          "pixelSize": 40
        },
        "fields": "pixelSize"
      }
    }
  ]
}

Append empty rows or columns

The following spreadsheets.batchUpdate request appends rows and columns. The first request appends three empty rows to the end of the sheet, while the second appends a single empty column.

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": [
    {
      "appendDimension": {
        "sheetId": sheetId,
        "dimension": "ROWS",
        "length": 3
      }
    },
    {
      "appendDimension": {
        "sheetId": sheetId,
        "dimension": "COLUMNS",
        "length": 1
      }
    }
  ]
}

Automatically resize a column

The following spreadsheets.batchUpdate request will resize columns A through C, based on the size of the column content.

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": [
    {
      "autoResizeDimensions": {
        "dimensions": {
          "sheetId": sheetId,
          "dimension": "COLUMNS",
          "startIndex": 0,
          "endIndex": 3
        }
      }
    }
  ]
}

Automatically resize a row

The following spreadsheets.batchUpdate request will clear the row heights of the first three rows. The row heights will then each grow dynamically based on the content of the cells in each row.

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": [
    {
      "autoResizeDimensions": {
        "dimensions": {
          "sheetId": sheetId,
          "dimension": "ROWS",
          "startIndex": 0,
          "endIndex": 3
        }
      }
    }
  ]
}

Delete rows or columns

The following spreadsheets.batchUpdate request deletes the first three rows in the sheet. A second request deletes columns B:D.

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": [
    {
      "deleteDimension": {
        "range": {
          "sheetId": sheetId,
          "dimension": "ROWS",
          "startIndex": 0,
          "endIndex": 3
        }
      }
    },
    {
      "deleteDimension": {
        "range": {
          "sheetId": sheetId,
          "dimension": "COLUMNS",
          "startIndex": 1,
          "endIndex": 4
        }
      }
    },
  ],
}

Insert an empty row or column

The following spreadsheets.batchUpdate request inserts two blank columns at column C. A second request inserts three empty rows at row 1. The inheritFromBefore field, if true, tells the API to give the new columns or rows the same properties as the prior row or column; otherwise the new columns or rows acquire the properties of those that follow them. inheritFromBefore cannot be true if inserting a row at row 1 or a column at column A.

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": [
    {
      "insertDimension": {
        "range": {
          "sheetId": sheetId,
          "dimension": "COLUMNS",
          "startIndex": 2,
          "endIndex": 4
        },
        "inheritFromBefore": true
      }
    },
    {
      "insertDimension": {
        "range": {
          "sheetId": sheetId,
          "dimension": "ROWS",
          "startIndex": 0,
          "endIndex": 3
        },
        "inheritFromBefore": false
      }
    },
  ],
}

Move a row or column

The following spreadsheets.batchUpdate request moves column A to the column D position. A second request moves rows 5 through 10 to the row 20 position.

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": [
    {
      "moveDimension": {
        "source": {
          "sheetId": sheetId,
          "dimension": "COLUMNS",
          "startIndex": 0,
          "endIndex": 1
        },
        "destinationIndex": 3
      }
    },
    {
      "moveDimension": {
        "source": {
          "sheetId": sheetId,
          "dimension": "ROWS",
          "startIndex": 4,
          "endIndex": 10
        },
        "destinationIndex": 19
      }
    },
  ],
}

Send feedback about...

Need help? Visit our support page.