ExcelScript.PivotTable interface

Package:

ExcelScript

Represents an Excel PivotTable.

Remarks

Examples

TypeScript

/**
 * This script creates a PivotTable from an existing table and adds it to a new worksheet.
 * This script assumes there is a table in the current worksheet with columns named "Type" and "Sales".
 */
function main(workbook: ExcelScript.Workbook) {
  // Create a PivotTable based on a table in the current worksheet.
  let sheet = workbook.getActiveWorksheet();
  let table = sheet.getTables()[0];

  // Add the PivotTable to a new worksheet.
  let newSheet = workbook.addWorksheet("Pivot");
  let pivotTable = newSheet.addPivotTable("My Pivot", table, "A1");

  // Add fields to the PivotTable to show "Sales" per "Type".
  pivotTable.addRowHierarchy(pivotTable.getHierarchy("Type"));
  pivotTable.addDataHierarchy(pivotTable.getHierarchy("Sales"));
}

Methods

addColumnHierarchy(pivotHierarchy)	Adds the PivotHierarchy to the current axis. If the hierarchy is present elsewhere on the row, column, or filter axis, it will be removed from that location.
addDataHierarchy(pivotHierarchy)	Adds the PivotHierarchy to the current axis.
addFilterHierarchy(pivotHierarchy)	Adds the PivotHierarchy to the current axis. If the hierarchy is present elsewhere on the row, column, or filter axis, it will be removed from that location.
addRowHierarchy(pivotHierarchy)	Adds the PivotHierarchy to the current axis. If the hierarchy is present elsewhere on the row, column, or filter axis, it will be removed from that location.
delete()	Deletes the PivotTable.
getAllowMultipleFiltersPerField()	Specifies if the PivotTable allows the application of multiple PivotFilters on a given PivotField in the table.
getColumnHierarchies()	The Column Pivot Hierarchies of the PivotTable.
getColumnHierarchy(name)	Gets a RowColumnPivotHierarchy by name. If the RowColumnPivotHierarchy does not exist, then this method returns undefined.
getDataHierarchies()	The Data Pivot Hierarchies of the PivotTable.
getDataHierarchy(name)	Gets a DataPivotHierarchy by name. If the DataPivotHierarchy does not exist, then this method returns undefined.
getEnableDataValueEditing()	Specifies if the PivotTable allows values in the data body to be edited by the user.
getFilterHierarchies()	The Filter Pivot Hierarchies of the PivotTable.
getFilterHierarchy(name)	Gets a FilterPivotHierarchy by name. If the FilterPivotHierarchy does not exist, then this method returns undefined.
getHierarchies()	The Pivot Hierarchies of the PivotTable.
getHierarchy(name)	Gets a PivotHierarchy by name. If the PivotHierarchy does not exist, then this method returns undefined.
getId()	ID of the PivotTable.
getLayout()	The PivotLayout describing the layout and visual structure of the PivotTable.
getName()	Name of the PivotTable.
getRowHierarchies()	The Row Pivot Hierarchies of the PivotTable.
getRowHierarchy(name)	Gets a RowColumnPivotHierarchy by name. If the RowColumnPivotHierarchy does not exist, then this method returns undefined.
getUseCustomSortLists()	Specifies if the PivotTable uses custom lists when sorting.
getWorksheet()	The worksheet containing the current PivotTable.
refresh()	Refreshes the PivotTable.
removeColumnHierarchy(rowColumnPivotHierarchy)	Removes the PivotHierarchy from the current axis.
removeDataHierarchy(DataPivotHierarchy)	Removes the PivotHierarchy from the current axis.
removeFilterHierarchy(filterPivotHierarchy)	Removes the PivotHierarchy from the current axis.
removeRowHierarchy(rowColumnPivotHierarchy)	Removes the PivotHierarchy from the current axis.
setAllowMultipleFiltersPerField(allowMultipleFiltersPerField)	Specifies if the PivotTable allows the application of multiple PivotFilters on a given PivotField in the table.
setEnableDataValueEditing(enableDataValueEditing)	Specifies if the PivotTable allows values in the data body to be edited by the user.
setName(name)	Name of the PivotTable.
setUseCustomSortLists(useCustomSortLists)	Specifies if the PivotTable uses custom lists when sorting.

Method Details

