Access and modify pivot table breakout groups.
Methods
Method | Return type | Brief description |
---|---|---|
add | Pivot | Adds a manual grouping rule for this pivot group. |
are | Boolean | Returns whether labels are displayed as repeated. |
clear | Pivot | Removes any grouping rules from this pivot group. |
clear | Pivot | Removes any sorting applied to this group. |
get | Date | Returns the date-time grouping rule on the pivot group, or null if no date-time
grouping rule is set. |
get | Dimension | Returns whether this is a row or column group. |
get | Pivot | Returns the pivot group limit on the pivot group. |
get | Integer | Returns the index of this pivot group in the current group order. |
get | Pivot | Returns the Pivot which this grouping belongs to. |
get | Integer | Returns the number of the source data column this group summarizes. |
get | Data | Returns the data source column the pivot group operates on. |
hide | Pivot | Hides repeated labels for this grouping. |
is | Boolean | Returns true if the sort is ascending, returns false if the sort order is
descending. |
move | Pivot | Moves this group to the specified position in the current list of row or column groups. |
remove() | void | Removes this pivot group from the table. |
remove | Pivot | Removes the manual grouping rule with the specified group . |
reset | Pivot | Resets the display name of this group in the pivot table to its default value. |
set | Pivot | Sets the date-time grouping rule on the pivot group. |
set | Pivot | Sets the display name of this group in the pivot table. |
set | Pivot | Sets the pivot group limit on the pivot group. |
set | Pivot | Sets a histogram grouping rule for this pivot group. |
show | Pivot | When there is more than one row or column grouping, this method displays this grouping's label for each entry of the subsequent grouping. |
show | Pivot | Sets whether to show total values for this pivot group in the table. |
sort | Pivot | Sets the sort order to be ascending. |
sort | Pivot | Sorts this group by the specified Pivot for the values from the opposite . |
sort | Pivot | Sets the sort order to be descending. |
totals | Boolean | Returns whether total values are currently shown for this pivot group. |
Detailed documentation
addManualGroupingRule(groupName, groupMembers)
Adds a manual grouping rule for this pivot group.
Parameters
Name | Type | Description |
---|---|---|
group | String | The name of this grouping rule. |
group | Object[] | The values that are included in this grouping rule. |
Return
Pivot
— the pivot 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
areLabelsRepeated()
Returns whether labels are displayed as repeated.
Return
Boolean
— true
if labels are repeated; otherwise returns false
.
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
clearGroupingRule()
Removes any grouping rules from this pivot group.
Return
Pivot
— the pivot 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
clearSort()
Removes any sorting applied to this group.
Return
Pivot
— the pivot 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
getDateTimeGroupingRule()
Returns the date-time grouping rule on the pivot group, or null
if no date-time
grouping rule is set.
Return
Date
— The date-time grouping rule.
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
getDimension()
Returns whether this is a row or column group.
Return
Dimension
— the dimension representing this group's type
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
getGroupLimit()
Returns the pivot group limit on the pivot group. Returns null
if no pivot group limit
is set.
Return
Pivot
— The pivot group limit.
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
getIndex()
Returns the index of this pivot group in the current group order.
Return
Integer
— the pivot group's index
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
getPivotTable()
Returns the Pivot
which this grouping belongs to.
Return
Pivot
— the pivot table this group belongs to.
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
getSourceDataColumn()
Returns the number of the source data column this group summarizes. This index is 1-based, if
this group summarizes source data in column "A" of the spreadsheet this method returns 1
.
Return
Integer
— the source data column number
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
getSourceDataSourceColumn()
Returns the data source column the pivot group operates on. Returns null
if the pivot
table is not a {DataSourcePivotTableApi}.
Return
Data
— The data source column the pivot group operates on.
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
hideRepeatedLabels()
Hides repeated labels for this grouping. If labels are already hidden this results in a no-op. If this method is called before there are multiple row or column groupings, when an additional grouping is added repeated labels are hidden.
Return
Pivot
— the pivot 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
isSortAscending()
Returns true
if the sort is ascending, returns false
if the sort order is
descending.
Return
Boolean
— true
if the sort order is ascending.
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
moveToIndex(index)
Moves this group to the specified position in the current list of row or column groups. These
indices are 0-based. For example, if this group should be moved to the first position this
method should be called with 0
.
Parameters
Name | Type | Description |
---|---|---|
index | Integer | The index to move this grouping to. |
Return
Pivot
— the pivot 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
remove()
Removes this pivot group from the table.
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
removeManualGroupingRule(groupName)
Removes the manual grouping rule with the specified group
.
Parameters
Name | Type | Description |
---|---|---|
group | String | The name of the grouping rule to remove. |
Return
Pivot
— the pivot 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
resetDisplayName()
Resets the display name of this group in the pivot table to its default value.
Return
Pivot
— the pivot 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
setDateTimeGroupingRule(dateTimeGroupingRuleType)
Sets the date-time grouping rule on the pivot group.
To remove the rule, use clear
.
Parameters
Name | Type | Description |
---|---|---|
date | Date | The rule type to set. |
Return
Pivot
— The pivot 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
setDisplayName(name)
Sets the display name of this group in the pivot table.
Parameters
Name | Type | Description |
---|---|---|
name | String | The display name to set. |
Return
Pivot
— the pivot 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
setGroupLimit(countLimit)
Sets the pivot group limit on the pivot group. The operation is only supported for Data
.
Parameters
Name | Type | Description |
---|---|---|
count | Integer | The count limit of rows or columns to set. Must be positive. |
Return
Pivot
— The pivot 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
setHistogramGroupingRule(minValue, maxValue, intervalSize)
Sets a histogram grouping rule for this pivot group. A histogram rule organizes values in a
source data column into buckets of a constant size. All values from min
to max
are placed into groups of size interval
. All values below min
are placed into one bucket, as are all values greater than max
.
Parameters
Name | Type | Description |
---|---|---|
min | Integer | The minimum value for items to be placed into buckets. Values less than this are combined into a single bucket. |
max | Integer | The maximum value for items to be placed into buckets. Values greater than this are combined into a single bucket. |
interval | Integer |
Return
Pivot
— the pivot 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
showRepeatedLabels()
When there is more than one row or column grouping, this method displays this grouping's label for each entry of the subsequent grouping. If labels are already repeated this results in a no-op. If this method is called before there are multiple row or column groupings, when an additional grouping is added repeated labels are shown.
Return
Pivot
— the pivot 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
showTotals(showTotals)
Sets whether to show total values for this pivot group in the table.
Parameters
Name | Type | Description |
---|---|---|
show | Boolean | Whether to show totals or not. |
Return
Pivot
— the pivot 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
sortAscending()
Sets the sort order to be ascending.
Return
Pivot
— the pivot 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
sortBy(value, oppositeGroupValues)
Sorts this group by the specified Pivot
for the values from the opposite
.
// Sorts the item group by the "SUM of Quantity" pivot value for the specified // salespersons. const sheet = SpreadsheetApp.getActiveSheet(); const pivotTable = sheet.getPivotTables()[0]; const itemGroup = pivotTable.getRowGroups()[0]; const sumQuantityValue = pivotTable.getPivotValues()[0]; itemGroup.sortBy(sumQuantityValue, ['Beth', 'Amir', 'Devyn']);
Parameters
Name | Type | Description |
---|---|---|
value | Pivot | The pivot value to sort by. |
opposite | Object[] | The values of an opposite pivot group (a column group if sorting a row group, or a row group if sorting a column group) that are used to sort. The order of these values determines precedence for tie breaking. |
Return
Pivot
— the pivot 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
sortDescending()
Sets the sort order to be descending.
Return
Pivot
— the pivot 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
totalsAreShown()
Returns whether total values are currently shown for this pivot group.
Return
Boolean
— true
if total values are displayed for this pivot group; otherwise returns
false
.
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