Getting groups of unknown depths - group.getDepth, group.getRowGroup

Question

I would like the GAS code to get the depth of the group that starts at a given Row number.

The dirty way to do this would be to write the depth of the group into a hidden column when I create the group, but is there a way to find the depth of a group at a given row programmatically? the Demo code for get group depth in the documentation is this:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
var range = sheet.getRange(2:3);
range.shiftRowGroupDepth(1);
var group = sheet.getRowGroupAt(2, 1);

// Returns 1 if the group is at depth 1.
var depth = group.getDepth();

But that code required the depth in line 4 to GET the group it is asking the depth for. Which seems to negate any usefulness of the getDepth function.

The ultimate goal is a function that can strip all the existing groups off the page, and another function that can collapse all the groups. But for either of those things I seem to need to know the dept in order to getRowGroup them.

Answer 1

How about using Sheets API for your situation? I think that there are several solutions for your situation. So please think of this as one of them.

When you use this script, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

1. Retrieving depth information of groups

When the depth information of a sheet name of "Sheet1" is retrieved, the script is as follows.

var spreadsheetId = "### spreadsheetId ###";
var ranges = ["Sheet1"]; // Put sheet names.
var res = Sheets.Spreadsheets.get(spreadsheetId, {ranges: ranges, fields: "sheets/rowGroups"});
Logger.log(res)

• If you want to retrieve values from sheets of "Sheet1" and "Sheet2", please put var ranges = ["Sheet1", "Sheet2"].
• If you also want to retrieve the information of columnGroups, please modify fields to sheets(columnGroups,rowGroups).

2. Deleting all groups

For one of your goals, when you want to delete all groups (rowGroups and columnGroups), the script is as follows. In this script, the values retrieved above script are used.

var spreadsheetId = "### spreadsheetId ###";
var ranges = ["Sheet1"];
var res = Sheets.Spreadsheets.get(spreadsheetId, {ranges: ranges, fields: "sheets(columnGroups,properties/sheetId,rowGroups)"});
var reqs = res.sheets.map(function(e) {
  var rows = e.rowGroups.map(function(f) {
    var obj = {deleteDimensionGroup: {range: f.range}};
    obj.deleteDimensionGroup.range.sheetId = e.properties.sheetId;
    return obj;
  });
  var columns = e.columnGroups.map(function(f) {
    var obj = {deleteDimensionGroup: {range: f.range}};
    obj.deleteDimensionGroup.range.sheetId = e.properties.sheetId;
    return obj;
  });
  return rows.concat(columns);
});
Sheets.Spreadsheets.batchUpdate({requests: reqs}, spreadsheetId);

3. Collapsing all groups

For another one of your goals, when you want to collapse all groups, the script is as follows. This sample script collapses all row groups in "Sheet1".

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
sheet.collapseAllRowGroups();
// sheet.collapseAllColumnGroups(); // If you want to collapse all column groups, please use this.

• collapseAllRowGroups() was added at April 23, 2018.
• Also there are the methods of expandAllRowGroups() and expandAllColumnGroups().

References:

• spreadsheets.get
• collapseAllRowGroups()
• collapseAllColumnGroups

If this was not what you want, I'm sorry.

Answer 2

Another way to delete groups without having to enable advanced sheets api:

// row is known, depth is not
function deleteAll(sheet, row) {

  // starting at greatest number of depth levels
  var depth = 8; 

  // loop through all levels until depth is zero
  while ( depth >= 1 ) {

    // handle execption errors thrown when a group doesn't exist
    try {

      var group = sheet.getRowGroup(row, depth);  
      group.remove();

    } catch(error) {}

    // count down to remove groups from right to left
    depth--;
  }

}