ExcelScript.PivotTable interface

Package:

ExcelScript

Represents an Excel PivotTable.

Remarks

Examples

/**
 * 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.

addColumnHierarchy(
            pivotHierarchy: PivotHierarchy
        ): RowColumnPivotHierarchy;

Parameters

pivotHierarchy

ExcelScript.PivotHierarchy

Returns

ExcelScript.RowColumnPivotHierarchy

Examples

/**
 * 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.

addDataHierarchy(pivotHierarchy: PivotHierarchy): DataPivotHierarchy;

Parameters

pivotHierarchy

ExcelScript.PivotHierarchy

Returns

ExcelScript.DataPivotHierarchy

Examples

/**
 * 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.

addFilterHierarchy(
            pivotHierarchy: PivotHierarchy
        ): FilterPivotHierarchy;

Parameters

pivotHierarchy

ExcelScript.PivotHierarchy

Returns

ExcelScript.FilterPivotHierarchy

Examples

/**
 * 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.

addRowHierarchy(
            pivotHierarchy: PivotHierarchy
        ): RowColumnPivotHierarchy;

Parameters

pivotHierarchy

ExcelScript.PivotHierarchy

Returns

ExcelScript.RowColumnPivotHierarchy

Examples

/**
 * 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.

delete(): void;

Returns

void

getAllowMultipleFiltersPerField()

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

getAllowMultipleFiltersPerField(): boolean;

Returns

boolean

getColumnHierarchies()

The Column Pivot Hierarchies of the PivotTable.

getColumnHierarchies(): RowColumnPivotHierarchy[];

Returns

ExcelScript.RowColumnPivotHierarchy[]

getColumnHierarchy(name)

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

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.

getDataHierarchies(): DataPivotHierarchy[];

Returns

ExcelScript.DataPivotHierarchy[]

getDataHierarchy(name)

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

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.

getEnableDataValueEditing(): boolean;

Returns

boolean

getFilterHierarchies()

The Filter Pivot Hierarchies of the PivotTable.

getFilterHierarchies(): FilterPivotHierarchy[];

Returns

ExcelScript.FilterPivotHierarchy[]

getFilterHierarchy(name)

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

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.

getHierarchies(): PivotHierarchy[];

Returns

ExcelScript.PivotHierarchy[]

getHierarchy(name)

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

getHierarchy(name: string): PivotHierarchy | undefined;

Parameters

name

string

Name of the PivotHierarchy to be retrieved.

Returns

ExcelScript.PivotHierarchy | undefined

getId()

ID of the PivotTable.

getId(): string;

Returns

string

getLayout()

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

getLayout(): PivotLayout;

Returns

ExcelScript.PivotLayout

Examples

/**
 * 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.

getName(): string;

Returns

string

getRowHierarchies()

The Row Pivot Hierarchies of the PivotTable.

getRowHierarchies(): RowColumnPivotHierarchy[];

Returns

ExcelScript.RowColumnPivotHierarchy[]

getRowHierarchy(name)

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

getRowHierarchy(name: string): RowColumnPivotHierarchy | undefined;

Parameters

name

string

Name of the RowColumnPivotHierarchy to be retrieved.

Returns

ExcelScript.RowColumnPivotHierarchy | undefined

Examples

/**
 * 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.

getUseCustomSortLists(): boolean;

Returns

boolean

getWorksheet()

The worksheet containing the current PivotTable.

getWorksheet(): Worksheet;

Returns

ExcelScript.Worksheet

refresh()

Refreshes the PivotTable.

refresh(): void;

Returns

void

removeColumnHierarchy(rowColumnPivotHierarchy)

Removes the PivotHierarchy from the current axis.

removeColumnHierarchy(
            rowColumnPivotHierarchy: RowColumnPivotHierarchy
        ): void;

Parameters

rowColumnPivotHierarchy

ExcelScript.RowColumnPivotHierarchy

Returns

void

removeDataHierarchy(DataPivotHierarchy)

Removes the PivotHierarchy from the current axis.

removeDataHierarchy(DataPivotHierarchy: DataPivotHierarchy): void;

Parameters

DataPivotHierarchy

ExcelScript.DataPivotHierarchy

Returns

void

removeFilterHierarchy(filterPivotHierarchy)

Removes the PivotHierarchy from the current axis.

removeFilterHierarchy(filterPivotHierarchy: FilterPivotHierarchy): void;

Parameters

filterPivotHierarchy

ExcelScript.FilterPivotHierarchy

Returns

void

removeRowHierarchy(rowColumnPivotHierarchy)

Removes the PivotHierarchy from the current axis.

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.

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.

setEnableDataValueEditing(enableDataValueEditing: boolean): void;

Parameters

enableDataValueEditing

boolean

Returns

void

setName(name)

Name of the PivotTable.

setName(name: string): void;

Parameters

name

string

Returns

void

setUseCustomSortLists(useCustomSortLists)

Specifies if the PivotTable uses custom lists when sorting.

setUseCustomSortLists(useCustomSortLists: boolean): void;

Parameters

useCustomSortLists

boolean

Returns

void