ExcelScript.PivotField interface

Package:

ExcelScript

Represents the Excel PivotField.

Methods

applyFilter(filter)	Sets one or more of the field's current PivotFilters and applies them to the field. If the provided filters are invalid or cannot be applied, an exception is thrown.
clearAllFilters()	Clears all criteria from all of the field's filters. This removes any active filtering on the field.
clearFilter(filterType)	Clears all existing criteria from the field's filter of the given type (if one is currently applied).
getFilters()	Gets all filters currently applied on the field.
getId()	ID of the PivotField.
getItems()	Returns the PivotItems associated with the PivotField.
getName()	Name of the PivotField.
getPivotItem(name)	Gets a PivotItem by name. If the PivotItem does not exist, then this method returns undefined.
getShowAllItems()	Determines whether to show all items of the PivotField.
getSubtotals()	Subtotals of the PivotField.
isFiltered(filterType)	Checks if there are any applied filters on the field.
setName(name)	Name of the PivotField.
setShowAllItems(showAllItems)	Determines whether to show all items of the PivotField.
setSubtotals(subtotals)	Subtotals of the PivotField.
sortByLabels(sortBy)	Sorts the PivotField. If a DataPivotHierarchy is specified, then sort will be applied based on it, if not sort will be based on the PivotField itself.
sortByValues(sortBy, valuesHierarchy, pivotItemScope)	Sorts the PivotField by specified values in a given scope. The scope defines which specific values will be used to sort when there are multiple values from the same DataPivotHierarchy.

Method Details

applyFilter(filter)

Sets one or more of the field's current PivotFilters and applies them to the field. If the provided filters are invalid or cannot be applied, an exception is thrown.

applyFilter(filter: PivotFilters): void;

Parameters

filter

ExcelScript.PivotFilters

A configured specific PivotFilter, or a PivotFilters interface containing multiple configured filters.

Returns

void

Examples

/**
 * This script applies a PivotValueFilter to the first row hierarchy in the PivotTable.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the PivotTable on the current worksheet.
  let sheet = workbook.getActiveWorksheet();
  let pivotTable = sheet.getPivotTables()[0];

  // Get the first row hierarchy to use as the field which gets filtered.
  let rowHierarchy = pivotTable.getRowHierarchies()[0];

  // Get the first data hierarchy to use as the values for filtering the rows.
  let dataHierarchy = pivotTable.getDataHierarchies()[0];

  // Create a filter that excludes values greater than 500.
  let filter: ExcelScript.PivotValueFilter = {
    condition: ExcelScript.ValueFilterCondition.greaterThan,
    comparator: 500,
    value: dataHierarchy.getName()
  };

  // Apply the filter.
  rowHierarchy.getPivotField(rowHierarchy.getName()).applyFilter({
    valueFilter: filter
  });
}

clearAllFilters()

Clears all criteria from all of the field's filters. This removes any active filtering on the field.

clearAllFilters(): void;

Returns

void

clearFilter(filterType)

Clears all existing criteria from the field's filter of the given type (if one is currently applied).

clearFilter(filterType: PivotFilterType): void;

Parameters

filterType

ExcelScript.PivotFilterType

The type of filter on the field of which to clear all criteria.

Returns

void

Examples

/**
 * This script gets the "Type" field from the "Farms Sales" PivotTable 
 * and clears the value filter from it.
 */ 
function main(workbook: ExcelScript.Workbook) {
  // Get the PivotTable named "Farm Sales".
  const pivot = workbook.getPivotTable("Farm Sales");

  // Get the "Type" field.
  const typeField = pivot.getHierarchy("Type").getPivotField("Type");

  // Clear the value filter (if there is one) from the field.
  typeField.clearFilter(ExcelScript.PivotFilterType.value);
}

getFilters()

Gets all filters currently applied on the field.

getFilters(): PivotFilters;

Returns

ExcelScript.PivotFilters

getId()

ID of the PivotField.

getId(): string;

Returns

string

getItems()

Returns the PivotItems associated with the PivotField.

getItems(): PivotItem[];

Returns

ExcelScript.PivotItem[]

getName()

Name of the PivotField.

getName(): string;

Returns

string

getPivotItem(name)

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

getPivotItem(name: string): PivotItem | undefined;

Parameters

name

string

Name of the PivotItem to be retrieved.

Returns

ExcelScript.PivotItem | undefined

getShowAllItems()

Determines whether to show all items of the PivotField.

getShowAllItems(): boolean;

Returns

boolean

getSubtotals()

Subtotals of the PivotField.

getSubtotals(): Subtotals;

Returns

ExcelScript.Subtotals

isFiltered(filterType)

Checks if there are any applied filters on the field.

isFiltered(filterType?: PivotFilterType): boolean;

Parameters

filterType

ExcelScript.PivotFilterType

The filter type to check. If no type is provided, this method will check if any filter is applied.

Returns

boolean

setName(name)

Name of the PivotField.

setName(name: string): void;

Parameters

name

string

Returns

void

setShowAllItems(showAllItems)

Determines whether to show all items of the PivotField.

setShowAllItems(showAllItems: boolean): void;

Parameters

showAllItems

boolean

Returns

void

setSubtotals(subtotals)

Subtotals of the PivotField.

setSubtotals(subtotals: Subtotals): void;

Parameters

subtotals

ExcelScript.Subtotals

Returns

void

sortByLabels(sortBy)

Sorts the PivotField. If a DataPivotHierarchy is specified, then sort will be applied based on it, if not sort will be based on the PivotField itself.

sortByLabels(sortBy: SortBy): void;

Parameters

sortBy

ExcelScript.SortBy

Specifies if the sorting is done in ascending or descending order.

Returns

void

sortByValues(sortBy, valuesHierarchy, pivotItemScope)

Sorts the PivotField by specified values in a given scope. The scope defines which specific values will be used to sort when there are multiple values from the same DataPivotHierarchy.

sortByValues(
            sortBy: SortBy,
            valuesHierarchy: DataPivotHierarchy,
            pivotItemScope?: Array<PivotItem | string>
        ): void;

Parameters

sortBy

ExcelScript.SortBy

Specifies if the sorting is done in ascending or descending order.

valuesHierarchy

ExcelScript.DataPivotHierarchy

Specifies the values hierarchy on the data axis to be used for sorting.

pivotItemScope

Array<ExcelScript.PivotItem | string>

The items that should be used for the scope of the sorting. These will be the items that make up the row or column that you want to sort on. If a string is used instead of a PivotItem, the string represents the ID of the PivotItem. If there are no items other than data hierarchy on the axis you want to sort on, this can be empty.

Returns

void

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