Class FilterCriteriaBuilder

Builder for FilterCriteria.

Methods

Method	Return type	Brief description
`build()`	`FilterCriteria`	Constructs a filter criteria from the settings supplied to the builder.
`copy()`	`FilterCriteriaBuilder`	Creates a builder for a filter criteria based on this filter criteria's settings.
`getCriteriaType()`	`BooleanCriteria`	Returns the boolean criteria type as defined in the `BooleanCriteria` enum.
`getCriteriaValues()`	`Object[]`	Returns an array of arguments for the boolean criteria.
`getHiddenValues()`	`String[]`	Returns the values to hide.
`getVisibleBackgroundColor()`	`Color`	Returns the background color used as a filter criteria.
`getVisibleForegroundColor()`	`Color`	Returns the foreground color used as a filter criteria.
`getVisibleValues()`	`String[]`	Returns the values to show.
`setHiddenValues(values)`	`FilterCriteriaBuilder`	Sets the values to hide.
`setVisibleBackgroundColor(visibleBackgroundColor)`	`FilterCriteriaBuilder`	Sets the background color used as a filter criteria.
`setVisibleForegroundColor(visibleForegroundColor)`	`FilterCriteriaBuilder`	Sets the foreground color used as a filter criteria.
`setVisibleValues(values)`	`FilterCriteriaBuilder`	Sets the values to show.
`whenCellEmpty()`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell is empty.
`whenCellNotEmpty()`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell is not empty.
`whenDateAfter(date)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell date is after the specified value.
`whenDateAfter(date)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell date is after the specified relative date.
`whenDateBefore(date)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell date is before the specified date.
`whenDateBefore(date)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where a cell date is before the specified relative date.
`whenDateEqualTo(date)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where a cell date is equal to the specified date.
`whenDateEqualTo(date)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where a cell date is equal to the specified relative date.
`whenDateEqualToAny(dates)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell date is equal to any of the specified dates.
`whenDateNotEqualTo(date)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell date is not equal to the specified date.
`whenDateNotEqualToAny(dates)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell date is not equal to any of the specified dates.
`whenFormulaSatisfied(formula)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the specified formula (such as `=B:B<C:C`) evaluates to `true`.
`whenNumberBetween(start, end)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell number is falls between, or is either of, two specified numbers.
`whenNumberEqualTo(number)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell number is equal to the specified value.
`whenNumberEqualToAny(numbers)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell number is equal to any of the specified numbers.
`whenNumberGreaterThan(number)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell number is greater than the specified value.
`whenNumberGreaterThanOrEqualTo(number)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell number is greater than or equal to the specified value.
`whenNumberLessThan(number)`	`FilterCriteriaBuilder`	Sets the conditional conditional format rule to show cells where the cell number is less than the specified value.
`whenNumberLessThanOrEqualTo(number)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell number is less than or equal to the specified value.
`whenNumberNotBetween(start, end)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell number does not fall between, and is neither of, two specified numbers.
`whenNumberNotEqualTo(number)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell number is not equal to the specified value.
`whenNumberNotEqualToAny(numbers)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell number is not equal to any of the specified numbers.
`whenTextContains(text)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell text contains the specified text.
`whenTextDoesNotContain(text)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell text does not contain the specified text.
`whenTextEndsWith(text)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell text ends with the specified text.
`whenTextEqualTo(text)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell text is equal to the specified text.
`whenTextEqualToAny(texts)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell text is equal to any of the specified values.
`whenTextNotEqualTo(text)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell text is not equal to the specified text.
`whenTextNotEqualToAny(texts)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell text is not equal to any of the specified values.
`whenTextStartsWith(text)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the cell text starts with the specified text.
`withCriteria(criteria, args)`	`FilterCriteriaBuilder`	Sets the boolean criteria to criteria defined by `BooleanCriteria` values, typically taken from the `criteria` and `arguments` of an existing criteria.

Detailed documentation

`build()`

Constructs a filter criteria from the settings supplied to the builder.

Return

FilterCriteria — A representation of the filter criteria.

`copy()`

Creates a builder for a filter criteria based on this filter criteria's settings.

Return

FilterCriteriaBuilder — a filter criteria builder based on this filter criteria

`getCriteriaType()`

Returns the boolean criteria type as defined in the BooleanCriteria enum. To get the arguments for the criteria, use getCriteriaValues(). To use these values to create or modify a filter criteria, see withCriteria(criteria, args).

Return

BooleanCriteria — the type of boolean criteria

`getCriteriaValues()`

Returns an array of arguments for the boolean criteria. To get the boolean criteria type, use getCriteriaType(). To use these values to create or modify a filter criteria, see withCriteria(criteria, args).

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 to hide.

Return

String[] — an array of values to hide

`getVisibleBackgroundColor()`

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

Return

Color — The background color used as a filter criteria.

`getVisibleForegroundColor()`

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

Return

Color — The foreground color used as a filter criteria.

`getVisibleValues()`

Returns the values to show.

Return

String[] — an array of values to show

`setHiddenValues(values)`

Sets the values to hide. Clears any existing visible or hidden values.

Parameters

Name	Type	Description
`values`	`String[]`	The list of values to hide.

Return

FilterCriteriaBuilder — This builder, for chaining.

`setVisibleBackgroundColor(visibleBackgroundColor)`

Sets the background color used as a filter criteria. Cells with this background color remain visible. Setting a background color filter criteria removes the current color filter criteria from this builder.

Parameters

Name	Type	Description
`visibleBackgroundColor`	`Color`	The background color to set. This must be an RGB-style color, theme colors are not supported.

Return

FilterCriteriaBuilder — This builder, for chaining.

`setVisibleForegroundColor(visibleForegroundColor)`

Sets the foreground color used as a filter criteria. Cells with this foreground color remain visible. Setting a foreground color filter criteria removes the current color filter criteria from this builder.

Parameters

Name	Type	Description
`visibleForegroundColor`	`Color`	The foreground color to set. This must be an RGB-style color, theme colors are not supported.

Return

FilterCriteriaBuilder — This builder, for chaining.

`setVisibleValues(values)`

Sets the values to show. It clears any existing visible or hidden values.

Parameters

Name	Type	Description
`values`	`String[]`	The list of values to show.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenCellEmpty()`

