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/dist/flatpickr.min.css"
/>
<script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>
<script src="https://cdn.jsdelivr.net/npm/flatpickr/dist/plugins/rangePlugin.js"></script>
<script src="https://cdn.jsdelivr.net/npm/flatpickr/dist/plugins/monthSelect/index.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
    const DateRangeEditor = Handsontable.editors.TextEditor.prototype.extend();

    DateRangeEditor.prototype.createElements = function () {
      Handsontable.editors.TextEditor.prototype.createElements.apply(
        this,
        arguments
      );

      // Create a Flatpickr instance for date range 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";
      this.instance.rootElement.appendChild(this.datePicker);

      this.flatpickrInstance = flatpickr(this.datePicker, {
        mode: "range",
        dateFormat: "Y-m-d",
        onChange: (selectedDates) => {
          if (selectedDates.length === 2) {
            const value = formatDateRange(selectedDates[0], selectedDates[1]);

            // Update the Handsontable cell value
            this.instance.setDataAtCell(this.row, this.col, value);
            hot.setDataAtRowProp(this.row, this.col, value);
          }
        },
        onClose: () => {
          this.finishEditing();
          this.datePicker.style.display = "none";
        },
        onOpen: () => {
          this.datePicker.style.display = "block";
        },
      });
    };

    DateRangeEditor.prototype.open = function () {
      const cellOffset = this.TD.getBoundingClientRect();
      const containerOffset = this.instance.rootElement.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.instance.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();
      }
      this.flatpickrInstance.open();
    };

    DateRangeEditor.prototype.close = function () {
      Handsontable.editors.TextEditor.prototype.close.apply(this, arguments);
    };

    DateRangeEditor.prototype.destroy = function () {
      this.flatpickrInstance.destroy();
      this.datePicker.remove();
      Handsontable.editors.TextEditor.prototype.destroy.apply(this, arguments);
    };

    // Custom Month-Year Picker Editor
    const MonthYearEditor = Handsontable.editors.TextEditor.prototype.extend();

    MonthYearEditor.prototype.createElements = function () {
      Handsontable.editors.TextEditor.prototype.createElements.apply(
        this,
        arguments
      );

      // 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";

      this.instance.rootElement.appendChild(this.datePicker);

      this.flatpickrInstance = flatpickr(this.datePicker, {
        plugins: [
          new monthSelectPlugin({
            shorthand: true,
            dateFormat: "Y-m",
            altFormat: "F Y",
          }),
        ],
        onChange: (selectedDates) => {
          if (selectedDates.length === 1) {
            const selectedMonthYear = extractMonthYear(selectedDates[0]);
            this.instance.setDataAtCell(this.row, this.col, selectedMonthYear);
            hot.setDataAtRowProp(this.row, this.col, selectedMonthYear);
          }
        },
        onClose: () => {
          this.finishEditing();
          this.datePicker.style.display = "none";
        },
        onOpen: () => {
          this.datePicker.style.display = "block";
        },
      });
    };

    MonthYearEditor.prototype.open = function () {
      const cellOffset = this.TD.getBoundingClientRect();
      const containerOffset = this.instance.rootElement.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.instance.getDataAtCell(this.row, this.col);
      if (cellValue && cellValue?.includes("-")) {
        this.flatpickrInstance.setDate([new Date(cellValue)]);
      } else {
        this.flatpickrInstance.clear();
      }

      this.flatpickrInstance.open();
    };

    MonthYearEditor.prototype.close = function () {
      Handsontable.editors.TextEditor.prototype.close.apply(this, arguments);
    };

    MonthYearEditor.prototype.destroy = function () {
      this.flatpickrInstance.destroy();
      this.datePicker.remove();
      Handsontable.editors.TextEditor.prototype.destroy.apply(this, arguments);
    };

    // 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 fourth 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