Class DataValidation

Data Validation

Access data validation rules. To create a new rule, use Spreadsheet App.newDataValidation() and Data Validation Builder . You can use Range.setDataValidation(rule) to set the validation rule for a range.

 // Log information about the data validation rule for cell A1. 
 const 
  
 cell 
  
 = 
  
 SpreadsheetApp 
 . 
 getActive 
 (). 
 getRange 
 ( 
 'A1' 
 ); 
 const 
  
 rule 
  
 = 
  
 cell 
 . 
 getDataValidation 
 (); 
 if 
  
 ( 
 rule 
  
 != 
  
 null 
 ) 
  
 { 
  
 const 
  
 criteria 
  
 = 
  
 rule 
 . 
 getCriteriaType 
 (); 
  
 const 
  
 args 
  
 = 
  
 rule 
 . 
 getCriteriaValues 
 (); 
  
 Logger 
 . 
 log 
 ( 
 'The data validation rule is %s %s' 
 , 
  
 criteria 
 , 
  
 args 
 ); 
 } 
  
 else 
  
 { 
  
 Logger 
 . 
 log 
 ( 
 'The cell does not have a data validation rule.' 
 ); 
 }

Methods

Method	Return type	Brief description
`copy()`	`Data Validation Builder`	Creates a builder for a data validation rule based on this rule's settings.
`get Allow Invalid()`	`Boolean`	Returns `true` if the rule shows a warning when input fails data validation, or `false` if it rejects the input entirely.
`get Criteria Type()`	`Data Validation Criteria`	Gets the rule's criteria type as defined in the `Data Validation Criteria` enum.
`get Criteria Values()`	`Object[]`	Gets an array of arguments for the rule's criteria.
`get Help Text()`	`String`	Gets the rule's help text, or `null` if no help text is set.

Detailed documentation

`copy()`

Creates a builder for a data validation rule based on this rule's settings.

 // Change existing data validation rules that require a date in 2013 to require 
 // a date in 2014. 
 const 
  
 oldDates 
  
 = 
  
 [ 
 new 
  
 Date 
 ( 
 '1/1/2013' 
 ), 
  
 new 
  
 Date 
 ( 
 '12/31/2013' 
 )]; 
 const 
  
 newDates 
  
 = 
  
 [ 
 new 
  
 Date 
 ( 
 '1/1/2014' 
 ), 
  
 new 
  
 Date 
 ( 
 '12/31/2014' 
 )]; 
 const 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 const 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 1 
 , 
  
 1 
 , 
  
 sheet 
 . 
 getMaxRows 
 (), 
  
 sheet 
 . 
 getMaxColumns 
 ()); 
 const 
  
 rules 
  
 = 
  
 range 
 . 
 getDataValidations 
 (); 
 for 
  
 ( 
 let 
  
 i 
  
 = 
  
 0 
 ; 
  
 i 
 < 
 rules 
 . 
 length 
 ; 
  
 i 
 ++ 
 ) 
  
 { 
  
 for 
  
 ( 
 let 
  
 j 
  
 = 
  
 0 
 ; 
  
 j 
 < 
 rules 
 [ 
 i 
 ]. 
 length 
 ; 
  
 j 
 ++ 
 ) 
  
 { 
  
 const 
  
 rule 
  
 = 
  
 rules 
 [ 
 i 
 ][ 
 j 
 ]; 
  
 if 
  
 ( 
 rule 
  
 != 
  
 null 
 ) 
  
 { 
  
 const 
  
 criteria 
  
 = 
  
 rule 
 . 
 getCriteriaType 
 (); 
  
 const 
  
 args 
  
 = 
  
 rule 
 . 
 getCriteriaValues 
 (); 
  
 if 
  
 ( 
 criteria 
  
 === 
  
 SpreadsheetApp 
 . 
 DataValidationCriteria 
 . 
 DATE_BETWEEN 
  
&&  
 args 
 [ 
 0 
 ]. 
 getTime 
 () 
  
 === 
  
 oldDates 
 [ 
 0 
 ]. 
 getTime 
 () 
  
&&  
 args 
 [ 
 1 
 ]. 
 getTime 
 () 
  
 === 
  
 oldDates 
 [ 
 1 
 ]. 
 getTime 
 ()) 
  
 { 
  
 // Create a builder from the existing rule, then change the dates. 
  
 rules 
 [ 
 i 
 ][ 
 j 
 ] 
  
 = 
  
 rule 
 . 
 copy 
 (). 
 withCriteria 
 ( 
 criteria 
 , 
  
 newDates 
 ). 
 build 
 (); 
  
 } 
  
 } 
  
 } 
 } 
 range 
 . 
 setDataValidations 
 ( 
 rules 
 );