Sets the filter criteria to show cells where the cell is empty.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenCellNotEmpty()`

Sets the filter criteria to show cells where the cell is not empty.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenDateAfter(date)`

Sets the filter criteria to show cells where the cell date is after the specified value.

Parameters

Name	Type	Description
`date`	`Date`	The latest date.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenDateAfter(date)`

Sets the filter criteria to show cells where the cell date is after the specified relative date.

Parameters

Name	Type	Description
`date`	`RelativeDate`	The latest date relative to the date type selected.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenDateBefore(date)`

Sets the filter criteria to show cells where the cell date is before the specified date.

Parameters

Name	Type	Description
`date`	`Date`	The earliest unacceptable date.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenDateBefore(date)`

Sets the filter criteria to show cells where a cell date is before the specified relative date.

Parameters

Name	Type	Description
`date`	`RelativeDate`	The latest date relative to the date type selected.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenDateEqualTo(date)`

Sets the filter criteria to show cells where a cell date is equal to the specified date.

Parameters

Name	Type	Description
`date`	`Date`	The sole acceptable date.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenDateEqualTo(date)`

Sets the filter criteria to show cells where a cell date is equal to the specified relative date.

Parameters

Name	Type	Description
`date`	`RelativeDate`	The latest date relative to the date type selected.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenDateEqualToAny(dates)`

Sets the filter criteria to show cells where the cell date is equal to any of the specified dates.

Parameters

Name	Type	Description
`dates`	`Date[]`	The acceptable values.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenDateNotEqualTo(date)`

Sets the filter criteria to show cells where the cell date is not equal to the specified date.

Parameters

Name	Type	Description
`date`	`Date`	The latest date relative to the date type selected.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenDateNotEqualToAny(dates)`

Sets the filter criteria to show cells where the cell date is not equal to any of the specified dates.

Parameters

Name	Type	Description
`dates`	`Date[]`	The acceptable values.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenFormulaSatisfied(formula)`

Sets the filter criteria to show cells where the specified formula (such as =B:B<C:C) evaluates to true.

Parameters

Name	Type	Description
`formula`	`String`	A custom formula that evaluates to `true` if the input is valid.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenNumberBetween(start, end)`

Sets the filter criteria to show cells where the cell number is falls between, or is either of, two specified numbers.

Parameters

