How to apply data validation based JavaScript Spreadsheet?

This article explains how to apply data validation based on the value selected from the DatePicker in JavaScript Spreadsheet. Render the DatePicker in the cell containing the date value using the beforeCellRender event. Apply data validation to the DatePicker control’s change event using the addDataValidation method.

[HTML]

 <div id="spreadsheet"></div> 

[TS]

let spreadsheet = new ej.spreadsheet.Spreadsheet({
 sheets: [
   {
     name: 'Car Sales Report',
     ranges: [{ dataSource: defaultData }],
     columns: [
       { width: 180 },
       { width: 130 },
       { width: 130 },
       { width: 180 },
       { width: 130 },
       { width: 120 },
     ],
   },
 ],
 created: function () {
   spreadsheet.cellFormat(
     { fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },
     'A1:F1'
   );
   // Method to bind the DatePicker to cells.
   bindDatePicker();
 },
 beforeCellRender: function (args) {
   // Condition to check the active sheet and the rendered cell is not header cell.
   if (
     spreadsheet.activeSheetIndex === 0 &&
     !args.element.classList.contains('e-header-cell')
   ) {
     // Render templates dynamically for date range cells.
     let target = args.element.firstElementChild;
     if (target) {
       // Convert the input element as an EJ2 DatePicker control.
       new ej.calendars.DatePicker(
         {
           placeholder: 'Delivery Date',
           value: spreadsheet.getDisplayText(args.cell),
           // Event that gets triggered when the value is changed.
           change: onDateChange,
         },
         target
       );
     }
   }
 },
});

spreadsheet.appendTo('#spreadsheet');

// Function for data picker change event.
function onDateChange(args) {
 let datePickerValue = args.value;
 let activeCell = spreadsheet.getActiveSheet().activeCell;
 let date = datePickerValue.toLocaleDateString();
 // Update the value to the cell model.
 spreadsheet.updateCell({ value: date, format: 'MM-dd-yyyy' }, activeCell);
 let cellIdx = ejs.spreadsheet.getRangeIndexes(activeCell);
 let cell = ejs.spreadsheet.getCell(
   cellIdx[0],
   cellIdx[1],
   spreadsheet.getActiveSheet()
 );
 // Fetching the current date.
 let today = new Date().toLocaleDateString();
 if (cell.format === 'MM-dd-yyyy') {
   // Apply validation to the cell.
   spreadsheet.addDataValidation({ type: 'Date', operator: 'LessThanOrEqualTo', value1: today, isHighlighted: true}, activeCell);
 }
}

function bindDatePicker() {
 let sheet = spreadsheet.getActiveSheet();
 let range = sheet.usedRange;
 let rangeCollection = [];
 // Iteration between the used range to update 
 for (let i = 0; i <= range.rowIndex; i++) {
   let cells = sheet.rows[i].cells;
   for (let j = 0; j <= range.colIndex; j++) {
     // Condition to check whether the fetched cell constains date value.
     if (cells[j] && cells[j].value && cells[j].format == 'mm-dd-yyyy') {
       let address = ejs.spreadsheet.getCellAddress(i, j);
       let ranges = {
         template: '<input />',
         address: address, // Specify the range you want.
       };
       rangeCollection.push(ranges);
     }
   }
 }
 spreadsheet.sheets[0].ranges = rangeCollection;
 spreadsheet.resize(); // To refresh the spreadsheet.
}

Sample Link:

Refer to the working sample for additional details and implementation: Sample

Output:

Documentation link:

https://ej2.syncfusion.com/javascript/documentation/spreadsheet/cell-range#data-validation

https://ej2.syncfusion.com/javascript/documentation/spreadsheet/template

https://ej2.syncfusion.com/javascript/documentation/datepicker/es5-getting-started

Demo link:

https://ej2.syncfusion.com/javascript/demos/#/material3/spreadsheet/cell-template.html

https://ej2.syncfusion.com/javascript/demos/#/material3/spreadsheet/data-validation.html

https://ej2.syncfusion.com/javascript/demos/#/material3/datepicker/default.html

Conclusion

We hope you enjoyed learning how to apply data validation based on the value selected from the DatePicker in the JavaScript Spreadsheet.

You can refer to our JavaScript Spreadsheet feature tour page to learn about its other groundbreaking feature representations.
Documentation explains how to quickly get started with configuration specifications. You can also explore our
JavaScript Spreadsheet example to understand how to create and manipulate data.

For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.

If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums,
BoldDesk Support, or feedback portal. We are always happy to assist you!