ExcelScript.PivotField interface

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

Returns

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

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

clearAllFilters(): void;

Returns

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

clearFilter(filterType: PivotFilterType): void;

Parameters

Returns

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

Gets all filters currently applied on the field.

getFilters(): PivotFilters;

Returns

ID of the PivotField.

getId(): string;

Returns

Returns the PivotItems associated with the PivotField.

getItems(): PivotItem[];

Returns

Name of the PivotField.

getName(): string;

Returns

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

getPivotItem(name: string): PivotItem | undefined;

Parameters

Returns

Determines whether to show all items of the PivotField.

getShowAllItems(): boolean;

Returns

Subtotals of the PivotField.

getSubtotals(): Subtotals;

Returns

Checks if there are any applied filters on the field.

isFiltered(filterType?: PivotFilterType): boolean;

Parameters

Returns

Name of the PivotField.

setName(name: string): void;

Parameters

Returns

Determines whether to show all items of the PivotField.

setShowAllItems(showAllItems: boolean): void;

Parameters

Returns

Subtotals of the PivotField.

setSubtotals(subtotals: Subtotals): void;

Parameters

Returns

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

Returns

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

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