Class PivotTable
Page Summary
Pivot tables can be accessed and modified using various methods.
Methods are available to add calculated pivot values, column groups, filters, and pivot values.
You can retrieve information about the pivot table's anchor cell, column groups, filters, pivot values, row groups, source data range, and values display orientation.
Pivot tables can be deleted using the
remove()method.The display orientation of pivot table values can be set using the
setValuesDisplayOrientation()method.
Access and modify pivot tables.
Methods
| Method | Return type | Brief description |
|---|---|---|
add | Pivot | Creates a new pivot value in the pivot table calculated from the specifiedformula withthe specifiedname. |
add | Pivot | Defines a new pivot column grouping in the pivot table. |
add | Pivot | Creates a new pivot filter for the pivot table. |
add | Pivot | Defines a new pivot value in the pivot table with the specifiedsummarize. |
add | Pivot | Defines a new pivot row grouping in the pivot table. |
as | Data | Returns the pivot table as a data source pivot table if the pivot table is linked to aData, ornull otherwise. |
get | Range | Returns theRange representing the cell where this pivot table is anchored. |
get | Pivot | Returns an ordered list of the column groups in this pivot table. |
get | Pivot | Returns an ordered list of the filters in this pivot table. |
get | Pivot | Returns an ordered list of the pivot values in this pivot table. |
get | Pivot | Returns an ordered list of the row groups in this pivot table. |
get | Range | Returns the source data range on which the pivot table is constructed. |
get | Dimension | Returns whether values are displayed as rows or columns. |
remove() | void | Deletes this pivot table. |
set | Pivot | 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 specifiedformula withthe specifiedname.
Parameters
| Name | Type | Description |
|---|---|---|
name | String | The name for this calculated pivot value. |
formula | String | The formula used to calculate this value. |
Return
Pivot — the newly createdPivot
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
addColumnGroup(sourceDataColumn)
Defines a new pivot column grouping in the pivot table. The specifiedsourceindicates the column in the source data this grouping is based on.
Parameters
| Name | Type | Description |
|---|---|---|
source | 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
Pivot — the newly createdPivot
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
addFilter(sourceDataColumn, filterCriteria)
Creates a new pivot filter for the pivot table. The specifiedsourceindicates the column in the source data this filter operates on.
Parameters
| Name | Type | Description |
|---|---|---|
source | 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. |
filter | Filter | The filter criteria used to perform the filtering. |
Return
Pivot — the newly createdPivot
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
addPivotValue(sourceDataColumn, summarizeFunction)
Defines a new pivot value in the pivot table with the specifiedsummarize. Thespecifiedsource indicates the column in the source data this value is basedon.
Parameters
| Name | Type | Description |
|---|---|---|
source | 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. |
summarize | Pivot |
Return
Pivot — the newly createdPivot
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
addRowGroup(sourceDataColumn)
Defines a new pivot row grouping in the pivot table. The specifiedsourceindicates the column in the source data this grouping is based on.
Parameters
| Name | Type | Description |
|---|---|---|
source | 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
Pivot — the newly createdPivot
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
asDataSourcePivotTable()
Returns the pivot table as a data source pivot table if the pivot table is linked to aData, ornull otherwise.
Return
Data — A data source pivot table.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getAnchorCell()
Returns theRange 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 followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getColumnGroups()
Returns an ordered list of the column groups in this pivot table.
Return
Pivot — the column groups in this pivot table
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getFilters()
Returns an ordered list of the filters in this pivot table.
Return
Pivot — the filters in this pivot table
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getPivotValues()
Returns an ordered list of the pivot values in this pivot table.
Return
Pivot — the pivot values in this pivot table
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getRowGroups()
Returns an ordered list of the row groups in this pivot table.
Return
Pivot — the row groups in this pivot table
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
getSourceDataRange()
Returns the source data range on which the pivot table is constructed.
Return
Range — The source data range of this pivot table.
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://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 followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://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 followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://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
Pivot — the pivot table for chaining
Authorization
Scripts that use this method require authorization with one or more of the followingscopes:
https://www.googleapis.com/auth/spreadsheets.currentonlyhttps://www.googleapis.com/auth/spreadsheets
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-11 UTC.