Class DataValidation

DataValidation

Access data validation rules. To create a new rule, use SpreadsheetApp.newDataValidation() and DataValidationBuilder . You can use Range.setDataValidation(rule) to set the validation rule for a range.

 // 
  
 Log 
  
 information 
  
 about 
  
 the 
  
 data 
  
 validation 
  
 rule 
  
 for 
  
 cell 
  
 A1 
 . 
 var 
  
 cell 
  
 = 
  
 SpreadsheetApp 
 . 
 getActive 
 () 
 . 
 getRange 
 ( 
 'A1' 
 ); 
 var 
  
 rule 
  
 = 
  
 cell 
 . 
 getDataValidation 
 (); 
 if 
  
 ( 
 rule 
  
 != 
  
 null 
 ) 
  
 { 
  
 var 
  
 criteria 
  
 = 
  
 rule 
 . 
 getCriteriaType 
 (); 
  
 var 
  
 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()`	`DataValidationBuilder`	Creates a builder for a data validation rule based on this rule's settings.
`getAllowInvalid()`	`Boolean`	Returns `true` if the rule shows a warning when input fails data validation, or `false` if it rejects the input entirely.
`getCriteriaType()`	`DataValidationCriteria`	Gets the rule's criteria type as defined in the `DataValidationCriteria` enum.
`getCriteriaValues()`	`Object[]`	Gets an array of arguments for the rule's criteria.
`getHelpText()`	`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. 
 var 
  
 oldDates 
  
 = 
  
 [ 
 new Date('1/1/2013'), new Date('12/31/2013') 
 ] 
 ; 
 var 
  
 newDates 
  
 = 
  
 [ 
 new Date('1/1/2014'), new Date('12/31/2014') 
 ] 
 ; 
 var 
  
 sheet 
  
 = 
  
 SpreadsheetApp 
 . 
 getActiveSheet 
 (); 
 var 
  
 range 
  
 = 
  
 sheet 
 . 
 getRange 
 ( 
 1 
 , 
  
 1 
 , 
  
 sheet 
 . 
 getMaxRows 
 (), 
  
 sheet 
 . 
 getMaxColumns 
 ()); 
 var 
  
 rules 
  
 = 
  
 range 
 . 
 getDataValidations 
 (); 
 for 
  
 ( 
 var 
  
 i 
  
 = 
  
 0 
 ; 
  
 i 
 < 
 rules 
 . 
 length 
 ; 
  
 i 
 ++ 
 ) 
  
 { 
  
 for 
  
 ( 
 var 
  
 j 
  
 = 
  
 0 
 ; 
  
 j 
 < 
 rules 
 [ 
 i 
 ] 
 . 
 length 
 ; 
  
 j 
 ++ 
 ) 
  
 { 
  
 var 
  
 rule 
  
 = 
  
 rules 
 [ 
 i 
 ][ 
 j 
 ] 
 ; 
  
 if 
  
 ( 
 rule 
  
 != 
  
 null 
 ) 
  
 { 
  
 var 
  
 criteria 
  
 = 
  
 rule 
 . 
 getCriteriaType 
 (); 
  
 var 
  
 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

DataValidationBuilder — a builder based on this rule's settings

`getAllowInvalid()`

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

`getCriteriaType()`

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

 // 
  
 Log 
  
 information 
  
 about 
  
 the 
  
 data 
  
 validation 
  
 rule 
  
 for 
  
 cell 
  
 A1 
 . 
 var 
  
 cell 
  
 = 
  
 SpreadsheetApp 
 . 
 getActive 
 () 
 . 
 getRange 
 ( 
 'A1' 
 ); 
 var 
  
 rule 
  
 = 
  
 cell 
 . 
 getDataValidation 
 (); 
 if 
  
 ( 
 rule 
  
 != 
  
 null 
 ) 
  
 { 
  
 var 
  
 criteria 
  
 = 
  
 rule 
 . 
 getCriteriaType 
 (); 
  
 var 
  
 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

DataValidationCriteria — the type of data validation criteria

`getCriteriaValues()`

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

 // 
  
 Log 
  
 information 
  
 about 
  
 the 
  
 data 
  
 validation 
  
 rule 
  
 for 
  
 cell 
  
 A1 
 . 
 var 
  
 cell 
  
 = 
  
 SpreadsheetApp 
 . 
 getActive 
 () 
 . 
 getRange 
 ( 
 'A1' 
 ); 
 var 
  
 rule 
  
 = 
  
 cell 
 . 
 getDataValidation 
 (); 
 if 
  
 ( 
 rule 
  
 != 
  
 null 
 ) 
  
 { 
  
 var 
  
 criteria 
  
 = 
  
 rule 
 . 
 getCriteriaType 
 (); 
  
 var 
  
 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 DataValidationBuilder class

`getHelpText()`

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