addColumnHierarchy(pivotHierarchy)

Adds the PivotHierarchy to the current axis. If the hierarchy is present elsewhere on the row, column, or filter axis, it will be removed from that location.

TypeScript

addColumnHierarchy(
            pivotHierarchy: PivotHierarchy
        ): RowColumnPivotHierarchy;

Parameters

pivotHierarchy

ExcelScript.PivotHierarchy

Returns

ExcelScript.RowColumnPivotHierarchy

Examples

TypeScript

/**
 * This script adds a row hierarchy to the PivotTable on the current worksheet.
 * This assumes the source data has columns named 
 * "Type", "Classification", and "Sales".
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the PivotTable on the current worksheet.
  let sheet = workbook.getActiveWorksheet();
  let pivotTable = sheet.getPivotTables()[0];

  // Add the field "Type" to the PivotTable as a row hierarchy.
  pivotTable.addRowHierarchy(pivotTable.getHierarchy("Type"));

  // Add the field "Classification" to the PivotTable as a column hierarchy.
  pivotTable.addColumnHierarchy(pivotTable.getHierarchy("Classification"));

  // Add the field "Sales" to the PivotTable as a data hierarchy.
  // By default, this displays the sums of the values in "Sales" based on the "Type".
  pivotTable.addDataHierarchy(pivotTable.getHierarchy("Sales"));
}

addDataHierarchy(pivotHierarchy)

Adds the PivotHierarchy to the current axis.

TypeScript

addDataHierarchy(pivotHierarchy: PivotHierarchy): DataPivotHierarchy;

Parameters

pivotHierarchy

ExcelScript.PivotHierarchy

Returns

ExcelScript.DataPivotHierarchy

Examples

TypeScript

/**
 * This script creates a PivotTable from an existing table and adds it to a new worksheet.
 * This script assumes there is a table in the current worksheet with columns named "Type" and "Sales".
 */
function main(workbook: ExcelScript.Workbook) {
  // Create a PivotTable based on a table in the current worksheet.
  let sheet = workbook.getActiveWorksheet();
  let table = sheet.getTables()[0];

  // Add the PivotTable to a new worksheet.
  let newSheet = workbook.addWorksheet("Pivot");
  let pivotTable = newSheet.addPivotTable("My Pivot", table, "A1");

  // Add fields to the PivotTable to show "Sales" per "Type".
  pivotTable.addRowHierarchy(pivotTable.getHierarchy("Type"));
  pivotTable.addDataHierarchy(pivotTable.getHierarchy("Sales"));
}

addFilterHierarchy(pivotHierarchy)

Adds the PivotHierarchy to the current axis. If the hierarchy is present elsewhere on the row, column, or filter axis, it will be removed from that location.

TypeScript

addFilterHierarchy(
            pivotHierarchy: PivotHierarchy
        ): FilterPivotHierarchy;

Parameters

pivotHierarchy

ExcelScript.PivotHierarchy

Returns

ExcelScript.FilterPivotHierarchy

Examples

TypeScript

/**
 * This script adds a manual filter to a PivotTable. 
 */
function main(workbook: ExcelScript.Workbook)
{
  // Get the first PivotTable in the workbook.
  const pivot = workbook.getPivotTables()[0];

  // Get the hierarchy to use as the filter.
  const location = pivot.getHierarchy("Location");

  // Use "Location" as the FilterHierarchy.
  pivot.addFilterHierarchy(location);

  // Select items for the filter.
  // Note that hierarchies and fields have a 1:1 relationship in Excel,
  // so `getFields()[0]` always gets the correct field.
  location.getFields()[0].applyFilter({
    manualFilter: {
      selectedItems: ["Seattle", "Chicago"]
    }
  });
}

addRowHierarchy(pivotHierarchy)

Adds the PivotHierarchy to the current axis. If the hierarchy is present elsewhere on the row, column, or filter axis, it will be removed from that location.

TypeScript

addRowHierarchy(
            pivotHierarchy: PivotHierarchy
        ): RowColumnPivotHierarchy;

Parameters

pivotHierarchy

ExcelScript.PivotHierarchy

Returns

ExcelScript.RowColumnPivotHierarchy

Examples

TypeScript

/**
 * This script creates a PivotTable from an existing table and adds it to a new worksheet.
 * This script assumes there is a table in the current worksheet with columns named "Type" and "Sales".
 */