Return

Data Validation Builder — a builder based on this rule's settings

`get Allow Invalid()`

Returns true if the rule shows a warning when input fails data validation, or false if it rejects the input entirely. The default for new data validation rules is true .

Return

Boolean — true if the rule allows input that fails data validation; false if not

`get Criteria Type()`

Gets the rule's criteria type as defined in the Data Validation Criteria enum. To get the arguments for the criteria, use get Criteria Values() . To use these values to create or modify a data validation rule, see Data Validation Builder.withCriteria(criteria, args) .

 // Log information about the data validation rule for cell A1. 
 const 
  
 cell 
  
 = 
  
 SpreadsheetApp 
 . 
 getActive 
 (). 
 getRange 
 ( 
 'A1' 
 ); 
 const 
  
 rule 
  
 = 
  
 cell 
 . 
 getDataValidation 
 (); 
 if 
  
 ( 
 rule 
  
 != 
  
 null 
 ) 
  
 { 
  
 const 
  
 criteria 
  
 = 
  
 rule 
 . 
 getCriteriaType 
 (); 
  
 const 
  
 args 
  
 = 
  
 rule 
 . 
 getCriteriaValues 
 (); 
  
 Logger 
 . 
 log 
 ( 
 'The data validation rule is %s %s' 
 , 
  
 criteria 
 , 
  
 args 
 ); 
 } 
  
 else 
  
 { 
  
 Logger 
 . 
 log 
 ( 
 'The cell does not have a data validation rule.' 
 ); 
 }

Return

Data Validation Criteria — the type of data validation criteria

`get Criteria Values()`

Gets an array of arguments for the rule's criteria. To get the criteria type, use get Criteria Type() . To use these values to create or modify a data validation rule, see Data Validation Builder.withCriteria(criteria, args) .

 // Log information about the data validation rule for cell A1. 
 const 
  
 cell 
  
 = 
  
 SpreadsheetApp 
 . 
 getActive 
 (). 
 getRange 
 ( 
 'A1' 
 ); 
 const 
  
 rule 
  
 = 
  
 cell 
 . 
 getDataValidation 
 (); 
 if 
  
 ( 
 rule 
  
 != 
  
 null 
 ) 
  
 { 
  
 const 
  
 criteria 
  
 = 
  
 rule 
 . 
 getCriteriaType 
 (); 
  
 const 
  
 args 
  
 = 
  
 rule 
 . 
 getCriteriaValues 
 (); 
  
 Logger 
 . 
 log 
 ( 
 'The data validation rule is %s %s' 
 , 
  
 criteria 
 , 
  
 args 
 ); 
 } 
  
 else 
  
 { 
  
 Logger 
 . 
 log 
 ( 
 'The cell does not have a data validation rule.' 
 ); 
 }

Return

Object[] — an array of arguments appropriate to the rule's criteria type; the number of arguments and their type match the corresponding require...() method of the Data Validation Builder class

`get Help Text()`

Gets the rule's help text, or null if no help text is set.

Return

String — the rule's help text, or null if no help text is set