ExcelScript.FilterCriteria interface

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

dynamicCriteria

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

dynamicCriteria?: DynamicFilterCriteria;

Property Value

filterOn

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

filterOn: FilterOn;

Property Value

icon

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

icon?: Icon;

Property Value

operator

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

operator?: FilterOperator;

Property Value

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>