Class Group

Group

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

See also