ExcelScript.FilterCriteria interface

Package:

ExcelScript

Represents the filtering criteria applied to a column.

Properties

color	The HTML color string used to filter cells. Used with cellColor and fontColor filtering.
criterion1	The first criterion used to filter data. Used as an operator in the case of custom filtering. For example ">50" for numbers greater than 50, or "=*s" for values ending in "s". Used as a number in the case of top/bottom items/percents (e.g., "5" for the top 5 items if filterOn is set to topItems).
criterion2	The second criterion used to filter data. Only used as an operator in the case of custom filtering.
dynamicCriteria	The dynamic criteria from the ExcelScript.DynamicFilterCriteria set to apply on this column. Used with dynamic filtering.
filterOn	The property used by the filter to determine whether the values should stay visible.
icon	The icon used to filter cells. Used with icon filtering.
operator	The operator used to combine criterion 1 and 2 when using custom filtering.
subField	The property used by the filter to do a rich filter on rich values.
values	The set of values to be used as part of values filtering.

Property Details

color

The HTML color string used to filter cells. Used with cellColor and fontColor filtering.

color?: string;

Property Value

string

criterion1

The first criterion used to filter data. Used as an operator in the case of custom filtering. For example ">50" for numbers greater than 50, or "=*s" for values ending in "s".

Used as a number in the case of top/bottom items/percents (e.g., "5" for the top 5 items if filterOn is set to topItems).

criterion1?: string;

Property Value

string

Examples

/**
 * This script creates an autoFilter on the worksheet that filters out rows based on column values. 
 * The autoFilter filters to only include rows that have a value in column C in the lowest 10 values 
 * (of column C values).
 */
function main(workbook: ExcelScript.Workbook) {
  const currentSheet = workbook.getActiveWorksheet();
  const dataRange = currentSheet.getUsedRange();

  // Add a filter that will only show the rows with the lowest 10 values in column C
  // (index 2, assuming the used range spans from at least A:C).
  const filterCriteria: ExcelScript.FilterCriteria = {
    criterion1: "10",
    filterOn: ExcelScript.FilterOn.bottomItems
  };
  currentSheet.getAutoFilter().apply(dataRange, 2, filterCriteria);
}

criterion2

The second criterion used to filter data. Only used as an operator in the case of custom filtering.

criterion2?: string;

Property Value

string

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

dynamicCriteria

The dynamic criteria from the ExcelScript.DynamicFilterCriteria set to apply on this column. Used with dynamic filtering.

dynamicCriteria?: DynamicFilterCriteria;

Property Value

ExcelScript.DynamicFilterCriteria

filterOn

The property used by the filter to determine whether the values should stay visible.

filterOn: FilterOn;

Property Value

ExcelScript.FilterOn

icon

The icon used to filter cells. Used with icon filtering.

icon?: Icon;

Property Value

ExcelScript.Icon

operator

The operator used to combine criterion 1 and 2 when using custom filtering.

operator?: FilterOperator;

Property Value

ExcelScript.FilterOperator

subField

The property used by the filter to do a rich filter on rich values.

subField?: string;

Property Value

string

values

The set of values to be used as part of values filtering.

values?: Array<string | FilterDatetime>;

Property Value

Array<string | ExcelScript.FilterDatetime>