function main(workbook: ExcelScript.Workbook) {
  // Create a PivotTable based on a table in the current worksheet.
  let sheet = workbook.getActiveWorksheet();
  let table = sheet.getTables()[0];

  // Add the PivotTable to a new worksheet.
  let newSheet = workbook.addWorksheet("Pivot");
  let pivotTable = newSheet.addPivotTable("My Pivot", table, "A1");

  // Add fields to the PivotTable to show "Sales" per "Type".
  pivotTable.addRowHierarchy(pivotTable.getHierarchy("Type"));
  pivotTable.addDataHierarchy(pivotTable.getHierarchy("Sales"));
}

delete()

Deletes the PivotTable.

TypeScript

delete(): void;

Returns

void

getAllowMultipleFiltersPerField()

Specifies if the PivotTable allows the application of multiple PivotFilters on a given PivotField in the table.

TypeScript

getAllowMultipleFiltersPerField(): boolean;

Returns

boolean

getColumnHierarchies()

The Column Pivot Hierarchies of the PivotTable.

TypeScript

getColumnHierarchies(): RowColumnPivotHierarchy[];

Returns

ExcelScript.RowColumnPivotHierarchy[]

getColumnHierarchy(name)

Gets a RowColumnPivotHierarchy by name. If the RowColumnPivotHierarchy does not exist, then this method returns undefined.

TypeScript

getColumnHierarchy(name: string): RowColumnPivotHierarchy | undefined;

Parameters

name

string

Name of the RowColumnPivotHierarchy to be retrieved.

Returns

ExcelScript.RowColumnPivotHierarchy | undefined

getDataHierarchies()

The Data Pivot Hierarchies of the PivotTable.

TypeScript

getDataHierarchies(): DataPivotHierarchy[];

Returns

ExcelScript.DataPivotHierarchy[]

getDataHierarchy(name)

Gets a DataPivotHierarchy by name. If the DataPivotHierarchy does not exist, then this method returns undefined.

TypeScript

getDataHierarchy(name: string): DataPivotHierarchy | undefined;

Parameters

name

string

Name of the DataPivotHierarchy to be retrieved.

Returns

ExcelScript.DataPivotHierarchy | undefined

getEnableDataValueEditing()

Specifies if the PivotTable allows values in the data body to be edited by the user.

TypeScript

getEnableDataValueEditing(): boolean;

Returns

boolean

getFilterHierarchies()

The Filter Pivot Hierarchies of the PivotTable.

TypeScript

getFilterHierarchies(): FilterPivotHierarchy[];

Returns

ExcelScript.FilterPivotHierarchy[]

getFilterHierarchy(name)

Gets a FilterPivotHierarchy by name. If the FilterPivotHierarchy does not exist, then this method returns undefined.

TypeScript

getFilterHierarchy(name: string): FilterPivotHierarchy | undefined;

Parameters

name

string

Name of the FilterPivotHierarchy to be retrieved.

Returns

ExcelScript.FilterPivotHierarchy | undefined

getHierarchies()

The Pivot Hierarchies of the PivotTable.

TypeScript

getHierarchies(): PivotHierarchy[];

Returns

ExcelScript.PivotHierarchy[]

getHierarchy(name)

Gets a PivotHierarchy by name. If the PivotHierarchy does not exist, then this method returns undefined.

TypeScript

getHierarchy(name: string): PivotHierarchy | undefined;

Parameters

name

string

Name of the PivotHierarchy to be retrieved.

Returns

ExcelScript.PivotHierarchy | undefined

getId()

ID of the PivotTable.

TypeScript

getId(): string;

Returns

string

getLayout()

The PivotLayout describing the layout and visual structure of the PivotTable.

TypeScript

getLayout(): PivotLayout;

Returns

ExcelScript.PivotLayout

Examples

TypeScript

/**
 * This script sets the layout of the "Farms Sales" PivotTable to the "tabular"
 * setting. This places the fields from the Rows area in separate columns.
 */ 
function main(workbook: ExcelScript.Workbook) {
  // Get the PivotTable named "Farm Sales".
  const pivot = workbook.getPivotTable("Farm Sales");

  // Get the PivotLayout object.
  const layout = pivot.getLayout();

  // Set the layout type to "tabular".
  layout.setLayoutType(ExcelScript.PivotLayoutType.tabular);
}

