Access and modify pivot tables.
Methods
| Method | Return type | Brief description |
|---|---|---|
addCalculatedPivotValue(name, formula) | PivotValue | Creates a new pivot value in the pivot table calculated from the specified formula with
the specified name. |
addColumnGroup(sourceDataColumn) | PivotGroup | Defines a new pivot column grouping in the pivot table. |
addFilter(sourceDataColumn, filterCriteria) | PivotFilter | Creates a new pivot filter for the pivot table. |
addPivotValue(sourceDataColumn, summarizeFunction) | PivotValue | Defines a new pivot value in the pivot table with the specified summarizeFunction. |
addRowGroup(sourceDataColumn) | PivotGroup | Defines a new pivot row grouping in the pivot table. |
getAnchorCell() | Range | Returns the Range representing the cell where this pivot table is anchored. |
getColumnGroups() | PivotGroup[] | Returns an ordered list of the column groups in this pivot table. |
getFilters() | PivotFilter[] | Returns an ordered list of the filters in this pivot table. |
getPivotValues() | PivotValue[] | Returns an ordered list of the pivot values in this pivot table. |
getRowGroups() | PivotGroup[] | Returns an ordered list of the row groups in this pivot table. |
getValuesDisplayOrientation() | Dimension | Returns whether values are displayed as rows or columns. |
remove() | void | Deletes this pivot table. |
setValuesDisplayOrientation(dimension) | PivotTable | Sets the layout of this pivot table to display values as columns or rows. |
Detailed documentation
addCalculatedPivotValue(name, formula)
Creates a new pivot value in the pivot table calculated from the specified formula with
the specified name.
Parameters
| Name | Type | Description |
|---|---|---|
name | String | The name for this calculated pivot value. |
formula | String | The formula used to calculate this value. |
Return
PivotValue — the newly created PivotValue
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
addColumnGroup(sourceDataColumn)
Defines a new pivot column grouping in the pivot table. The specified sourceDataColumn
indicates the column in the source data this grouping is based on.
Parameters
| Name | Type | Description |
|---|---|---|
sourceDataColumn | Integer | The number of the column this group summarizes. This index represents
the absolute number of the column in the spreadsheet; 1 representing column "A,"
2 representing column B, etc. |
Return
PivotGroup — the newly created PivotGroup
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
addFilter(sourceDataColumn, filterCriteria)
Creates a new pivot filter for the pivot table. The specified sourceDataColumn
indicates the column in the source data this filter operates on.
Parameters
| Name | Type | Description |
|---|---|---|
sourceDataColumn | Integer | The number of the column this group summarizes. This index represents
the absolute number of the column in the spreadsheet; 1 representing column "A,"
2 representing column B, etc. |
filterCriteria | FilterCriteria | The filter criteria used to perform the filtering. |
Return
PivotFilter — the newly created PivotFilter
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
addPivotValue(sourceDataColumn, summarizeFunction)
Defines a new pivot value in the pivot table with the specified summarizeFunction. The
specified sourceDataColumn indicates the column in the source data this value is based
on.
Parameters
| Name | Type | Description |
|---|---|---|
sourceDataColumn | Integer | The number of the column this group summarizes. This index represents
the absolute number of the column in the spreadsheet; 1 representing column "A,"
2 representing column B, etc. |
summarizeFunction | PivotTableSummarizeFunction |
Return
PivotValue — the newly created PivotValue
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
addRowGroup(sourceDataColumn)
Defines a new pivot row grouping in the pivot table. The specified sourceDataColumn
indicates the column in the source data this grouping is based on.
Parameters
| Name | Type | Description |
|---|---|---|
sourceDataColumn | Integer | The number of the column this group summarizes. This index represents
the absolute number of the column in the spreadsheet; 1 representing column "A,"
2 representing column B, etc. |
Return
PivotGroup — the newly created PivotGroup
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
getAnchorCell()
Returns the Range representing the cell where this pivot table is anchored.
Return
Range — this pivot table's anchor cell
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
getColumnGroups()
Returns an ordered list of the column groups in this pivot table.
Return
PivotGroup[] — the column groups in this pivot 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
getFilters()
Returns an ordered list of the filters in this pivot table.
Return
PivotFilter[] — the filters in this pivot 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
getPivotValues()
Returns an ordered list of the pivot values in this pivot table.
Return
PivotValue[] — the pivot values in this pivot 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
getRowGroups()
Returns an ordered list of the row groups in this pivot table.
Return
PivotGroup[] — the row groups in this pivot 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
getValuesDisplayOrientation()
Returns whether values are displayed as rows or columns.
Return
Dimension — whether values are displayed as rows or columns
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()
Deletes this pivot table. Further operations on this pivot table results in an error.
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
setValuesDisplayOrientation(dimension)
Sets the layout of this pivot table to display values as columns or rows.
Parameters
| Name | Type | Description |
|---|---|---|
dimension | Dimension | The dimension indicating how to display pivot values. |
Return
PivotTable — the pivot table 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
Need help? Visit our