Class FilterCriteriaBuilder

FilterCriteriaBuilder

Builder for FilterCriteria.

Methods

MethodReturn typeBrief description
build()FilterCriteriaConstructs a filter criteria from the settings supplied to the builder.
copy()FilterCriteriaBuilderCreates a builder for a filter criteria based on this filter criteria's settings.
getCriteriaType()BooleanCriteriaReturns 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()ColorReturns the background color used as a filter criteria.
getVisibleForegroundColor()ColorReturns the foreground color used as a filter criteria.
getVisibleValues()String[]Returns the values to show.
setHiddenValues(values)FilterCriteriaBuilderSets the values to hide.
setVisibleBackgroundColor(visibleBackgroundColor)FilterCriteriaBuilderSets the background color used as a filter criteria.
setVisibleForegroundColor(visibleForegroundColor)FilterCriteriaBuilderSets the foreground color used as a filter criteria.
setVisibleValues(values)FilterCriteriaBuilderSets the values to show.
whenCellEmpty()FilterCriteriaBuilderSets the filter criteria to show cells where the cell is empty.
whenCellNotEmpty()FilterCriteriaBuilderSets the filter criteria to show cells where the cell is not empty.
whenDateAfter(date)FilterCriteriaBuilderSets the filter criteria to show cells where the cell date is after the specified value.
whenDateAfter(date)FilterCriteriaBuilderSets the filter criteria to show cells where the cell date is after the specified relative date.
whenDateBefore(date)FilterCriteriaBuilderSets the filter criteria to show cells where the cell date is before the specified date.
whenDateBefore(date)FilterCriteriaBuilderSets the filter criteria to show cells where a cell date is before the specified relative date.
whenDateEqualTo(date)FilterCriteriaBuilderSets the filter criteria to show cells where a cell date is equal to the specified date.
whenDateEqualTo(date)FilterCriteriaBuilderSets the filter criteria to show cells where a cell date is equal to the specified relative date.
whenDateEqualToAny(dates)FilterCriteriaBuilderSets the filter criteria to show cells where the cell date is equal to any of the specified dates.
whenDateNotEqualTo(date)FilterCriteriaBuilderSets the filter criteria to show cells where the cell date is not equal to the specified date.
whenDateNotEqualToAny(dates)FilterCriteriaBuilderSets the filter criteria to show cells where the cell date is not equal to any of the specified dates.
whenFormulaSatisfied(formula)FilterCriteriaBuilderSets the filter criteria to show cells where the specified formula (such as =B:B<C:C) evaluates to true.
whenNumberBetween(start, end)FilterCriteriaBuilderSets the filter criteria to show cells where the cell number falls between, or is either of, two specified numbers.
whenNumberEqualTo(number)FilterCriteriaBuilderSets the filter criteria to show cells where the cell number is equal to the specified value.
whenNumberEqualToAny(numbers)FilterCriteriaBuilderSets the filter criteria to show cells where the cell number is equal to any of the specified numbers.
whenNumberGreaterThan(number)FilterCriteriaBuilderSets the filter criteria to show cells where the cell number is greater than the specified value.
whenNumberGreaterThanOrEqualTo(number)FilterCriteriaBuilderSets the filter criteria to show cells where the cell number is greater than or equal to the specified value.
whenNumberLessThan(number)FilterCriteriaBuilderSets the conditional conditional format rule to show cells where the cell number is less than the specified value.
whenNumberLessThanOrEqualTo(number)FilterCriteriaBuilderSets the filter criteria to show cells where the cell number is less than or equal to the specified value.
whenNumberNotBetween(start, end)FilterCriteriaBuilderSets the filter criteria to show cells where the cell number doesn't fall between, and is neither of, two specified numbers.
whenNumberNotEqualTo(number)FilterCriteriaBuilderSets the filter criteria to show cells where the cell number is not equal to the specified value.
whenNumberNotEqualToAny(numbers)FilterCriteriaBuilderSets the filter criteria to show cells where the cell number isn't equal to any of the specified numbers.
whenTextContains(text)FilterCriteriaBuilderSets the filter criteria to show cells where the cell text contains the specified text.
whenTextDoesNotContain(text)FilterCriteriaBuilderSets the filter criteria to show cells where the cell text doesn't contain the specified text.
whenTextEndsWith(text)FilterCriteriaBuilderSets the filter criteria to show cells where the cell text ends with the specified text.
whenTextEqualTo(text)FilterCriteriaBuilderSets the filter criteria to show cells where the cell text is equal to the specified text.
whenTextEqualToAny(texts)FilterCriteriaBuilderSets the filter criteria to show cells where the cell text is equal to any of the specified values.
whenTextNotEqualTo(text)FilterCriteriaBuilderSets the filter criteria to show cells where the cell text is not equal to the specified text.
whenTextNotEqualToAny(texts)FilterCriteriaBuilderSets the filter criteria to show cells where the cell text is not equal to any of the specified values.
whenTextStartsWith(text)FilterCriteriaBuilderSets the filter criteria to show cells where the cell text starts with the specified text.
withCriteria(criteria, args)FilterCriteriaBuilderSets 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.

