// 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.');}
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.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);
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
// 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.');}
// 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 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
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Missing the information I need","missingTheInformationINeed","thumb-down"],["Too complicated / too many steps","tooComplicatedTooManySteps","thumb-down"],["Out of date","outOfDate","thumb-down"],["Samples / code issue","samplesCodeIssue","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2024-12-03 UTC."],[[["\u003cp\u003e\u003ccode\u003eDataValidation\u003c/code\u003e objects give you access to data validation rules applied to spreadsheet ranges.\u003c/p\u003e\n"],["\u003cp\u003eYou can use the \u003ccode\u003eSpreadsheetApp.newDataValidation()\u003c/code\u003e and \u003ccode\u003eDataValidationBuilder\u003c/code\u003e classes to programmatically create new validation rules in your sheet.\u003c/p\u003e\n"],["\u003cp\u003e\u003ccode\u003eDataValidation\u003c/code\u003e provides methods such as \u003ccode\u003egetCriteriaType()\u003c/code\u003e, \u003ccode\u003egetCriteriaValues()\u003c/code\u003e and \u003ccode\u003egetHelpText()\u003c/code\u003e to examine details of an existing validation rule.\u003c/p\u003e\n"],["\u003cp\u003eUsing \u003ccode\u003ecopy()\u003c/code\u003e and \u003ccode\u003ewithCriteria()\u003c/code\u003e on an existing \u003ccode\u003eDataValidation\u003c/code\u003e object helps modify current validation rules without recreating them from scratch.\u003c/p\u003e\n"],["\u003cp\u003e\u003ccode\u003egetAllowInvalid()\u003c/code\u003e allows you to determine if a rule will show warnings or reject invalid input outright.\u003c/p\u003e\n"]]],[],null,["DataValidation\n\nAccess data validation rules. To create a new rule, use [SpreadsheetApp.newDataValidation()](/apps-script/reference/spreadsheet/spreadsheet-app#newDataValidation()) and [DataValidationBuilder](/apps-script/reference/spreadsheet/data-validation-builder). You can use\n[Range.setDataValidation(rule)](/apps-script/reference/spreadsheet/range#setDataValidation(DataValidation)) to set the validation rule for a range.\n\n```javascript\n// Log information about the data validation rule for cell A1.\nconst cell = SpreadsheetApp.getActive().getRange('A1');\nconst rule = cell.getDataValidation();\nif (rule != null) {\n const criteria = rule.getCriteriaType();\n const args = rule.getCriteriaValues();\n Logger.log('The data validation rule is %s %s', criteria, args);\n} else {\n Logger.log('The cell does not have a data validation rule.');\n}\n``` \n\nMethods\n\n| Method | Return type | Brief description |\n|---------------------------------------------|---------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------|\n| [copy()](#copy()) | [DataValidationBuilder](/apps-script/reference/spreadsheet/data-validation-builder) | Creates a builder for a data validation rule based on this rule's settings. |\n| [getAllowInvalid()](#getAllowInvalid()) | `Boolean` | Returns `true` if the rule shows a warning when input fails data validation, or `false` if it rejects the input entirely. |\n| [getCriteriaType()](#getCriteriaType()) | [DataValidationCriteria](/apps-script/reference/spreadsheet/data-validation-criteria) | Gets the rule's criteria type as defined in the [DataValidationCriteria](/apps-script/reference/spreadsheet/data-validation-criteria) enum. |\n| [getCriteriaValues()](#getCriteriaValues()) | `Object[]` | Gets an array of arguments for the rule's criteria. |\n| [getHelpText()](#getHelpText()) | `String` | Gets the rule's help text, or `null` if no help text is set. |\n\nDetailed documentation \n\n`copy()` \nCreates a builder for a data validation rule based on this rule's settings.\n\n```javascript\n// Change existing data validation rules that require a date in 2013 to require\n// a date in 2014.\nconst oldDates = [new Date('1/1/2013'), new Date('12/31/2013')];\nconst newDates = [new Date('1/1/2014'), new Date('12/31/2014')];\nconst sheet = SpreadsheetApp.getActiveSheet();\nconst range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());\nconst rules = range.getDataValidations();\n\nfor (let i = 0; i \u003c rules.length; i++) {\n for (let j = 0; j \u003c rules[i].length; j++) {\n const rule = rules[i][j];\n\n if (rule != null) {\n const criteria = rule.getCriteriaType();\n const args = rule.getCriteriaValues();\n\n if (criteria === SpreadsheetApp.DataValidationCriteria.DATE_BETWEEN &&\n args[0].getTime() === oldDates[0].getTime() &&\n args[1].getTime() === oldDates[1].getTime()) {\n // Create a builder from the existing rule, then change the dates.\n rules[i][j] = rule.copy().withCriteria(criteria, newDates).build();\n }\n }\n }\n}\nrange.setDataValidations(rules);\n```\n\nReturn\n\n\n[DataValidationBuilder](/apps-script/reference/spreadsheet/data-validation-builder) --- a builder based on this rule's settings\n\n*** ** * ** ***\n\n`get``Allow``Invalid()` \nReturns `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`.\n\nReturn\n\n\n`Boolean` --- `true` if the rule allows input that fails data validation; `false` if not\n\n*** ** * ** ***\n\n`get``Criteria``Type()` \nGets the rule's criteria type as defined in the [DataValidationCriteria](/apps-script/reference/spreadsheet/data-validation-criteria) enum. To get the\narguments for the criteria, use [getCriteriaValues()](#getCriteriaValues()). To use these values to create or\nmodify a data validation rule, see [DataValidationBuilder.withCriteria(criteria, args)](/apps-script/reference/spreadsheet/data-validation-builder#withCriteria(DataValidationCriteria,Object)).\n\n```javascript\n// Log information about the data validation rule for cell A1.\nconst cell = SpreadsheetApp.getActive().getRange('A1');\nconst rule = cell.getDataValidation();\nif (rule != null) {\n const criteria = rule.getCriteriaType();\n const args = rule.getCriteriaValues();\n Logger.log('The data validation rule is %s %s', criteria, args);\n} else {\n Logger.log('The cell does not have a data validation rule.');\n}\n```\n\nReturn\n\n\n[DataValidationCriteria](/apps-script/reference/spreadsheet/data-validation-criteria) --- the type of data validation criteria\n\n*** ** * ** ***\n\n`get``Criteria``Values()` \nGets an array of arguments for the rule's criteria. To get the criteria type, use [getCriteriaType()](#getCriteriaType()). To use these values to create or modify a data validation rule, see [DataValidationBuilder.withCriteria(criteria, args)](/apps-script/reference/spreadsheet/data-validation-builder#withCriteria(DataValidationCriteria,Object)).\n\n```javascript\n// Log information about the data validation rule for cell A1.\nconst cell = SpreadsheetApp.getActive().getRange('A1');\nconst rule = cell.getDataValidation();\nif (rule != null) {\n const criteria = rule.getCriteriaType();\n const args = rule.getCriteriaValues();\n Logger.log('The data validation rule is %s %s', criteria, args);\n} else {\n Logger.log('The cell does not have a data validation rule.');\n}\n```\n\nReturn\n\n\n`Object[]` --- an array of arguments appropriate to the rule's criteria type; the number of arguments\nand their type match the corresponding `require...()` method of the [DataValidationBuilder](/apps-script/reference/spreadsheet/data-validation-builder) class\n\n*** ** * ** ***\n\n`get``Help``Text()` \nGets the rule's help text, or `null` if no help text is set.\n\nReturn\n\n\n`String` --- the rule's help text, or `null` if no help text is set"]]
