build()

Constructs a data-validation rule from the settings applied to the builder.

Return

DataValidation — a representation of the data-validation rule

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

requireDate()

Sets the data-validation rule to require a date.

 // Set the data validation for cell A1 to require a date.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireDate().build();
 cell.setDataValidation(rule);
 

Return

DataValidationBuilder — the builder, for chaining

requireDateAfter(date)

Sets the data-validation rule to require a date after the given value. The time fields of the Date object are ignored; only the day, month, and year fields are used.

 // Set the data validation for cell A1 to require a date after January 1, 2013.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireDateAfter(new Date('1/1/2013')).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireDateBefore(date)

Sets the data-validation rule to require a date before the given value. The time fields of the Date object are ignored; only the day, month, and year fields are used.

 // Set the data validation for cell A1 to require a date before January 1, 2013.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireDateBefore(new Date('1/1/2013')).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireDateBetween(start, end)

Sets the data-validation rule to require a date between the given values, inclusive of the values themselves. The time fields of the Date objects are ignored; only the day, month, and year fields are used.

 // Set the data validation for cell A1 to require a date in 2013.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation()
     .requireDateBetween(new Date('1/1/2013'), new Date('12/31/2013')).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireDateEqualTo(date)

Sets the data-validation rule to require a date equal to the given value. The time fields of the Date object are ignored; only the day, month, and year fields are used.

 // Set the data validation for cell A1 to require a date equal to January 1, 2013.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireDateEqualTo(new Date('1/1/2013'))
     .build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireDateNotBetween(start, end)

Sets the data-validation rule to require a date not between the given values, inclusive of the values themselves. The time fields of the Date objects are ignored; only the day, month, and year fields are used.

 // Set the data validation for cell A1 to require a date not in 2013.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation()
     .requireDateNotBetween(new Date('1/1/2013'), new Date('12/31/2013')).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireDateOnOrAfter(date)

Sets the data-validation rule to require a date on or after the given value. The time fields of the Date object are ignored; only the day, month, and year fields are used.

 // Set the data validation for cell A1 to require a date on or after January 1, 2013.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation()
     .requireDateOnOrAfter(new Date('1/1/2013')).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireDateOnOrBefore(date)

Sets the data-validation rule to require a date on or before the given value. The time fields of the Date object are ignored; only the day, month, and year fields are used.

 // Set the data validation for cell A1 to require a date on or before January 1, 2013.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation()
     .requireDateOnOrBefore(new Date('1/1/2013')).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireFormulaSatisfied(formula)

Sets the data-validation rule to require that the given formula evaluates to true. This method is only available in the new version of Google Sheets.

 // Set the data validation for cell A1 to equal B1 with a custom formula.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireFormulaSatisfied('=EQ(A1,B1)').build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireNumberBetween(start, end)

Sets the data-validation rule to require a number between the given values, inclusive of the values themselves.

 // Set the data validation for cell A1 to require a number between 1 and 10.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireNumberBetween(1, 10).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireNumberEqualTo(number)

Sets the data-validation rule to require a number equal to the given value.

 // Set the data validation for cell A1 to require a number equal to 3.1415926536.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireNumberEqualTo(3.1415926536).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireNumberGreaterThan(number)

Sets the data-validation rule to require a number greater than the given value.

 // Set the data validation for cell A1 to require a number greater than 0.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireNumberGreaterThan(0).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireNumberGreaterThanOrEqualTo(number)

Sets the data-validation rule to require a number greater than or equal to the given value.

 // Set the data validation for cell A1 to require a number greater than or equal to 0.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireNumberGreaterThanOrEqualTo(0).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireNumberLessThan(number)

Sets the data-validation rule to require a number less than the given value.

 // Set the data validation for cell A1 to require a number less than 0.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireNumberLessThan(0).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireNumberLessThanOrEqualTo(number)

Sets the data-validation rule to require a number less than or equal to the given value.

 // Set the data validation for cell A1 to require a number less than or equal to 0.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireNumberLessThanOrEqualTo(0).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireNumberNotBetween(start, end)

Sets the data-validation rule to require a number not between the given values, inclusive of the values themselves.

 // Set the data validation for cell A1 to require a number not between 1 and 10.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireNumberNotBetween(1, 10).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireNumberNotEqualTo(number)

Sets the data-validation rule to require a number not equal to the given value.

 // Set the data validation for cell A1 to require a number not equal to 0.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireNumberNotEqualTo(0).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireTextContains(text)

Sets the data-validation rule to require that the input contains the given value.

 // Set the data validation for cell A1 to require any value that includes "Google".
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireTextContains('Google').build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireTextDoesNotContain(text)

Sets the data-validation rule to require that the input does not contain the given value.

 // Set the data validation for cell A1 to require any value that does not include "@".
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireTextDoesNotContain('@').build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireTextEqualTo(text)

Sets the data-validation rule to require that the input is equal to the given value.

 // Set the data validation for cell A1 to require "Yes".
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireTextEqualTo('Yes').build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireTextIsEmail()

Sets the data-validation rule to require that the input is in the form of an email address.

 // Set the data validation for cell A1 to require text in the form of an email address.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireTextIsEmail().build();
 cell.setDataValidation(rule);
 

Return

DataValidationBuilder — the builder, for chaining

requireTextIsUrl()

Sets the data-validation rule to require that the input is in the form of a URL.

 // Set the data validation for cell A1 to require text in the form of a URL.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireTextIsUrl().build();
 cell.setDataValidation(rule);
 

Return

DataValidationBuilder — the builder, for chaining

requireValueInList(values)

Sets the data-validation rule to require that the input is equal to one of the given values.

 // Set the data validation for cell A1 to require "Yes" or "No", with a dropdown menu.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Yes', 'No']).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireValueInList(values, showDropdown)

Sets the data-validation rule to require that the input is equal to one of the given values, with an option to hide the dropdown menu.

 // Set the data validation for cell A1 to require "Yes" or "No", with no dropdown menu.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Yes', 'No'], false).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireValueInRange(range)

Sets the data-validation rule to require that the input is equal to a value in the given range.

 // Set the data validation for cell A1 to require a value from B1:B10, with a dropdown menu.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var range = SpreadsheetApp.getActive().getRange('B1:B10');
 var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

requireValueInRange(range, showDropdown)

Sets the data-validation rule to require that the input is equal to a value in the given range, with an option to hide the dropdown menu.

 // Set the data validation for cell A1 to require value from B1:B10, with no dropdown menu.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var range = SpreadsheetApp.getActive().getRange('B1:B10');
 var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range, false).build();
 cell.setDataValidation(rule);
 

Parameters

Return

DataValidationBuilder — the builder, for chaining

setAllowInvalid(allowInvalidData)

Sets whether to show a warning when input fails data validation or whether to reject the input entirely. The default for new data-validation rules is true.

Parameters

Return

DataValidationBuilder — the builder, for chaining

setHelpText(helpText)

Sets the help text shown when the user hovers over the cell on which data-validation is set.

Parameters

Return

DataValidationBuilder — the builder, for chaining

withCriteria(criteria, args)

Sets the data-validation rule to require criteria defined in the DataValidationCriteria enum. This is an advanced method used primarily to create a new data-validation rule based on the criteria and arguments of an existing rule; in most other cases, the require...() methods are easier to use.

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

Parameters

Return

DataValidationBuilder — the builder, for chaining