ExcelScript.Filter interface

Package:

ExcelScript

Manages the filtering of a table's column.

Remarks

Examples

/**
 * This script adds a table filter to only show the top 10% of values 
 * belonging to a particular column.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the first table on the current worksheet.
    const table = workbook.getActiveWorksheet().getTables()[0];

    // Get the filter for the "PageViews" table column.
    const pageViewFilter : ExcelScript.Filter = table.getColumnByName("PageViews").getFilter();

    // Apply a filter to only show the rows with the top 10% of values in this column.
    pageViewFilter.applyTopPercentFilter(10);
}

Methods

apply(criteria)	Apply the given filter criteria on the given column.
applyBottomItemsFilter(count)	Apply a "Bottom Item" filter to the column for the given number of elements.
applyBottomPercentFilter(percent)	Apply a "Bottom Percent" filter to the column for the given percentage of elements.
applyCellColorFilter(color)	Apply a "Cell Color" filter to the column for the given color.
applyCustomFilter(criteria1, criteria2, oper)	Apply an "Icon" filter to the column for the given criteria strings.
applyDynamicFilter(criteria)	Apply a "Dynamic" filter to the column.
applyFontColorFilter(color)	Apply a "Font Color" filter to the column for the given color.
applyIconFilter(icon)	Apply an "Icon" filter to the column for the given icon.
applyTopItemsFilter(count)	Apply a "Top Item" filter to the column for the given number of elements.
applyTopPercentFilter(percent)	Apply a "Top Percent" filter to the column for the given percentage of elements.
applyValuesFilter(values)	Apply a "Values" filter to the column for the given values.
clear()	Clear the filter on the given column.
getCriteria()	The currently applied filter on the given column.

Method Details

apply(criteria)

Apply the given filter criteria on the given column.

apply(criteria: FilterCriteria): void;

Parameters

criteria

ExcelScript.FilterCriteria

The criteria to apply.

Returns

void

applyBottomItemsFilter(count)

Apply a "Bottom Item" filter to the column for the given number of elements.

applyBottomItemsFilter(count: number): void;

Parameters

count

number

The number of elements from the bottom to show.

Returns

void

applyBottomPercentFilter(percent)

Apply a "Bottom Percent" filter to the column for the given percentage of elements.

applyBottomPercentFilter(percent: number): void;

Parameters

percent

number

The percentage of elements from the bottom to show.

Returns

void

applyCellColorFilter(color)

Apply a "Cell Color" filter to the column for the given color.

applyCellColorFilter(color: string): void;

Parameters

color

string

The background color of the cells to show.

Returns

void

applyCustomFilter(criteria1, criteria2, oper)

Apply an "Icon" filter to the column for the given criteria strings.

applyCustomFilter(
            criteria1: string,
            criteria2?: string,
            oper?: FilterOperator
        ): void;

Parameters

criteria1

string

The first criteria string.

criteria2

string

Optional. The second criteria string.

oper

ExcelScript.FilterOperator

Optional. The operator that describes how the two criteria are joined.

Returns

void

Examples

/**
 * The script filters rows from a table based on numerical values.
 */ 
function main(workbook: ExcelScript.Workbook) {
  // Get the first table in the current worksheet.
  const currentSheet = workbook.getActiveWorksheet();
  const table = currentSheet.getTables()[0];

  // Filter to only show rows with values in the "Sales" column that are 
  // greater than or equal to 2000.
  table.getColumnByName("Sales").getFilter().applyCustomFilter(">=2000");
}

applyDynamicFilter(criteria)

Apply a "Dynamic" filter to the column.

applyDynamicFilter(criteria: DynamicFilterCriteria): void;

Parameters

criteria

ExcelScript.DynamicFilterCriteria

The dynamic criteria to apply.

Returns

void

Examples

/**
 * This script applies a filter to a table that filters it 
 * to only show rows with dates from the previous month.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the table named "ReportTable".
  const table = workbook.getTable("ReportTable");

  // Get the column with the header "Date".
  const dateColumn = table.getColumnByName("Date");

  // Apply a dynamic filter to the column. 
  // `lastMonth` will only show rows with a date from the previous month.
  dateColumn.getFilter().applyDynamicFilter(ExcelScript.DynamicFilterCriteria.lastMonth);
}

applyFontColorFilter(color)

Apply a "Font Color" filter to the column for the given color.

applyFontColorFilter(color: string): void;

Parameters

color

string

The font color of the cells to show.

Returns

void

applyIconFilter(icon)

Apply an "Icon" filter to the column for the given icon.

applyIconFilter(icon: Icon): void;

Parameters

icon

ExcelScript.Icon

The icons of the cells to show.

Returns

void

applyTopItemsFilter(count)

Apply a "Top Item" filter to the column for the given number of elements.

applyTopItemsFilter(count: number): void;

Parameters

count

number

The number of elements from the top to show.

Returns

void

applyTopPercentFilter(percent)

Apply a "Top Percent" filter to the column for the given percentage of elements.

applyTopPercentFilter(percent: number): void;

Parameters

percent

number

The percentage of elements from the top to show.

Returns

void

applyValuesFilter(values)

Apply a "Values" filter to the column for the given values.

applyValuesFilter(values: Array<string | FilterDatetime>): void;

Parameters

values

Array<string | ExcelScript.FilterDatetime>

The list of values to show. This must be an array of strings or an array of ExcelScript.FilterDateTime objects.

Returns

void

Examples

/**
 * This script applies a filter to a table so that it only shows rows with "Needs Review" in the "Type" column.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first table in the workbook.
  const table = workbook.getTables()[0];

  // Apply the filter to the "Type" column.
  const typeColumn = table.getColumnByName("Type");
  typeColumn.getFilter().applyValuesFilter(["Needs Review"]);
}

clear()

Clear the filter on the given column.

clear(): void;

Returns

void

Examples

/**
 * This script shows how to clear a filter from a table column.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first table in the workbook.
  const table = workbook.getTables()[0];

  // Clear the filter for the table column named "Status".
  const statusColumnFilter = table.getColumn("Status").getFilter();
  statusColumnFilter.clear();
}

getCriteria()

The currently applied filter on the given column.

getCriteria(): FilterCriteria;

Returns

ExcelScript.FilterCriteria