Class FilterCriteriaBuilder

    Filter Criteria Builder

    Builder for filter criteria. To add criteria to a filter, you must do the following:

    1. Create the criteria builder using Spreadsheet App.newFilterCriteria() .
    2. Add settings to the builder using the methods from this class.
    3. Use build() to assemble the criteria with your specified settings.

    Common uses

    Hide values on a sheet

    The following sample gets a sheet's existing filter and adds criteria that hides the cells in column C that contain "hello" or "world." The criteria in this sample can only be used with filters on Grid sheets, the default type of sheet. 
     const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 setHiddenValues 
 ([ 
 'hello' 
 , 
  
 'world' 
 ]) 
  
 . 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 3 
 , 
  
 criteria 
 );

    Only show non-empty cells

    The following sample adds a filter to a Data Source sheet, a sheet that's connected to a database, with criteria that only shows cells in the "Category" column that aren't empty. 
     // Gets the sheet named "Connected sheet," which is connected to a database. 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 () 
  
 . 
 getSheetByName 
 ( 
 'Connected sheet' 
 ) 
  
 . 
 asDataSourceSheet 
 (); 
 // Creates criteria that only shows non-empty cells. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenCellNotEmpty 
 (). 
 build 
 (); 
 // Applies the criteria to the column named "Category." 
 sheet 
 . 
 addFilter 
 ( 
 'Category' 
 , 
  
 criteria 
 );

    Methods

    Method Return type Brief description
    build()
    		Filter Criteria Assembles the filter criteria using the settings you add to the criteria builder.
    copy()
    		Filter Criteria Builder Copies this filter criteria and creates a criteria builder that you can apply to another filter.
    get Criteria Type()
    		Boolean Criteria Returns the criteria's boolean type, for example, CELL_EMPTY .
    get Criteria Values()
    		Object[] Returns an array of arguments for boolean criteria.
    get Hidden Values()
    		String[] Returns the values that the filter hides.
    get Visible Background Color()
    		Color Returns the background color used as filter criteria.
    get Visible Foreground Color()
    		Color Returns the foreground color used as a filter criteria.
    get Visible Values()
    		String[] Returns the values that the pivot table filter shows.
    set Hidden Values(values)
    		Filter Criteria Builder Sets the values to hide.
    set Visible Background Color(visibleBackgroundColor)
    		Filter Criteria Builder Sets the background color used as filter criteria.
    set Visible Foreground Color(visibleForegroundColor)
    		Filter Criteria Builder Sets the foreground color used as filter criteria.
    set Visible Values(values)
    		Filter Criteria Builder Sets the values to show on a pivot table.
    when Cell Empty()
    		Filter Criteria Builder Sets the filter criteria to show empty cells.
    when Cell Not Empty()
    		Filter Criteria Builder Sets the filter criteria to show cells that aren't empty.
    when Date After(date)
    		Filter Criteria Builder Sets filter criteria that shows cells with dates that are after the specified date.
    when Date After(date)
    		Filter Criteria Builder Sets filter criteria that shows cells with dates that are after the specified relative date.
    when Date Before(date)
    		Filter Criteria Builder Sets filter criteria that shows cells with dates that are before the specified date.
    when Date Before(date)
    		Filter Criteria Builder Sets filter criteria that shows cells with dates that are before the specified relative date.
    when Date Equal To(date)
    		Filter Criteria Builder Sets filter criteria that shows cells with dates that are equal to the specified date.
    when Date Equal To(date)
    		Filter Criteria Builder Sets filter criteria that shows cells with dates that are equal to the specified relative date.
    when Date Equal To Any(dates)
    		Filter Criteria Builder Sets the filter criteria to show cells with dates that are equal to any of the specified dates.
    when Date Not Equal To(date)
    		Filter Criteria Builder Sets the filter criteria to show cells that aren't equal to the specified date.
    when Date Not Equal To Any(dates)
    		Filter Criteria Builder Sets the filter criteria to show cells with dates that aren't equal to any of the specified dates.
    when Formula Satisfied(formula)
    		Filter Criteria Builder Sets the filter criteria to show cells with a specified formula (such as =B:B<C:C ) that evaluates to true .
    when Number Between(start, end)
    		Filter Criteria Builder Sets the filter criteria to show cells with a number that falls between, or is either of, 2 specified numbers.
    when Number Equal To(number)
    		Filter Criteria Builder Sets the filter criteria to show cells with a number that's equal to the specified number.
    when Number Equal To Any(numbers)
    		Filter Criteria Builder Sets the filter criteria to show cells with a number that's equal to any of the specified numbers.
    when Number Greater Than(number)
    		Filter Criteria Builder Sets the filter criteria to show cells with a number greater than the specified number
    when Number Greater Than Or Equal To(number)
    		Filter Criteria Builder Sets the filter criteria to show cells with a number greater than or equal to the specified number.
    when Number Less Than(number)
    		Filter Criteria Builder Sets the filter criteria to show cells with a number that's less than the specified number.
    when Number Less Than Or Equal To(number)
    		Filter Criteria Builder Sets the filter criteria to show cells with a number less than or equal to the specified number.
    when Number Not Between(start, end)
    		Filter Criteria Builder Sets the filter criteria to show cells with a number doesn't fall between, and is neither of, 2 specified numbers.
    when Number Not Equal To(number)
    		Filter Criteria Builder Sets the filter criteria to show cells with a number that isn't equal to the specified number.
    when Number Not Equal To Any(numbers)
    		Filter Criteria Builder Sets the filter criteria to show cells with a number that isn't equal to any of the specified numbers.
    when Text Contains(text)
    		Filter Criteria Builder Sets the filter criteria to show cells with text that contains the specified text.
    when Text Does Not Contain(text)
    		Filter Criteria Builder Sets the filter criteria to show cells with text that doesn't contain the specified text.
    when Text Ends With(text)
    		Filter Criteria Builder Sets the filter criteria to show cells with text that ends with the specified text.
    when Text Equal To(text)
    		Filter Criteria Builder Sets the filter criteria to show cells with text that's equal to the specified text.
    when Text Equal To Any(texts)
    		Filter Criteria Builder Sets the filter criteria to show cells with text that's equal to any of the specified text values.
    when Text Not Equal To(text)
    		Filter Criteria Builder Sets the filter criteria to show cells with text that isn't equal to the specified text.
    when Text Not Equal To Any(texts)
    		Filter Criteria Builder Sets the filter criteria to show cells with text that isn't equal to any of the specified values.
    when Text Starts With(text)
    		Filter Criteria Builder Sets the filter criteria to show cells with text that starts with the specified text.
    with Criteria(criteria, args)
    		Filter Criteria Builder Sets the filter criteria to a boolean condition defined by Boolean Criteria values, such as CELL_EMPTY or NUMBER_GREATER_THAN .

    Detailed documentation

    build()

    Assembles the filter criteria using the settings you add to the criteria builder.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
  
 . 
 newFilterCriteria 
 () 
  
 // Creates a criteria builder. 
  
 . 
 whenCellNotEmpty 
 () 
  
 // Adds settings to the builder. 
  
 . 
 build 
 (); 
  
 // Assembles the criteria. 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Return

    Filter Criteria — A representation of the filter criteria.

    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.

     const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Makes a copy of the filter criteria applied to column C. 
 const 
  
 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

    Filter Criteria Builder — A filter criteria builder based on this filter criteria.

    get Criteria Type()

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

    People often use this method to add boolean condition 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 .

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

    Return

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

    get Criteria Values()

    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 boolean condition criteria to a filter without replacing existing criteria.

    • To get the boolean criteria type, use get Criteria Type() .
    • To use the criteria type and criteria values to create or modify a filter criteria, see with Criteria(criteria, args) .

      You can use this method for any type of filter.

       const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 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. 
 const 
  
 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 Filter Criteria Builder class.

    get Hidden Values()

    Returns the values that the filter hides.

    Use this criteria with filters on Grid sheets, the default type of sheet. Returns null if you call this method for other types of filters.

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

    Return

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

    get Visible Background Color()

    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. Returns null if you call this method for other types of filters.

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

    Return

    Color — The background color used as filter criteria.

    get Visible Foreground Color()

    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. Returns null if you call this method for other types of filters.

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

    Return

    Color — The foreground color used as a filter criteria.

    get Visible Values()

    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.

     const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 // Gets the first pivot table on the sheet, then gets the visible values of its 
 // first filter. 
 const 
  
 pivotTable 
  
 = 
  
 ss 
 . 
 getPivotTables 
 ()[ 
 0 
 ]; 
 const 
  
 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.

    set Hidden Values(values)

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

    You can only use this criteria for filters on Grid sheets, the default type of sheet.

     // Gets the existing filter on the range. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 ss 
 . 
 getRange 
 ( 
 'A1:C20' 
 ); 
 const 
  
 filter 
  
 = 
  
 range 
 . 
 getFilter 
 (); 
 // Sets the values to hide and applies the criteria to column C. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 setHiddenValues 
 ([ 
 'Hello' 
 , 
  
 'World' 
 ]) 
  
 . 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 3 
 , 
  
 criteria 
 );

    Parameters

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

    Return

    Filter Criteria Builder — This builder, for chaining.

    Throws

    Error — If any of the values are null .

    set Visible Background Color(visibleBackgroundColor)

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

    You can only use this criteria for filters on Grid sheets, the default type of sheet.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that filters by background color and sets it to column B. 
 const 
  
 color 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 (). 
 setRgbColor 
 ( 
 '#185ABC' 
 ). 
 build 
 (); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 setVisibleBackgroundColor 
 ( 
 color 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    visible Background Color
    		Color The background color to set. The color must be an RGB-style color. This method doesn't support theme colors.

    Return

    Filter Criteria Builder — This builder, for chaining.

    set Visible Foreground Color(visibleForegroundColor)

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

    You can only use this criteria for filters on Grid sheets, the default type of sheet.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that filters by foreground color and sets it to column B. 
 const 
  
 color 
  
 = 
  
 SpreadsheetApp 
 . 
 newColor 
 (). 
 setRgbColor 
 ( 
 '#185ABC' 
 ). 
 build 
 (); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 setVisibleForegroundColor 
 ( 
 color 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    visible Foreground Color
    		Color The foreground color to set. The color must be an RGB-style color. This method doesn't support theme colors.

    Return

    Filter Criteria Builder — This builder, for chaining.

    set Visible Values(values)

    Sets the values to show on a pivot table. Clears any existing visible or hidden values.

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

     // Gets the active sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 // Gets the first pivot table on the sheet and adds a filter to it that 
 // sets the visible values to "Northeast" and "Southwest." 
 const 
  
 pivotTable 
  
 = 
  
 ss 
 . 
 getPivotTables 
 ()[ 
 0 
 ]; 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 setVisibleValues 
 ([ 
 'Northeast' 
 , 
  
 'Southwest' 
 ]) 
  
 . 
 build 
 (); 
 pivotTable 
 . 
 addFilter 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

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

    Return

    Filter Criteria Builder — This builder, for chaining.

    Throws

    Error — If any of the values are null .

    when Cell Empty()

    Sets the filter criteria to show empty cells.

    You can use this criteria with any type of filter.

     // Gets the existing filter on the range. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 ss 
 . 
 getRange 
 ( 
 'A1:C20' 
 ); 
 const 
  
 filter 
  
 = 
  
 range 
 . 
 getFilter 
 (); 
 // Sets criteria to column B that only shows empty cells. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenCellEmpty 
 (). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Cell Not Empty()

    Sets the filter criteria to show cells that aren't empty.

    You can use this criteria with any type of filter.

     // Gets the existing filter on the range. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 ss 
 . 
 getRange 
 ( 
 'A1:C20' 
 ); 
 const 
  
 filter 
  
 = 
  
 range 
 . 
 getFilter 
 (); 
 // Sets criteria to column B that only shows cells that aren't empty. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenCellNotEmpty 
 (). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Date After(date)

    Sets filter criteria that shows cells with dates that are after 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.

     // Gets the existing filter on the range. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 ss 
 . 
 getRange 
 ( 
 'A1:C20' 
 ); 
 const 
  
 filter 
  
 = 
  
 range 
 . 
 getFilter 
 (); 
 // Creates criteria that only shows cells with dates after June 1, 2022 
 // and sets it to column A. 
 const 
  
 date 
  
 = 
  
 new 
  
 Date 
 ( 
 'June 1, 2022' 
 ); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenDateAfter 
 ( 
 date 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 1 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    date
    		Date The latest date to hide.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Date After(date)

    Sets filter criteria that shows cells with dates that are after the specified relative date. To view the relative date options, refer to Enum 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.

     // Gets the existing filter on the range. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 ss 
 . 
 getRange 
 ( 
 'A1:C20' 
 ); 
 const 
  
 filter 
  
 = 
  
 range 
 . 
 getFilter 
 (); 
 // Creates criteria that only shows cells with dates after today's date 
 // and sets it to column A. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 whenDateAfter 
 ( 
 SpreadsheetApp 
 . 
 RelativeDate 
 . 
 TODAY 
 ) 
  
 . 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 1 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    date
    		Relative Date The latest relative date.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Date Before(date)

    Sets filter criteria that shows cells with dates that are 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.

     // Gets the existing filter on the range. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 ss 
 . 
 getRange 
 ( 
 'A1:C20' 
 ); 
 const 
  
 filter 
  
 = 
  
 range 
 . 
 getFilter 
 (); 
 // Creates criteria that only shows cells with dates before June 1, 2022 
 // and sets it to column A. 
 const 
  
 date 
  
 = 
  
 new 
  
 Date 
 ( 
 'June 1, 2022' 
 ); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenDateBefore 
 ( 
 date 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 1 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    date
    		Date The earliest date to hide.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Date Before(date)

    Sets filter criteria that shows cells with dates that are before the specified relative date. To view the relative date options, refer to Enum 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.

     // Gets the existing filter on the range. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 ss 
 . 
 getRange 
 ( 
 'A1:C20' 
 ); 
 const 
  
 filter 
  
 = 
  
 range 
 . 
 getFilter 
 (); 
 // Creates criteria that only shows cells with dates before today's date 
 // and sets it to column A. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 whenDateBefore 
 ( 
 SpreadsheetApp 
 . 
 RelativeDate 
 . 
 TODAY 
 ) 
  
 . 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 1 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    date
    		Relative Date The earliest relative date to hide.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Date Equal To(date)

    Sets filter criteria that shows cells with dates that are 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.

     // Gets the existing filter on the range. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 ss 
 . 
 getRange 
 ( 
 'A1:C20' 
 ); 
 const 
  
 filter 
  
 = 
  
 range 
 . 
 getFilter 
 (); 
 // Creates criteria that only shows cells with dates equal to June 1, 2022 
 // and sets it to column A. 
 const 
  
 date 
  
 = 
  
 new 
  
 Date 
 ( 
 'June 1, 2022' 
 ); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenDateEqualTo 
 ( 
 date 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 1 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    date
    		Date The date that cell values must match.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Date Equal To(date)

    Sets filter criteria that shows cells with dates that are equal to the specified relative date. To view the relative date options, refer to Enum 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.

     // Gets the existing filter on the range. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 ss 
 . 
 getRange 
 ( 
 'A1:C20' 
 ); 
 const 
  
 filter 
  
 = 
  
 range 
 . 
 getFilter 
 (); 
 // Creates criteria that only shows cells with dates that fall within the past 
 // month and sets it to column A. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 whenDateEqualTo 
 ( 
 SpreadsheetApp 
 . 
 RelativeDate 
 . 
 PAST_MONTH 
 ) 
  
 . 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 1 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    date
    		Relative Date The relative date that cell values must match.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Date Equal To Any(dates)

    Sets the filter criteria to show cells with dates that are equal to any of the specified dates.

    You can only use this criteria with data connected to a database. For example, use this criteria with filters on a Data Source sheet, a sheet that's connected to a database, or a Data Source Pivot Table , a pivot table created from a Data Source sheet.

     // Gets the sheet that's connected to a database. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (). 
 getSheetByName 
 ( 
 'Data Sheet' 
 ); 
 const 
  
 dataSheet 
  
 = 
  
 ss 
 . 
 asDataSourceSheet 
 (); 
 // Adds criteria to the "date" column that shows cells with any of the below 
 // dates. 
 const 
  
 date1 
  
 = 
  
 new 
  
 Date 
 ( 
 'June 1, 2022' 
 ); 
 const 
  
 date2 
  
 = 
  
 new 
  
 Date 
 ( 
 'June 2, 2022' 
 ); 
 const 
  
 date3 
  
 = 
  
 new 
  
 Date 
 ( 
 'June 3, 2022' 
 ); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 whenDateEqualToAny 
 ([ 
 date1 
 , 
  
 date2 
 , 
  
 date3 
 ]) 
  
 . 
 build 
 (); 
 dataSheet 
 . 
 addFilter 
 ( 
 'date' 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    dates
    		Date[] The dates to show.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Date Not Equal To(date)

    Sets the filter criteria to show cells that aren't equal to the specified date.

    You can only use this criteria with data connected to a database. For example, use this criteria with filters on a Data Source sheet, a sheet that's connected to a database, or a Data Source Pivot Table , a pivot table created from a Data Source sheet.

    The data type of the column you're filtering by must be a date.

     // Gets a pivot table that's connected to a database. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (). 
 getSheetByName 
 ( 
 'Pivot Table Sheet' 
 ); 
 const 
  
 dataPivotTable 
  
 = 
  
 ss 
 . 
 getDataSourcePivotTables 
 ()[ 
 0 
 ]; 
 // Creates criteria that only shows cells that don't equal June 16, 2022 
 // and sets it to the "date" column. 
 const 
  
 date 
  
 = 
  
 new 
  
 Date 
 ( 
 'June 16, 2022' 
 ); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenDateNotEqualTo 
 ( 
 date 
 ). 
 build 
 (); 
 dataPivotTable 
 . 
 addFilter 
 ( 
 'date' 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    date
    		Date The date to hide.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Date Not Equal To Any(dates)

    Sets the filter criteria to show cells with dates that aren't equal to any of the specified dates.

    You can only use this criteria with data connected to a database. For example, use this criteria with filters on a Data Source sheet, a sheet that's connected to a database, or a Data Source Pivot Table , a pivot table created from a Data Source sheet.

     // Gets the sheet that's connected to a database. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (). 
 getSheetByName 
 ( 
 'Data Sheet' 
 ); 
 const 
  
 dataSheet 
  
 = 
  
 ss 
 . 
 asDataSourceSheet 
 (); 
 // Adds criteria to the "date" column that hides cells with any of the below 
 // dates. 
 const 
  
 date1 
  
 = 
  
 new 
  
 Date 
 ( 
 'June 1, 2022' 
 ); 
 const 
  
 date2 
  
 = 
  
 new 
  
 Date 
 ( 
 'June 2, 2022' 
 ); 
 const 
  
 date3 
  
 = 
  
 new 
  
 Date 
 ( 
 'June 3, 2022' 
 ); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 whenDateNotEqualToAny 
 ([ 
 date1 
 , 
  
 date2 
 , 
  
 date3 
 ]) 
  
 . 
 build 
 (); 
 dataSheet 
 . 
 addFilter 
 ( 
 'date' 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    dates
    		Date[] The dates to hide.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Formula Satisfied(formula)

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

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

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that shows the rows where the value in column B is less than 
 // the value in column C and sets it to column A. 
 const 
  
 formula 
  
 = 
  
 '=B:B<C:C' 
 ; 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenFormulaSatisfied 
 ( 
 formula 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 1 
 , 
  
 criteria 
 );

    Parameters

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

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Number Between(start, end)

    Sets the filter criteria to show cells with a number that falls between, or is either of, 2 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.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that only shows cells with numbers that fall between 1-25, 
 // inclusively, and sets it to column A. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenNumberBetween 
 ( 
 1 
 , 
  
 25 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 1 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    start
    		Number The lowest number to show.
    end
    		Number The highest number to show.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Number Equal To(number)

    Sets the filter criteria to show cells with a number that's equal to the specified number.

    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.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that only shows cells that are equal to 25 and sets it to 
 // column B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenNumberEqualTo 
 ( 
 25 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    number
    		Number The number to show.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Number Equal To Any(numbers)

    Sets the filter criteria to show cells with a number that's equal to any of the specified numbers.

    You can only use this criteria with data connected to a database. For example, use this criteria with filters on a Data Source sheet, a sheet that's connected to a database, or a Data Source Pivot Table , a pivot table created from a Data Source sheet.

     // Gets the sheet that's connected to a database. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (). 
 getSheetByName 
 ( 
 'Data Sheet' 
 ); 
 const 
  
 dataSheet 
  
 = 
  
 ss 
 . 
 asDataSourceSheet 
 (); 
 // Adds criteria to the "amount" column that only shows cells with the number 
 // 10, 20, or 30. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 whenNumberEqualToAny 
 ([ 
 10 
 , 
  
 20 
 , 
  
 30 
 ]) 
  
 . 
 build 
 (); 
 dataSheet 
 . 
 addFilter 
 ( 
 'amount' 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    numbers
    		Number[] The numbers to show.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Number Greater Than(number)

    Sets the filter criteria to show cells with a number greater than the specified number

    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.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that shows cells greater than 10 and sets it to column B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenNumberGreaterThan 
 ( 
 10 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    number
    		Number The highest number to hide.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Number Greater Than Or Equal To(number)

    Sets the filter criteria to show cells with a number greater than or equal to the specified number.

    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.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that shows cells greater than or equal to 10 and sets it to 
 // column B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 whenNumberGreaterThanOrEqualTo 
 ( 
 10 
 ) 
  
 . 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    number
    		Number The lowest number to show.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Number Less Than(number)

    Sets the filter criteria to show cells with a number that's less than the specified number.

    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.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that shows cells less than 10 and sets it to column B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenNumberLessThan 
 ( 
 10 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    number
    		Number The lowest number to hide.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Number Less Than Or Equal To(number)

    Sets the filter criteria to show cells with a number less than or equal to the specified number.

    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.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that shows cells less than or equal to 10 and sets it to 
 // column B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenNumberLessThanOrEqualTo 
 ( 
 10 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    number
    		Number The highest number to show.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Number Not Between(start, end)

    Sets the filter criteria to show cells with a number doesn't fall between, and is neither of, 2 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.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that hides cells with numbers that fall between 1-25, 
 // inclusively, and sets it to column B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenNumberNotBetween 
 ( 
 1 
 , 
  
 25 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    start
    		Number The lowest number hide.
    end
    		Number The highest number to hide.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Number Not Equal To(number)

    Sets the filter criteria to show cells with a number that isn't equal to the specified number.

    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.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that hides cells that are equal to 25 and sets it to column 
 // B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenNumberNotEqualTo 
 ( 
 25 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    number
    		Number The number to hide.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Number Not Equal To Any(numbers)

    Sets the filter criteria to show cells with a number that isn't equal to any of the specified numbers.

    You can only use this criteria with data connected to a database. For example, use this criteria with filters on a Data Source sheet, a sheet that's connected to a database, or a Data Source Pivot Table , a pivot table created from a Data Source sheet.

     // Gets the sheet that's connected to a database. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (). 
 getSheetByName 
 ( 
 'Data Sheet' 
 ); 
 const 
  
 dataSheet 
  
 = 
  
 ss 
 . 
 asDataSourceSheet 
 (); 
 // Adds criteria to the "amount" column that hides cells with the number 10, 20, 
 // or 30. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 whenNumberNotEqualToAny 
 ([ 
 10 
 , 
  
 20 
 , 
  
 30 
 ]) 
  
 . 
 build 
 (); 
 dataSheet 
 . 
 addFilter 
 ( 
 'amount' 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    numbers
    		Number[] The numbers to hide.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Text Contains(text)

    Sets the filter criteria to show cells with text that contains the specified text. The text isn't case-sensitive.

    You can use this criteria with any type of filter.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that shows cells that contain "Northwest" and sets it to 
 // column B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenTextContains 
 ( 
 'Northwest' 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    text
    		String The text that the cell must contain.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Text Does Not Contain(text)

    Sets the filter criteria to show cells with text that doesn't contain the specified text. The text isn't case-sensitive.

    You can use this criteria with any type of filter.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that hides cells that contain "Northwest" and sets it to 
 // column B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 whenTextDoesNotContain 
 ( 
 'Northwest' 
 ) 
  
 . 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    text
    		String The text that the cell must not contain.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Text Ends With(text)

    Sets the filter criteria to show cells with text that ends with the specified text. The text isn't case-sensitive.

    You can use this criteria with any type of filter.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that shows cells with text that ends with "est" and sets it 
 // to column B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenTextEndsWith 
 ( 
 'est' 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    text
    		String Text that the end of the cell's text must contain.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Text Equal To(text)

    Sets the filter criteria to show cells with text that's equal to the specified text. The text isn't case-sensitive.

    You can use this criteria with any type of filter.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that shows cells with text that equals "hello" and sets it 
 // to column B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenTextEqualTo 
 ( 
 'hello' 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    text
    		String The text that the cell's text must equal.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Text Equal To Any(texts)

    Sets the filter criteria to show cells with text that's equal to any of the specified text values. The text isn't case-sensitive.

    You can only use this criteria with data connected to a database. For example, use this criteria with filters on a Data Source sheet, a sheet that's connected to a database, or a Data Source Pivot Table , a pivot table created from a Data Source sheet.

     // Gets the sheet that's connected to a database. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (). 
 getSheetByName 
 ( 
 'Data Sheet' 
 ); 
 const 
  
 dataSheet 
  
 = 
  
 ss 
 . 
 asDataSourceSheet 
 (); 
 // Adds criteria to the "category" column that shows cells with the text "tech" 
 // or "business." 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 whenTextEqualToAny 
 ([ 
 'tech' 
 , 
  
 'business' 
 ]) 
  
 . 
 build 
 (); 
 dataSheet 
 . 
 addFilter 
 ( 
 'category' 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    texts
    		String[] The text values that a cell must equal.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Text Not Equal To(text)

    Sets the filter criteria to show cells with text that isn't equal to the specified text. The text isn't case-sensitive.

    You can only use this criteria with data connected to a database. For example, use this criteria with filters on a Data Source sheet, a sheet that's connected to a database, or a Data Source Pivot Table , a pivot table created from a Data Source sheet.

     // Gets the sheet that's connected to a database. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (). 
 getSheetByName 
 ( 
 'Data Sheet' 
 ); 
 const 
  
 dataSheet 
  
 = 
  
 ss 
 . 
 asDataSourceSheet 
 (); 
 // Adds criteria to the "category" column that hides cells with text equal to 
 // "tech." 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenTextNotEqualTo 
 ( 
 'tech' 
 ). 
 build 
 (); 
 dataSheet 
 . 
 addFilter 
 ( 
 'category' 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    text
    		String The text that the cell's text can't equal.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Text Not Equal To Any(texts)

    Sets the filter criteria to show cells with text that isn't equal to any of the specified values. The text isn't case-sensitive.

    You can only use this criteria with data connected to a database. For example, use this criteria with filters on a Data Source sheet, a sheet that's connected to a database, or a Data Source Pivot Table , a pivot table created from a Data Source sheet.

     // Gets the sheet that's connected to a database. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSpreadsheet 
 (). 
 getSheetByName 
 ( 
 'Data Sheet' 
 ); 
 const 
  
 dataSheet 
  
 = 
  
 ss 
 . 
 asDataSourceSheet 
 (); 
 // Adds criteria to the "category" column that hides cells with the text "tech" 
 // or "business." 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 whenTextNotEqualToAny 
 ([ 
 'tech' 
 , 
  
 'business' 
 ]) 
  
 . 
 build 
 (); 
 dataSheet 
 . 
 addFilter 
 ( 
 'category' 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    texts
    		String[] The text values that a cell can't equal.

    Return

    Filter Criteria Builder — This builder, for chaining.

    when Text Starts With(text)

    Sets the filter criteria to show cells with text that starts with the specified text. The text isn't case-sensitive.

    You can use this criteria with any type of filter.

     // Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Creates criteria that shows cells with text that starts with "pre" and sets 
 // it to column B. 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 (). 
 whenTextStartsWith 
 ( 
 'pre' 
 ). 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 2 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    text
    		String Text that the start of the cell's text must contain.

    Return

    Filter Criteria Builder — This builder, for chaining.

    with Criteria(criteria, args)

    Sets the filter criteria to a boolean condition defined by Boolean Criteria values, such as CELL_EMPTY or NUMBER_GREATER_THAN . To copy the boolean condition from existing criteria, define the parameters for this method using get Criteria Type() and get Criteria Values() on an existing criteria.

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

     // Builds a filter criteria that is based on existing boolean conditions from 
 // another criteria. Gets the existing filter on the sheet. 
 const 
  
 ss 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 filter 
  
 = 
  
 ss 
 . 
 getFilter 
 (); 
 // Gets the existing boolean conditions applied to Column B and adds criteria to 
 // column C that has the same boolean conditions and additional criteria that 
 // hides the value, "Northwest." 
 const 
  
 filterCriteria 
  
 = 
  
 filter 
 . 
 getColumnFilterCriteria 
 ( 
 2 
 ); 
 const 
  
 criteria 
  
 = 
  
 SpreadsheetApp 
 . 
 newFilterCriteria 
 () 
  
 . 
 withCriteria 
 ( 
  
 filterCriteria 
 . 
 getCriteriaType 
 (), 
  
 filterCriteria 
 . 
 getCriteriaValues 
 (), 
  
 ) 
  
 . 
 setHiddenValues 
 ([ 
 'Northwest' 
 ]) 
  
 . 
 build 
 (); 
 filter 
 . 
 setColumnFilterCriteria 
 ( 
 3 
 , 
  
 criteria 
 );

    Parameters

    Name Type Description
    criteria
    		Boolean Criteria 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...() methods above.

    Return

    Filter Criteria Builder — This builder, for chaining.
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: