Class FilterCriteria

FilterCriteria

Use this class to get information about or copy the criteria for existing filters.

Common uses

Copy criteria

The following sample gets the filter that applies to the range A1:C20, gets the criteria applied to column C, and copies the criteria to column B.
let ss = SpreadsheetApp.getActiveSheet();
let range = ss.getRange("A1:C20");
// Copies the filter criteria applied to column C.
let filter = range.getFilter();
let criteria = filter.getColumnFilterCriteria(3).copy().build();
// Applies the copied criteria to column B. The copied criteria overwrites any existing
// criteria on column B.
filter.setColumnFilterCriteria(2, criteria);

Get values hidden by the filter

The following sample gets the filter that applies to the given range and logs the values from column B that the filter hides.
let ss = SpreadsheetApp.getActiveSheet();
let range = ss.getRange("A1:C20");
let filter = range.getFilter();
// Gets the filter criteria applied to column B, then gets the hidden values.
let filterCriteria = filter.getColumnFilterCriteria(2).getHiddenValues();
// Logs the hidden values.
console.log(filterCriteria);

Methods

MethodReturn typeBrief description
copy()FilterCriteriaBuilderCopies this filter criteria and creates a criteria builder that you can apply to another filter.
getCriteriaType()BooleanCriteriaReturns the criteria's boolean type, for example, CELL_EMPTY.
getCriteriaValues()Object[]Returns an array of arguments for boolean criteria.
getHiddenValues()String[]Returns the values that the filter hides.
getVisibleBackgroundColor()ColorReturns the background color used as filter criteria.
getVisibleForegroundColor()ColorReturns the foreground color used as a filter criteria.
getVisibleValues()String[]Returns the values that the pivot table filter shows.

Detailed documentation

copy()

Copies this filter criteria and creates a criteria builder that you can apply to another filter.

You can use this method with any type of filter. If you're using a sheet filter, you can copy the criteria to another column.

let ss = SpreadsheetApp.getActiveSheet();
let filter = ss.getFilter();
// Makes a copy of the filter criteria applied to column C.
let criteria = filter.getColumnFilterCriteria(3).copy().build();
// Applies the copied criteria to column B. The copied criteria overwrites any existing
// criteria on column B.
filter.setColumnFilterCriteria(2, criteria);

Return

FilterCriteriaBuilder — A filter criteria builder based on this filter criteria.


getCriteriaType()

Returns the criteria's boolean type, for example, CELL_EMPTY. To learn about the types of boolean criteria, see the BooleanCriteria enum.

People often use this method to add criteria to a filter without replacing existing criteria.

You can use this method for any type of filter. If the filter criteria isn't a boolean condition, returns null.

let ss = SpreadsheetApp.getActiveSheet();
// Gets the filter on the active sheet.
let filter = ss.getFilter();
// Gets the criteria type and returns a string representing the criteria type object.
let criteriaType = filter.getColumnFilterCriteria(2)
                         .getCriteriaType()
                         .toString();
// Logs the criteria type.
console.log(criteriaType);

Return

BooleanCriteria — The type of boolean criteria, or null if the criteria isn't a boolean condition.


getCriteriaValues()

Returns an array of arguments for boolean criteria. Some boolean criteria types don't have arguments and return an empty array, for example, CELL_NOT_EMPTY.

People often use this method to add criteria to a filter without replacing existing criteria.

  • To get the boolean criteria type, use getCriteriaType().
  • To use the criteria type and criteria values to create or modify a filter criteria, see FilterCriteriaBuilder.withCriteria(criteria, args).

    You can use this method for any type of filter.

    let ss = SpreadsheetApp.getActiveSheet();
    let filter = ss.getFilter();
    // Gets the values of the boolean criteria and logs them. For example, if the boolean
    // condition is whenNumberGreaterThan(10), then the logged value is 10.
    let criteriaValues = filter.getColumnFilterCriteria(2).getCriteriaValues();
    console.log(criteriaValues);

    Return

    Object[] — An array of arguments appropriate to the boolean criteria type. The number of arguments and their type match the corresponding when...() method of the FilterCriteriaBuilder class.


getHiddenValues()

Returns the values that the filter hides.

Use this criteria with filters on Grid sheets, the default type of sheet. Grid sheets are sheets that aren't connected to a database. Returns null if you call this method for other types of filters.

let ss = SpreadsheetApp.getActiveSheet();
let range = ss.getRange("A1:C20");
let filter = range.getFilter();
// Gets the filter criteria applied to column B, then gets the hidden values.
let filterCriteria = filter.getColumnFilterCriteria(2).getHiddenValues();
// Logs the hidden values.
console.log(filterCriteria);

Return

String[] — An array of values that the filter hides.


getVisibleBackgroundColor()

Returns the background color used as filter criteria. Cells with this background color remain visible.

Use this criteria with filters on Grid sheets, the default type of sheet. Grid sheets are sheets that aren't connected to a database. Returns null if you call this method for other types of filters.

let ss = SpreadsheetApp.getActiveSheet();
let range = ss.getRange("A1:C20");
// Logs the background color that column B is filtered by as a hexadecimal string.
let filter = range.getFilter();
let color = filter.getColumnFilterCriteria(2)
                  .getVisibleBackgroundColor()
                  .asRgbColor()
                  .asHexString();
console.log(color);

Return

Color — The background color used as filter criteria.


getVisibleForegroundColor()

Returns the foreground color used as a filter criteria. Cells with this foreground color remain visible.

Use this criteria with filters on Grid sheets, the default type of sheet. Grid sheets are sheets that aren't connected to a database. Returns null if you call this method for other types of filters.

let ss = SpreadsheetApp.getActiveSheet();
let range = ss.getRange("A1:C20");
// Logs the foreground color that column B is filtered by as a hexadecimal string.
let filter = range.getFilter();
let color = filter.getColumnFilterCriteria(2)
                  .getVisibleForegroundColor()
                  .asRgbColor()
                  .asHexString();
console.log(color);

Return

Color — The foreground color used as a filter criteria.


getVisibleValues()

Returns the values that the pivot table filter shows.

This criteria is only for filters on pivot tables that aren't connected to a database. Returns an empty array for other types of filters.

let ss = SpreadsheetApp.getActiveSheet();
// Gets the first pivot table on the sheet, then gets the visible values of its first filter.
pivotTable = ss.getPivotTables()[0];
pivotFilterValues = pivotTable.getFilters()[0].getFilterCriteria().getVisibleValues();
// Logs the visible values.
console.log(pivotFilterValues);

Return

String[] — An array of values that the pivot table filter shows.