Class DataValidation

  • UseSpreadsheetApp.newDataValidation() andDataValidationBuilder to create new data validation rules.

  • TheRange.setDataValidation(rule) method sets a data validation rule for a specific range.

  • Data validation rules have methods to get their criteria type, criteria values, whether invalid input is allowed, and associated help text.

  • Thecopy() method creates a builder based on an existing data validation rule's settings, allowing for modifications.

DataValidation

Access data validation rules. To create a new rule, useSpreadsheetApp.newDataValidation() andDataValidationBuilder. You can useRange.setDataValidation(rule) to set the validation rule for a range.

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

MethodReturn typeBrief description
copy()DataValidationBuilderCreates a builder for a data validation rule based on this rule's settings.
getAllowInvalid()BooleanReturnstrue if the rule shows a warning when input fails data validation, orfalse if it rejects the input entirely.
getCriteriaType()DataValidationCriteriaGets the rule's criteria type as defined in theDataValidationCriteria enum.
getCriteriaValues()Object[]Gets an array of arguments for the rule's criteria.
getHelpText()StringGets the rule's help text, ornull 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.constoldDates=[newDate('1/1/2013'),newDate('12/31/2013')];constnewDates=[newDate('1/1/2014'),newDate('12/31/2014')];constsheet=SpreadsheetApp.getActiveSheet();constrange=sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());construles=range.getDataValidations();for(leti=0;i <rules.length;i++){for(letj=0;j <rules[i].length;j++){construle=rules[i][j];if(rule!=null){constcriteria=rule.getCriteriaType();constargs=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()

Returnstrue if the rule shows a warning when input fails data validation, orfalse if it rejects the input entirely. The default for new data validation rules istrue.

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 theDataValidationCriteria enum. To get thearguments for the criteria, usegetCriteriaValues(). To use these values to create ormodify a data validation rule, seeDataValidationBuilder.withCriteria(criteria, args).

// Log information about the data validation rule for cell A1.constcell=SpreadsheetApp.getActive().getRange('A1');construle=cell.getDataValidation();if(rule!=null){constcriteria=rule.getCriteriaType();constargs=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, usegetCriteriaType(). To use these values to create or modify a data validation rule, seeDataValidationBuilder.withCriteria(criteria, args).

// Log information about the data validation rule for cell A1.constcell=SpreadsheetApp.getActive().getRange('A1');construle=cell.getDataValidation();if(rule!=null){constcriteria=rule.getCriteriaType();constargs=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 correspondingrequire...() method of theDataValidationBuilder class

getHelpText()

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

Return

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

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-11 UTC.