You can use this method with any type of filter.

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).

You can use this method for any type of filter.

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).

You can use this method for any type of filter.

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.

This criteria is for Grid sheet filters. Grid sheets are sheets with data that aren't connected to a database. Returns an empty array for other types of filters.

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. This criteria is for filters applied to sheets within a spreadsheet. Returns null if you call this method for other types of filters.

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.

This criteria is for Grid sheet filters. Grid sheets are sheets with data that aren't connected to a database. Returns null for other types of filters.

Return

Color — The foreground color used as a filter criteria.


getVisibleValues()

Returns the values to show.

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

Return

String[] — An array of values to show.


setHiddenValues(values)

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

You can use this filter criteria for Grid sheet filters. Grid sheets are sheets with data that aren't connected to a database.

Parameters

NameTypeDescription
valuesString[]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.

You can use this filter criteria for Grid sheet filters. Grid sheets are sheets with data that aren't connected to a database.

Parameters

NameTypeDescription
visibleBackgroundColorColorThe 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.

You can use this filter criteria for Grid sheet filters. Grid sheets are sheets with data that aren't connected to a database.

Parameters

NameTypeDescription
visibleForegroundColorColorThe 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.

You can use this criteria for filters on pivot tables that aren't connected to a database.

Parameters

NameTypeDescription
valuesString[]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.

You can use this criteria with any type of filter.

Return

FilterCriteriaBuilder — This builder, for chaining.


whenCellNotEmpty()

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

You can use this criteria with any type of filter.

Return

FilterCriteriaBuilder — This builder, for chaining.


whenDateAfter(date)

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

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a date. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a date, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
dateDateThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a date. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a date, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
dateRelativeDateThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a date. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a date, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
dateDateThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a date. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a date, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
dateRelativeDateThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a date. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a date, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
dateDateThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a date. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a date, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
dateRelativeDateThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a date. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a date, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
datesDate[]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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a date. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a date, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
dateDateThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a date. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a date, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
datesDate[]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.

You can only use this criteria to filter data that isn't connected to a database.

Parameters

NameTypeDescription
formulaStringA 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 falls between, or is either of, two specified numbers.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a number. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a number, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
startNumberThe lowest acceptable value.
endNumberThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a number. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a number, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
numberNumberThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a number. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a number, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
numbersNumber[]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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a number. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a number, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
numberNumberThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a number. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a number, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
numberNumberThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a number. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a number, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
numberNumberThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a number. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a number, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
numberNumberThe highest acceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.


whenNumberNotBetween(start, end)

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

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a number. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a number, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
startNumberThe lowest unacceptable value.
endNumberThe 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.

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a number. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a number, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
numberNumberThe sole unacceptable value.

Return

FilterCriteriaBuilder — This builder, for chaining.


whenNumberNotEqualToAny(numbers)

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

You can use this criteria with any type of filter. If you use this criteria with data that's connected to a database, the data type of the column you're filtering by must be a number. If the data isn't connected to a database, the data type of the column you're filtering by doesn't need to be a number, but if it's not, you might get unexpected results.

Parameters

NameTypeDescription
numbersNumber[]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.

You can use this criteria with any type of filter.

Parameters

NameTypeDescription
textStringThe 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 doesn't contain the specified text.

You can use this criteria with any type of filter.

Parameters

NameTypeDescription
textStringThe 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.

You can use this criteria with any type of filter.

Parameters

NameTypeDescription
textStringText 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.

You can use this criteria with any type of filter.

Parameters

NameTypeDescription
textStringThe 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.

You can use this criteria with any type of filter.

Parameters

NameTypeDescription
textsString[]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.

You can use this criteria with any type of filter.

Parameters

NameTypeDescription
textStringThe 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.

You can use this criteria with any type of filter.

Parameters

NameTypeDescription
textsString[]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.

You can use this criteria with any type of filter.

Parameters

NameTypeDescription
textStringText 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.

You can use this criteria with any type of filter, but some BooleanCriteria aren't applicable for all filters.

// 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

NameTypeDescription
criteriaBooleanCriteriaThe type of boolean criteria.
argsObject[]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.