Class DataSourcePivotTable

Data Source Pivot Table

Access and modify existing data source pivot table. To create a new data source pivot table, use Range.createDataSourcePivotTable(dataSource) .

Only use this class with data that's connected to a database.

Methods

Method	Return type	Brief description
`add Column Group(columnName)`	`Pivot Group`	Adds a new pivot column group based on the specified data source column.
`add Filter(columnName, filterCriteria)`	`Pivot Filter`	Adds a new filter based on the specified data source column with the specified filter criteria.
`add Pivot Value(columnName)`	`Pivot Value`	Adds a new pivot value based on the specified data source column without any summarize function.
`add Pivot Value(columnName, summarizeFunction)`	`Pivot Value`	Adds a new pivot value based on the specified data source column with the specified summarize function.
`add Row Group(columnName)`	`Pivot Group`	Adds a new pivot row group based on the specified data source column.
`as Pivot Table()`	`Pivot Table`	Returns the data source pivot table as a regular pivot table object.
`cancel Data Refresh()`	`Data Source Pivot Table`	Cancels the data refresh associated with this object if it's currently running.
`force Refresh Data()`	`Data Source Pivot Table`	Refreshes the data of this object regardless of the current state.
`get Data Source()`	`Data Source`	Gets the data source the object is linked to.
`get Status()`	`Data Execution Status`	Gets the data execution status of the object.
`refresh Data()`	`Data Source Pivot Table`	Refreshes the data of the object.
`wait For Completion(timeoutInSeconds)`	`Data Execution Status`	Waits until the current execution completes, timing out after the provided number of seconds.

Detailed documentation

`add Column Group(columnName)`

Adds a new pivot column group based on the specified data source column.

Parameters

Name	Type	Description
`column Name`	`String`	The data source column name the pivot group is based on.

Return

Pivot Group — The new pivot 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

`add Filter(columnName, filterCriteria)`

Adds a new filter based on the specified data source column with the specified filter criteria.

Parameters

Name	Type	Description
`column Name`	`String`	The data source column name the filter is based on.
`filter Criteria`	`Filter Criteria`	The criteria the filter uses.

Return

Pivot Filter — The new filter.

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

`add Pivot Value(columnName)`

Adds a new pivot value based on the specified data source column without any summarize function. For Looker measures only.

The following code shows how you can add a Looker measure as a pivot value.

 // TODO(developer): Replace with your spreadsheet ID which has a Looker data 
 // source. 
 const 
  
 spreadsheet 
  
 = 
  
 SpreadsheetApp 
 . 
 openById 
 ( 
 'abcd1234' 
 ); 
 const 
  
 datasource 
  
 = 
  
 spreadsheet 
 . 
 getDataSources 
 ()[ 
 0 
 ]; 
 const 
  
 pivotTable 
  
 = 
  
 datasource 
 . 
 createDataSourcePivotTableOnNewSheet 
 (); 
 pivotTable 
 . 
 addPivotValue 
 ( 
 'columnName' 
 );

This method is only available for Looker data sources.

Parameters

Name	Type	Description
`column Name`	`String`	The data source column name the pivot value is based on.

Return

Pivot Value — The new pivot value.

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

`add Pivot Value(columnName, summarizeFunction)`

Adds a new pivot value based on the specified data source column with the specified summarize function.

In order to add pivot values for Looker measures, use add Pivot Value(columnName) .

Parameters

Name	Type	Description
`column Name`	`String`	The data source column name the pivot value is based on.
`summarize Function`	`Pivot Table Summarize Function`	The summarize function the pivot value uses.

Return

Pivot Value — The new pivot value.

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

`add Row Group(columnName)`

Adds a new pivot row group based on the specified data source column.

Parameters

Name	Type	Description
`column Name`	`String`	The data source column name the pivot group is based on.

Return

Pivot Group — The new pivot 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

`as Pivot Table()`

Returns the data source pivot table as a regular pivot table object.

Return

Pivot Table — The 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

`cancel Data Refresh()`

Cancels the data refresh associated with this object if it's currently running.

This example shows how to cancel a formula refresh.

 const 
  
 spreadsheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActive 
 (); 
 const 
  
 formula 
  
 = 
  
 spreadsheet 
 . 
 getDataSourceFormulas 
 ()[ 
 0 
 ]; 
 // Cancel the ongoing refresh on the formula. 
 formula 
 . 
 cancelDataRefresh 
 ();

Throws an exception if the data source type is not enabled. Use Spreadsheet App#enable...Execution() methods to enable data execution for specific data source type.

Return

Data Source Pivot Table — The data object.

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

`force Refresh Data()`

Refreshes the data of this object regardless of the current state. See refresh Data() for more details. If you want to cancel a currently running refresh of this object, see cancel Data Refresh() .

Throws an exception if the data source type is not enabled. Use Spreadsheet App#enable...Execution() methods to enable data execution for specific data source type.

Return

Data Source Pivot Table — The data object.

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

`get Data Source()`

Gets the data source the object is linked to.

Return

Data Source — The data source.

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

`get Status()`

Gets the data execution status of the object.

Return

Data Execution Status — The data execution status.

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

`refresh Data()`

Refreshes the data of the object.

Throws an exception if currently in error state. Use Data Source#updateSpec() to update the specification. The method is preferred over force Refresh Data() to prevent unexpected edits on data source.

Throws an exception if the data source type is not enabled. Use Spreadsheet App#enable...Execution() methods to enable data execution for specific data source type.

Return

Data Source Pivot Table — The data object.

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

`wait For Completion(timeoutInSeconds)`

Waits until the current execution completes, timing out after the provided number of seconds. Throws an exception if the execution is not completed when timing out, but does not cancel the data execution.

Parameters

Name	Type	Description
`timeout In Seconds`	`Integer`	The time to wait for data execution, in seconds. The maximum is 300 seconds.

Return

Data Execution Status — The data execution status.

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