Name	Type	Description
`start`	`Number`	The lowest acceptable value.
`end`	`Number`	The highest acceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenNumberEqualTo(number)`

Sets the filter criteria to show cells where the cell number is equal to the specified value.

Parameters

Name	Type	Description
`number`	`Number`	The sole acceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenNumberEqualToAny(numbers)`

Sets the filter criteria to show cells where the cell number is equal to any of the specified numbers.

Parameters

Name	Type	Description
`numbers`	`Number[]`	The acceptable values.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenNumberGreaterThan(number)`

Sets the filter criteria to show cells where the cell number is greater than the specified value.

Parameters

Name	Type	Description
`number`	`Number`	The highest unacceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenNumberGreaterThanOrEqualTo(number)`

Sets the filter criteria to show cells where the cell number is greater than or equal to the specified value.

Parameters

Name	Type	Description
`number`	`Number`	The lowest acceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenNumberLessThan(number)`

Sets the conditional conditional format rule to show cells where the cell number is less than the specified value.

Parameters

Name	Type	Description
`number`	`Number`	The lowest unacceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenNumberLessThanOrEqualTo(number)`

Sets the filter criteria to show cells where the cell number is less than or equal to the specified value.

Parameters

Name	Type	Description
`number`	`Number`	The highest acceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenNumberNotBetween(start, end)`

Sets the filter criteria to show cells where the cell number does not fall between, and is neither of, two specified numbers.

Parameters

Name	Type	Description
`start`	`Number`	The lowest unacceptable value.
`end`	`Number`	The highest unacceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenNumberNotEqualTo(number)`

Sets the filter criteria to show cells where the cell number is not equal to the specified value.

Parameters

Name	Type	Description
`number`	`Number`	The sole unacceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenNumberNotEqualToAny(numbers)`

Sets the filter criteria to show cells where the cell number is not equal to any of the specified numbers.

Parameters

Name	Type	Description
`numbers`	`Number[]`	The unacceptable values.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenTextContains(text)`

Sets the filter criteria to show cells where the cell text contains the specified text.

Parameters

Name	Type	Description
`text`	`String`	The value that the input must contain.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenTextDoesNotContain(text)`

Sets the filter criteria to show cells where the cell text does not contain the specified text.

Parameters

Name	Type	Description
`text`	`String`	The value that the input must not contain.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenTextEndsWith(text)`

Sets the filter criteria to show cells where the cell text ends with the specified text.

Parameters

Name	Type	Description
`text`	`String`	Text to compare against the end of the string.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenTextEqualTo(text)`

Sets the filter criteria to show cells where the cell text is equal to the specified text.

Parameters

Name	Type	Description
`text`	`String`	The sole acceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenTextEqualToAny(texts)`

Sets the filter criteria to show cells where the cell text is equal to any of the specified values.

Parameters

Name	Type	Description
`texts`	`String[]`	The acceptable values.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenTextNotEqualTo(text)`

Sets the filter criteria to show cells where the cell text is not equal to the specified text.

Parameters

Name	Type	Description
`text`	`String`	The unacceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenTextNotEqualToAny(texts)`

Sets the filter criteria to show cells where the cell text is not equal to any of the specified values.

Parameters

Name	Type	Description
`texts`	`String[]`	The unacceptable values.

Return

FilterCriteriaBuilder — This builder, for chaining.

`whenTextStartsWith(text)`

Sets the filter criteria to show cells where the cell text starts with the specified text.

Parameters

Name	Type	Description
`text`	`String`	Text to compare against the beginning of the string.

Return

FilterCriteriaBuilder — This builder, for chaining.

`withCriteria(criteria, args)`

Sets the boolean criteria to criteria defined by BooleanCriteria values, typically taken from the criteria and arguments of an existing criteria.

// Builds a filter criteria that is based on an existing criteria.

var sheet = SpreadsheetApp.getActiveSheet();
var criteria = sheet.getFilter().getColumnFilterCriteria(1);
var newCriteria = SpreadsheetApp.newFilterCriteria()
    .withCriteria(criteria.getCriteriaType(), criteria.getCriteriaValues())
    .setHiddenValues(['c'])
    .build();

Parameters

Name	Type	Description
`criteria`	`BooleanCriteria`	The type of boolean criteria.
`args`	`Object[]`	An array of arguments appropriate to the criteria type; the number of arguments and their type match the corresponding `when...()` method above.

Return

FilterCriteriaBuilder — This builder, for chaining.