Class PivotTable

PivotTable

Access and modify pivot tables.

Methods

MethodReturn typeBrief description
addCalculatedPivotValue(name, formula)PivotValueCreates a new pivot value in the pivot table calculated from the specified formula with the specified name.
addColumnGroup(sourceDataColumn)PivotGroupDefines a new pivot column grouping in the pivot table.
addFilter(sourceDataColumn, filterCriteria)PivotFilterCreates a new pivot filter for the pivot table.
addPivotValue(sourceDataColumn, summarizeFunction)PivotValueDefines a new pivot value in the pivot table with the specified summarizeFunction.
addRowGroup(sourceDataColumn)PivotGroupDefines a new pivot row grouping in the pivot table.
asDataSourcePivotTable()DataSourcePivotTableReturns the pivot table as a data source pivot table if the pivot table is linked to a DataSource, or null otherwise.
getAnchorCell()RangeReturns 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.
getSourceDataRange()RangeReturns the source data range on which the pivot table is constructed.
getValuesDisplayOrientation()DimensionReturns whether values are displayed as rows or columns.
remove()voidDeletes this pivot table.
setValuesDisplayOrientation(dimension)PivotTableSets 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

NameTypeDescription
nameStringThe name for this calculated pivot value.
formulaStringThe 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

NameTypeDescription
sourceDataColumnIntegerThe 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

NameTypeDescription
sourceDataColumnIntegerThe 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.
filterCriteriaFilterCriteriaThe 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

NameTypeDescription
sourceDataColumnIntegerThe 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.
summarizeFunctionPivotTableSummarizeFunction

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

NameTypeDescription
sourceDataColumnIntegerThe 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

asDataSourcePivotTable()

Returns the pivot table as a data source pivot table if the pivot table is linked to a DataSource, or null otherwise.

Return

DataSourcePivotTable — A data source 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

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

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 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

NameTypeDescription
dimensionDimensionThe 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