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.

That said, the Apps Script Spreadsheet Service has functionality related to [`Group`](https://developers.google.com/apps-script/reference/spreadsheet/group)s. See https://developers.google.com/apps-script/reference/spreadsheet/range#shiftColumnGroupDepth(Integer) and https://developers.google.com/apps-script/reference/spreadsheet/range#shiftRowGroupDepth(Integer) You can call Apps Scripts externally with the [Apps Script API](https://developers.google.com/apps-script/api/how-tos/execute) — tehhowch, May 08 '18 at 16:53
try this: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#DimensionGroup — jason, Jun 25 '18 at 02:22

score 8 · Answer 1 · answered Oct 03 '18 at 22:07

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);

score 4 · Answer 2 · answered Jul 12 '18 at 21:47

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.

Cooper · Answer 3 · 2019-11-01T19:14:30.923

Creating Column and Row Groups

Column Groups

function createSomeColumGroups() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("Sheet189");//this is used in the request
  var resource='{"requests": [';
  for(var i=1;i<25;i+=3) {//start at column 1 and ends at 24 making them in groups of 3
    if(i>1){resource+=', ';}
    resource+=Utilities.formatString('{"addDimensionGroup": {"range": {"dimension": "COLUMNS","sheetId": %s,"startIndex": %s ,"endIndex": %s}}}',sh.getSheetId(),i,i+2);
  }
  resource+=']}';
  Logger.log(resource);
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
}

Row Groups

function createSomeRowGroups() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("Sheet189");
  var resource='{"requests": [';
  for(var i=1;i<25;i+=3) {
    if(i>1){resource+=', ';}
    resource+=Utilities.formatString('{"addDimensionGroup": {"range": {"dimension": "ROWS","sheetId": %s,"startIndex": %s ,"endIndex": %s}}}',sh.getSheetId(),i,i+2);
  }
  resource+=']}';
  Logger.log(resource);
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());
}

Don't forget to go the Advanced Google Services and enable Sheets API version 4 and you will also have to enable on the Google Cloud Platform

How can I Group Rows via the Google Sheets API?

3 Answers3