Add Date Range Picker in Spreadsheet

Introduction

This guide demonstrates how to implement a date range picker in Joget DX using custom HTML and JavaScript code. A date range picker is an essential feature for applications that require users to select start and end dates, such as booking systems, project trackers, or report generators.

The implementation leverages Flatpickr’s robust functionalities, including range selection and month-year picking, ensuring a seamless and user-friendly experience. This tutorial also showcases custom editors integrated with Handsontable to enhance low-code project workflows.

By following this guide, users can:

• Enable dynamic date range selection for enhanced user input.

• Provide intuitive and customized month-year pickers for specific use cases.

• Simplify development with reusable components in low-code environments. 

How does it work?

To implement a date range picker in spreadsheets, use the Custom HTML field under the spreadsheet field in the form.

1. Enable the Handsontable instance to be interactable by pasting the following code into the HTML elements from Form Builder > Spreadsheet Configuration > UI > Custom Settings (JSON).
   

   {
       "afterInit": function() {
           var hot = this;
           $(hot.rootElement).data("hot", hot);
       }
   }

   
   
    
2. In the spreadsheet, set the format type as Text in the date range, month-year columns as below in the snippet.
   
3. Add the following script to the Custom HTML field.
   

   <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr@4.6.13/dist/flatpickr.min.css">
   <script src="https://cdn.jsdelivr.net/npm/flatpickr@4.6.13/dist/flatpickr.min.js"></script>
   <script src="https://cdn.jsdelivr.net/npm/flatpickr@4.6.13/dist/plugins/rangePlugin.min.js"></script>
   <script src="https://cdn.jsdelivr.net/npm/flatpickr@4.6.13/dist/plugins/monthSelect/index.min.js"></script>
    
   <style>
     .flatpickr-current-month .numInputWrapper {
       width: 74px;
     }
     .flatpickr-calendar.animate.open {
       min-width: 410px;
     }
     .flatpickr-rContainer {
       width: 100%;
     }
     .flatpickr-monthSelect-months {
       display: flex;
       justify-content: space-around;
       width: 100%;
     }
     span.flatpickr-monthSelect-month {
       padding: 0 4px;
       cursor: pointer;
     }
     span.flatpickr-monthSelect-month:hover {
       background: #f0f0f0;
     }
   </style>
    
   <script>
     $(document).ready(function () {
       const extractMonthYear = (date) => {
         const options = { year: "numeric", month: "2-digit" };
         return new Date(date).toLocaleDateString("en-CA", options);
       };
       const formatDate = (date) => {
         const options = { year: "numeric", month: "2-digit", day: "2-digit" };
         return new Date(date).toLocaleDateString("en-CA", options);
       };
       const formatDateRange = (start, end) => {
         return `${formatDate(start)} to ${formatDate(end)}`;
       };
    
       // Custom Date Range Editor for Handsontable 15.2.0
       class DateRangeEditor extends Handsontable.editors.TextEditor {
   
           
         constructor(hotInstance, row, col, prop, value, cellProperties) {
           super(hotInstance, row, col, prop, value, cellProperties);
           this.datePicker = null;
           this.flatpickrInstance = null;
         }
   
         createElements() {
           
           super.createElements();
   
           this.datePicker = document.createElement("input");
           this.datePicker.type = "text";
           this.datePicker.style.position = "absolute";
           this.datePicker.style.visibility = "hidden";
           this.datePicker.style.zIndex = "9999999";
           
           const targetBody = this.hot.rootDocument ? this.hot.rootDocument.body : document.body;
           targetBody.appendChild(this.datePicker);
   
           this.flatpickrInstance = flatpickr(this.datePicker, {
             mode: "range",
             dateFormat: "Y-m-d",
             onChange: (selectedDates) => {
               console.log('DateRange onChange - selectedDates:', selectedDates);
               if (selectedDates.length === 2) {
                 const value = formatDateRange(selectedDates[0], selectedDates[1]);
                 // Update the Handsontable cell value
                 this.hot.setDataAtCell(this.row, this.col, value);
                 setTimeout(() => {
                   this.finishEditing();
                   this.datePicker.style.display = "none";
                 }, 100);
               }
             },
             onClose: () => {
               this.finishEditing();
               this.datePicker.style.display = "none";
             },
             onOpen: () => {
               this.datePicker.style.display = "block";
             },
           });
         }
   
         open() {
           
           if (!this.datePicker || !this.flatpickrInstance) {
             console.log('Recreating elements in open method');
             this.createElements();
           }
           
           const cellOffset = this.TD.getBoundingClientRect();
           const targetBody = this.hot.rootDocument ? this.hot.rootDocument.body : document.body;
           const containerOffset = targetBody.getBoundingClientRect();
   
           this.datePicker.style.top = `${
             cellOffset.top - containerOffset.top + this.TD.offsetHeight
           }px`;
           this.datePicker.style.left = `${
             cellOffset.left - containerOffset.left
           }px`;
           this.datePicker.style.display = "block";
   
           const cellValue = this.hot.getDataAtCell(this.row, this.col);
           if (cellValue && cellValue.includes(" to ")) {
             const [startDate, endDate] = cellValue.split(" to ");
             this.flatpickrInstance.setDate([
               new Date(startDate),
               new Date(endDate),
             ]);
           } else {
             this.flatpickrInstance.clear();
           }
           setTimeout(() => {
               this.flatpickrInstance.open();
           }, 10);
         }
   
         close() {
           super.close();
         }
   
         destroy() {
           if (this.flatpickrInstance) {
             this.flatpickrInstance.destroy();
           }
           if (this.datePicker) {
             this.datePicker.remove();
           }
           super.destroy();
         }
       }
   
       // Custom Month-Year Picker Editor for Handsontable 15.2.0
       class MonthYearEditor extends Handsontable.editors.TextEditor {
         constructor(hotInstance, row, col, prop, value, cellProperties) {
           super(hotInstance, row, col, prop, value, cellProperties);
           this.datePicker = null;
           this.flatpickrInstance = null;
         }
   
         createElements() {
           super.createElements();
   
           // Create a Flatpickr instance for month-year selection
           this.datePicker = document.createElement("input");
           this.datePicker.type = "text";
           this.datePicker.style.position = "absolute";
           this.datePicker.style.visibility = "hidden";
           this.datePicker.style.zIndex = "9999999";
   
           // Use the correct way to append to document body in v15.2.0
           // Check if rootDocument exists, fallback to document if not
           const targetBody = this.hot.rootDocument ? this.hot.rootDocument.body : document.body;
           targetBody.appendChild(this.datePicker);
   
           this.flatpickrInstance = flatpickr(this.datePicker, {
             plugins: [
               new monthSelectPlugin({
                 shorthand: true,
                 dateFormat: "Y-m",
                 altFormat: "F Y",
               }),
             ],
             onChange: (selectedDates) => {
               console.log('MonthYear onChange - selectedDates:', selectedDates);
               if (selectedDates.length === 1) {
                 const selectedMonthYear = extractMonthYear(selectedDates[0]);
                 this.hot.setDataAtCell(this.row, this.col, selectedMonthYear);
                 // Close after selection for month-year picker
                 setTimeout(() => {
                   this.finishEditing();
                   this.datePicker.style.display = "none";
                 }, 100);
               }
             },
             onClose: () => {
               console.log('MonthYear onClose');
               this.finishEditing();
               this.datePicker.style.display = "none";
             },
             onOpen: () => {
               console.log('MonthYear onOpen');
               this.datePicker.style.display = "block";
             },
           });
         }
   
         open() {
           if (!this.datePicker || !this.flatpickrInstance) {
             console.log('Recreating elements in MonthYearEditor open method');
             this.createElements();
           }
           
           const cellOffset = this.TD.getBoundingClientRect();
           const targetBody = this.hot.rootDocument ? this.hot.rootDocument.body : document.body;
           const containerOffset = targetBody.getBoundingClientRect();
   
           this.datePicker.style.top = `${
             cellOffset.top - containerOffset.top + this.TD.offsetHeight
           }px`;
           this.datePicker.style.left = `${
             cellOffset.left - containerOffset.left
           }px`;
           this.datePicker.style.display = "block";
   
           const cellValue = this.hot.getDataAtCell(this.row, this.col);
           if (cellValue && cellValue?.includes("-")) {
             this.flatpickrInstance.setDate([new Date(cellValue)]);
           } else {
             this.flatpickrInstance.clear();
           }
   
           setTimeout(() => {
               this.flatpickrInstance.open();
           }, 10);
         }
   
         close() {
           super.close();
         }
   
         destroy() {
           if (this.flatpickrInstance) {
             this.flatpickrInstance.destroy();
           }
           if (this.datePicker) {
             this.datePicker.remove();
           }
           super.destroy();
         }
       }
   
       // Get the Handsontable instance
       var hot = FormUtil.getField("sp_custom").data("hot");
       console.log("hot :>> ", hot);
       if (hot) {
         hot.updateSettings({
           columns: hot.getSettings().columns.map((column, index) => {
             if (index === 0) {
               // apply the column position index (0 for the first column)
               return {
                 ...column,
                 editor: DateRangeEditor,
               };
             } else if (index === 1) {
               // Apply Month-Year Picker Editor to the second column
               return {
                 ...column,
                 editor: MonthYearEditor,
               };
             }
             return column;
           }),
         });
       }
     });
   </script>

4. Adjust the index (0, 1) value in lines 202 and 208 respectively, according to your position of the date range field column.

Expected Outcome

You can now select dates in a date range picker in Joget DX by clicking on the date range cell in the spreadsheet, as demonstrated in the image below.

Related Documentation

Please refer to the following article for further information:

• Spreadsheet Deep Customizations
• Spreadsheet Custom Field Formats
• Handsontable: Core

Download Sample App