Access and modify spreadsheet groups. Groups are an association between an interval of contiguous rows or columns that can be expanded or collapsed as a unit to hide/show the rows or columns. Each group has a control toggle on the row or column directly before or after the group (depending on settings) that can expand or collapse the group as a whole.
The depth of a group refers to the nested position of the group and how many larger groups contain the group. The collapsed state of a group refers to whether the group should remain collapsed or expanded after a parent group has been expanded. Additionally, at the time that a group is collapsed or expanded, the rows or columns within the group are hidden or set visible, though individual rows or columns can be hidden or set visible irrespective of the collapsed state.
Methods
Method | Return type | Brief description |
---|---|---|
collapse() | Group | Collapses this group. |
expand() | Group | Expands this group. |
getControlIndex() | Integer | Returns the control toggle index of this group. |
getDepth() | Integer | Returns the depth of this group. |
getRange() | Range | Returns the range over which this group exists. |
isCollapsed() | Boolean | Returns true if this group is collapsed. |
remove() | void | Removes this group from the sheet, reducing the group depth of the range by
one. |
Detailed documentation
collapse()
Collapses this group.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; var range = sheet.getRange(2:3); range.shiftRowGroupDepth(1); var group = sheet.getRowGroup(2, 1); // Collapses this group. group.collapse();
Return
Group
— this group, for chaining
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
expand()
Expands this group.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; var range = sheet.getRange(2:3); range.shiftRowGroupDepth(1); var group = sheet.getRowGroup(2, 1); // Expands this group. group.expand();
Return
Group
— this group, for chaining
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
getControlIndex()
Returns the control toggle index of this group. This is the index just before the range when the control toggle is shown before the group, or the index just after the range otherwise.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; sheet.setRowGroupControlAfter(true); var range = sheet.getRange(2:3); range.shiftRowGroupDepth(1); var group = sheet.getRowGroup(2, 1); // Returns 4 var controlIndex = group.getControlIndex();
Return
Integer
— the control toggle index of this group
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
getDepth()
Returns the depth of this group.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; var range = sheet.getRange(2:3); range.shiftRowGroupDepth(1); var group = sheet.getRowGroup(2, 1); // Returns 1 if the group is at depth 1. var depth = group.getDepth();
Return
Integer
— the depth of this group
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
getRange()
Returns the range over which this group exists.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; var range = sheet.getRange(2:3); range.shiftRowGroupDepth(1); var group = sheet.getRowGroup(1, 1); // Returns the range 2:3 if the group is over rows 2:3 range = group.getRange();
Return
Range
— the range over which the group exists
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
isCollapsed()
Returns true
if this group is collapsed.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; var range = sheet.getRange(2:3); range.shiftRowGroupDepth(1); var group = sheet.getRowGroup(2, 1); // Returns true if the group is collapsed. var isCollapsed = group.isCollapsed();
Return
Boolean
— true
if this group is collapsed; returns false
otherwise
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets
remove()
Removes this group from the sheet, reducing the group depth of the range
by
one. This may modify other groups. After calling this, the group object becomes invalid to use.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; var range = sheet.getRange(2:3); range.shiftRowGroupDepth(1); var group = sheet.getRowGroup(2, 1); // Removes this group var range = group.remove();
Authorization
Scripts that use this method require authorization with one or more of the following scopes:
-
https://www.googleapis.com/auth/spreadsheets.currentonly
-
https://www.googleapis.com/auth/spreadsheets