How can I Group Rows via the Google Sheets API?

Question

I'm looking for a way to create a group of rows via the Google Sheets API - is there a way to do this? I can't see to find an API that will do this, but it seems like it should be a fairly common formatting need.

This feature is supported in the UI by selecting a set of rows, right clicking and the option pops up to create a group, see the screenshot linked below. I'm just looking for a way to do that via an API.

Answer 1

Use this --> Range.shiftColumnGroupDepth

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
var range = sheet.getActiveRange();

// The column grouping depth is increased by 1.
range.shiftColumnGroupDepth(1);

// The column grouping depth is decreased by 1.
range.shiftColumnGroupDepth(-1);

Answer 2

You can accomplish this through version 4 of the Google Sheets API.

You will need to submit an HTTP POST to this endpoint:

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate

You will need to pass a valid JSON request. I created a simple spreadsheet with some rows to group and used this JSON as a test to group rows 14-17:

{
  "requests": [
    {
      "addDimensionGroup": {
        "range": {
          "dimension": "ROWS",
          "sheetId": 0,
          "startIndex": 14,
          "endIndex": 17
        }
      }
    }
  ]
}

Note that the startIndex is the row (or column) number that everything will fold into and will remain visible even if you collapse the group, while endIndex is the last element of the group which will remain hidden when the group is collapsed.

The documentation for this is here. If your window is wide enough it will show a "Try this API" pane on the right side. You can enter the spreadsheetId of your sheet and build up the JSON request body and test it to see it work directly on a sheet - if you have it open in another window you should see the update happen almost immediate after you click the "Execute" button.