getName()

Name of the PivotTable.

TypeScript

getName(): string;

Returns

string

getRowHierarchies()

The Row Pivot Hierarchies of the PivotTable.

TypeScript

getRowHierarchies(): RowColumnPivotHierarchy[];

Returns

ExcelScript.RowColumnPivotHierarchy[]

getRowHierarchy(name)

Gets a RowColumnPivotHierarchy by name. If the RowColumnPivotHierarchy does not exist, then this method returns undefined.

TypeScript

getRowHierarchy(name: string): RowColumnPivotHierarchy | undefined;

Parameters

name

string

Name of the RowColumnPivotHierarchy to be retrieved.

Returns

ExcelScript.RowColumnPivotHierarchy | undefined

Examples

TypeScript

/**
 * This sample sorts the rows of a PivotTable.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get an existing PivotTable.
  const pivotTable = workbook.getPivotTable("Farm Sales");

  // Get the data hierarchy to use as the basis of the sort.
  const valueFieldToSortOn = pivotTable.getDataHierarchy("Sum of Crates Sold Wholesale");

  // Get the row to sort.
  const rowToSort = pivotTable.getRowHierarchy("Farm");

  // Sort the "Farm" row's only field by the values in "Sum of Crates Sold Wholesale".
  rowToSort.getFields()[0].sortByValues(ExcelScript.SortBy.descending, valueFieldToSortOn);
}

getUseCustomSortLists()

Specifies if the PivotTable uses custom lists when sorting.

TypeScript

getUseCustomSortLists(): boolean;

Returns

boolean

getWorksheet()

The worksheet containing the current PivotTable.

TypeScript

getWorksheet(): Worksheet;

Returns

ExcelScript.Worksheet

refresh()

Refreshes the PivotTable.

TypeScript

refresh(): void;

Returns

void

removeColumnHierarchy(rowColumnPivotHierarchy)

Removes the PivotHierarchy from the current axis.

TypeScript

removeColumnHierarchy(
            rowColumnPivotHierarchy: RowColumnPivotHierarchy
        ): void;

Parameters

rowColumnPivotHierarchy

ExcelScript.RowColumnPivotHierarchy

Returns

void

removeDataHierarchy(DataPivotHierarchy)

Removes the PivotHierarchy from the current axis.

TypeScript

removeDataHierarchy(DataPivotHierarchy: DataPivotHierarchy): void;

Parameters

DataPivotHierarchy

ExcelScript.DataPivotHierarchy

Returns

void

removeFilterHierarchy(filterPivotHierarchy)

Removes the PivotHierarchy from the current axis.

TypeScript

removeFilterHierarchy(filterPivotHierarchy: FilterPivotHierarchy): void;

Parameters

filterPivotHierarchy

ExcelScript.FilterPivotHierarchy

Returns

void

removeRowHierarchy(rowColumnPivotHierarchy)

Removes the PivotHierarchy from the current axis.

TypeScript

removeRowHierarchy(
            rowColumnPivotHierarchy: RowColumnPivotHierarchy
        ): void;

Parameters

rowColumnPivotHierarchy

ExcelScript.RowColumnPivotHierarchy

Returns

void

setAllowMultipleFiltersPerField(allowMultipleFiltersPerField)

Specifies if the PivotTable allows the application of multiple PivotFilters on a given PivotField in the table.

TypeScript

setAllowMultipleFiltersPerField(
            allowMultipleFiltersPerField: boolean
        ): void;

Parameters

allowMultipleFiltersPerField

boolean

Returns

void

setEnableDataValueEditing(enableDataValueEditing)

Specifies if the PivotTable allows values in the data body to be edited by the user.

TypeScript

setEnableDataValueEditing(enableDataValueEditing: boolean): void;

Parameters

enableDataValueEditing

boolean

Returns

void

setName(name)

Name of the PivotTable.

TypeScript

setName(name: string): void;

Parameters

name

string

Returns

void

setUseCustomSortLists(useCustomSortLists)

Specifies if the PivotTable uses custom lists when sorting.

TypeScript

setUseCustomSortLists(useCustomSortLists: boolean): void;

Parameters

useCustomSortLists

boolean

Returns

void