ExcelScript.FilterCriteria interface

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

color?: string;

Property Value

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

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

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

criterion2?: string;

Property Value

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

dynamicCriteria?: DynamicFilterCriteria;

Property Value

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

filterOn: FilterOn;

Property Value

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

icon?: Icon;

Property Value

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

operator?: FilterOperator;

Property Value

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

subField?: string;

Property Value

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

values?: Array<string | FilterDatetime>;

Property Value