Row & column operations

The Google Sheets API lets you add, 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 Sheets API.

These examples are presented in the form of HTTP requests to be language neutral. To learn how to implement a batch update in different languages using the Google API client libraries, see Update spreadsheets.

In these examples, the placeholders SPREADSHEET_ID and SHEET_ID indicates where you would provide those IDs. You can find the spreadsheet ID in the spreadsheet URL. You can get the sheet ID by using the spreadsheets.get method. The ranges are specified using A1 notation. An example range is Sheet1!A1:D5.

Adjust column width or row height

The following spreadsheets.batchUpdate code sample shows how to use the UpdateDimensionPropertiesRequest to update the width property of column A to 160 pixels. A second request updates the row height property of the first three rows to be 40 pixels. The dimension field determines whether the operation applies to the columns or rows of a sheet.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": SHEET_ID,
          "dimension": "COLUMNS",
          "startIndex": 0,
          "endIndex": 1
        },
        "properties": {
          "pixelSize": 160
        },
        "fields": "pixelSize"
      }
    },
    {
      "updateDimensionProperties": {
        "range": {
          "sheetId": SHEET_ID,
          "dimension": "ROWS",
          "startIndex": 0,
          "endIndex": 3
        },
        "properties": {
          "pixelSize": 40
        },
        "fields": "pixelSize"
      }
    }
  ]
}

Append empty rows or columns

The following spreadsheets.batchUpdate code sample shows how to use the AppendDimensionRequest to append rows and columns. The first request appends three empty rows to the end of a sheet, while the second appends a single empty column. The dimension field determines whether the operation applies to the columns or rows of a sheet.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "appendDimension": {
        "sheetId": SHEET_ID,
        "dimension": "ROWS",
        "length": 3
      }
    },
    {
      "appendDimension": {
        "sheetId": SHEET_ID,
        "dimension": "COLUMNS",
        "length": 1
      }
    }
  ]
}

Automatically resize a column

The following spreadsheets.batchUpdate code sample shows how to use the AutoResizeDimensionsRequest to resize columns A:C, based on the size of the column content. The dimension field indicates that the operation applies to the columns of the sheet.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "autoResizeDimensions": {
        "dimensions": {
          "sheetId": SHEET_ID,
          "dimension": "COLUMNS",
          "startIndex": 0,
          "endIndex": 3
        }
      }
    }
  ]
}

Automatically resize a row

The following spreadsheets.batchUpdate code sample shows how to use the AutoResizeDimensionsRequest to clear the row heights of the first three rows. The row heights then grow dynamically based on the content of the cells in each row. The dimension field indicates that the operation applies to the rows of the sheet.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "autoResizeDimensions": {
        "dimensions": {
          "sheetId": SHEET_ID,
          "dimension": "ROWS",
          "startIndex": 0,
          "endIndex": 3
        }
      }
    }
  ]
}

Delete rows or columns

The following spreadsheets.batchUpdate code sample shows how to use the DeleteDimensionRequest to delete the first three rows in a sheet. A second request deletes columns B:D. The dimension field determines whether the operation applies to the columns or rows of a sheet.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "deleteDimension": {
        "range": {
          "sheetId": SHEET_ID,
          "dimension": "ROWS",
          "startIndex": 0,
          "endIndex": 3
        }
      }
    },
    {
      "deleteDimension": {
        "range": {
          "sheetId": SHEET_ID,
          "dimension": "COLUMNS",
          "startIndex": 1,
          "endIndex": 4
        }
      }
    },
  ],
}

Insert an empty row or column

The following spreadsheets.batchUpdate code sample shows how to use the InsertDimensionRequest to insert columns or rows at a particular index (such as prepending empty rows at the top of a sheet). The first request inserts two empty columns at column C. A second request inserts three empty rows beginning at row 1.

The dimension field determines whether the operation applies to the columns or rows of a sheet.

If true, the inheritFromBefore field tells the Sheets API to give the new columns or rows the same properties as the previous row or column and if false, to inherit from the dimensions after the new columns or rows. inheritFromBefore cannot be true if inserting a row at row 1 or a column at column A.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "insertDimension": {
        "range": {
          "sheetId": SHEET_ID,
          "dimension": "COLUMNS",
          "startIndex": 2,
          "endIndex": 4
        },
        "inheritFromBefore": true
      }
    },
    {
      "insertDimension": {
        "range": {
          "sheetId": SHEET_ID,
          "dimension": "ROWS",
          "startIndex": 0,
          "endIndex": 3
        },
        "inheritFromBefore": false
      }
    },
  ],
}

Move a row or column

The following spreadsheets.batchUpdate code sample shows how to use the MoveDimensionRequest to move column A to the column D position. A second request moves rows 5–10 to the row 20 position.

The dimension field determines whether the operation applies to the columns or rows of a sheet. The destinationIndex field determines where to move the source data using a zero-based start index.

The request protocol is shown below.

POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
  "requests": [
    {
      "moveDimension": {
        "source": {
          "sheetId": SHEET_ID,
          "dimension": "COLUMNS",
          "startIndex": 0,
          "endIndex": 1
        },
        "destinationIndex": 3
      }
    },
    {
      "moveDimension": {
        "source": {
          "sheetId": SHEET_ID,
          "dimension": "ROWS",
          "startIndex": 4,
          "endIndex": 10
        },
        "destinationIndex": 19
      }
    },
  ],
}