ExcelScript.PivotTable interface

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

Returns

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"));
}

Adds the PivotHierarchy to the current axis.

addDataHierarchy(pivotHierarchy: PivotHierarchy): DataPivotHierarchy;

Parameters

Returns

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"));
}

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

Returns

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"]
    }
  });
}

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

Returns

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"));
}

Deletes the PivotTable.

delete(): void;

Returns

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

getAllowMultipleFiltersPerField(): boolean;

Returns

The Column Pivot Hierarchies of the PivotTable.

getColumnHierarchies(): RowColumnPivotHierarchy[];

Returns

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

getColumnHierarchy(name: string): RowColumnPivotHierarchy | undefined;

Parameters

Returns

The Data Pivot Hierarchies of the PivotTable.

getDataHierarchies(): DataPivotHierarchy[];

Returns

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

getDataHierarchy(name: string): DataPivotHierarchy | undefined;

Parameters

Returns

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

getEnableDataValueEditing(): boolean;

Returns

The Filter Pivot Hierarchies of the PivotTable.

getFilterHierarchies(): FilterPivotHierarchy[];

Returns

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

getFilterHierarchy(name: string): FilterPivotHierarchy | undefined;

Parameters

Returns

The Pivot Hierarchies of the PivotTable.

getHierarchies(): PivotHierarchy[];

Returns

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

getHierarchy(name: string): PivotHierarchy | undefined;

Parameters

Returns

ID of the PivotTable.

getId(): string;

Returns

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

getLayout(): PivotLayout;

Returns

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);
}

Name of the PivotTable.

getName(): string;

Returns

The Row Pivot Hierarchies of the PivotTable.

getRowHierarchies(): RowColumnPivotHierarchy[];

Returns

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

getRowHierarchy(name: string): RowColumnPivotHierarchy | undefined;

Parameters

Returns

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);
}

Specifies if the PivotTable uses custom lists when sorting.

getUseCustomSortLists(): boolean;

Returns

The worksheet containing the current PivotTable.

getWorksheet(): Worksheet;

Returns

Refreshes the PivotTable.

refresh(): void;

Returns

Removes the PivotHierarchy from the current axis.

removeColumnHierarchy(
            rowColumnPivotHierarchy: RowColumnPivotHierarchy
        ): void;

Parameters

Returns

Removes the PivotHierarchy from the current axis.

removeDataHierarchy(DataPivotHierarchy: DataPivotHierarchy): void;

Parameters

Returns

Removes the PivotHierarchy from the current axis.

removeFilterHierarchy(filterPivotHierarchy: FilterPivotHierarchy): void;

Parameters

Returns

Removes the PivotHierarchy from the current axis.

removeRowHierarchy(
            rowColumnPivotHierarchy: RowColumnPivotHierarchy
        ): void;

Parameters

Returns

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

setAllowMultipleFiltersPerField(
            allowMultipleFiltersPerField: boolean
        ): void;

Parameters

Returns

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

setEnableDataValueEditing(enableDataValueEditing: boolean): void;

Parameters

Returns

Name of the PivotTable.

setName(name: string): void;

Parameters

Returns

Specifies if the PivotTable uses custom lists when sorting.

setUseCustomSortLists(useCustomSortLists: boolean): void;

Parameters

Returns