spreadSheetAddDataValidationRule

Last updated on Feb 25, 2025

Description

Data validation allows you to enforce restrictions on the type of data you enter in specific spreadsheet cells. You can use rules to ensure that the data you enter your spreadsheet is accurate, consistent, and adheres to the rules.

How is validation useful

Adding a data validation rule in a spreadsheet allows you to:

Prevent errors: Minimizes errors in data entry by restricting the data one can enter.

Maintain data integrity: Ensures the data is in a consistent format.

Improve data quality: Ensures one can enter only valid data, critical for accurate reporting.

Types of data validation in ColdFusion:

Integer: Allows only integers. For example, the age or size of an apartment.

Double: Allows only decimal numbers. For example, commodity prices or distance between two places.

List: Restricts entry to a pre-defined list of values. For example, selecting from department names or product categories.

Date: Ensures the entry is valid depending on specified date conditions. For example, setting project deadlines or ensuring that date of birth is within a range.

Text length: Restricts the number of characters in a cell. For example, zip codes, phone numbers, or employee IDs.

Returns

None

History

ColdFusion (2025 release): Added the function.

Syntax

spreadSheetAddDataValidationRule(spreadSheetObject, validationRule)

Parameters

Name	Required	Type	Description
spreadSheetObject	Yes	ExcelInfo	The Excel spreadsheet object to which to add the data validation.
validationRule	Yes	Struct	A struct containing the following rules: validationType: Select one of the following: Date Double Integer List Text_Length regions: Defines the area in the spreadsheet you’ll enter data subject to validation. It is an array of structs defining the start and end rows, and the start and end columns of the data entry area. If the area is in a single column, specify regions as {startRow:2, startColumn:1, endRow: 1, endColumn: 1} If the area spans more than one column, specify regions as [ { startRow : 1, startColumn : 1, endRow : 1, endColumn : 1 }, { startRow : 2, startColumn : 1, endRow : 2, endColumn : 4}] value: the value that the data entered in the range is validated against. operator: Select one of the following: greater_than less_than greater_or_equal less_or_equal equal not_equal between not_between minValue: Defines the minimum value of the validation range. Use it when the operator is between or not_between. The key doesn’t apply to the List validation type. maxValue: Defines the maximum value of the validation range. Use it when the operator is between or not_between. The key doesn’t apply to the List validation type. alertTitle: The title of the error message box. alertText: The error message that appears when validation fails. cellSelectTitle: The title of the message box when you select a cell. cellSelectText: The text that appears in a box when you select a cell.

Example - Integer validation

Example 1- using greater_than

<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-gt.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "greater_than", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer greater than 94." 
    } 

    try{ 
        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 
    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 2- using less_than

<cfscript> 

    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-lt.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "less_than", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer less than 94." 

    } 

    try{ 
        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 
    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 3- using greater_or_equal

<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-gte.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 

    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "greater_or_equal", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer greater or equal to 94." 
    } 

    try{ 

        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 

    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 4- using less_or_equal

<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-lte.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 

    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "less_or_equal", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer less than or equal to 94." 

    } 

    try{ 
        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 
    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 5- using equal

<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-equal.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "equal", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer equal to 94." 
    } 

    try{ 
        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 
    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 6- using not_equal

<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-not-equal.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "not_equal", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer not equal to 94." 
    } 

    try{ 

        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 
    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 7- using between

<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-between.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        minValue : 50, 
        maxValue: 100, 
        operator : "between", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer between 50-100." 
    } 

    try{ 

        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 

    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 8- using not_between

<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-not-between.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        minValue : 50, 
        maxValue: 100, 
        operator : "not_between", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer not between 50-100." 
    } 

    try{ 

        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 

    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Get help faster and easier

New user?