Class FilterCriteriaBuilder | Apps Script

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.
`getVisibleValues()`	`String[]`	Returns the values to show.
`setHiddenValues(values)`	`FilterCriteriaBuilder`	Sets the values to hide.
`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.
`whenFormulaSatisfied(formula)`	`FilterCriteriaBuilder`	Sets the filter criteria to show cells where the specified formula (such as "=B:Btrue.
`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.
`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.
`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.
`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

`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 — the 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 — the builder, for chaining

`whenCellEmpty()`

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

Return

FilterCriteriaBuilder — the builder, for chaining

`whenCellNotEmpty()`

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

Return

FilterCriteriaBuilder — the 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 — the 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 — the 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 — the 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 — the 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 — the 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 — the builder, for chaining

`whenFormulaSatisfied(formula)`

Sets the filter criteria to show cells where the specified formula (such as "=B:Btrue.

Parameters

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

Return

FilterCriteriaBuilder — the 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 — the 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 — the 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 — the 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 — the 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 — the 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 — the 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 — the 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 — the 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 — the 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 — the 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 — the 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 — the 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 — the 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 — the builder, for chaining