ExcelScript.FilterPivotHierarchy interface

Package:

ExcelScript

Represents the Excel FilterPivotHierarchy.

Remarks

Examples

/**
 * This script creates a PivotTable with a filter.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the two worksheets to use in PivotTable creation.
  const dataSheet = workbook.getWorksheet("Data");
  const pivotSheet = workbook.getWorksheet("Pivot");

  // Create a new PivotTable.
  const newPivot = pivotSheet.addPivotTable(
    "My PivotTable", 
    dataSheet.getUsedRange(), 
    pivotSheet.getRange("A1"));

  // Add a filter with the Quarter field.
  const filter: ExcelScript.FilterPivotHierarchy = 
    newPivot.addFilterHierarchy(newPivot.getHierarchy("Quarter"));

  // Add other hierarchies...
}

Methods

getEnableMultipleFilterItems()	Determines whether to allow multiple filter items.
getFields()	Returns the PivotFields associated with the FilterPivotHierarchy.
getId()	ID of the FilterPivotHierarchy.
getName()	Name of the FilterPivotHierarchy.
getPivotField(name)	Gets a PivotField by name. If the PivotField does not exist, then this method returns undefined.
getPosition()	Position of the FilterPivotHierarchy.
setEnableMultipleFilterItems(enableMultipleFilterItems)	Determines whether to allow multiple filter items.
setName(name)	Name of the FilterPivotHierarchy.
setPosition(position)	Position of the FilterPivotHierarchy.
setToDefault()	Reset the FilterPivotHierarchy back to its default values.

Method Details

getEnableMultipleFilterItems()

Determines whether to allow multiple filter items.

getEnableMultipleFilterItems(): boolean;

Returns

boolean

getFields()

Returns the PivotFields associated with the FilterPivotHierarchy.

getFields(): PivotField[];

Returns

ExcelScript.PivotField[]

getId()

ID of the FilterPivotHierarchy.

getId(): string;

Returns

string

getName()

Name of the FilterPivotHierarchy.

getName(): string;

Returns

string

Examples

/**
 * This script logs the names of all the filter hierarchies in a PivotTable.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the first PivotTable in the workbook.
    const pivotTable = workbook.getPivotTables()[0];

    // For each pivot filter, log its name.
    pivotTable.getFilterHierarchies().forEach((filter: ExcelScript.FilterPivotHierarchy) => {
      console.log(filter.getName());
    });
}

getPivotField(name)

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

getPivotField(name: string): PivotField | undefined;

Parameters

name

string

Name of the PivotField to be retrieved.

Returns

ExcelScript.PivotField | undefined

getPosition()

Position of the FilterPivotHierarchy.

getPosition(): number;

Returns

number

setEnableMultipleFilterItems(enableMultipleFilterItems)

Determines whether to allow multiple filter items.

setEnableMultipleFilterItems(enableMultipleFilterItems: boolean): void;

Parameters

enableMultipleFilterItems

boolean

Returns

void

setName(name)

Name of the FilterPivotHierarchy.

setName(name: string): void;

Parameters

name

string

Returns

void

setPosition(position)

Position of the FilterPivotHierarchy.

setPosition(position: number): void;

Parameters

position

number

Returns

void

setToDefault()

Reset the FilterPivotHierarchy back to its default values.

setToDefault(): void;

Returns

void