Spreadsheet Custom Field Formats

This section provides examples of custom field formats you can use in Joget spreadsheets to enhance the interactivity and functionality of your forms.

Date picker with 3 months view and disabled weekends

{{type:'date', dateFormat:'MM/DD/YYYY', correctFormat:true, defaultDate:'01/01/1900', datePickerConfig:{firstDay:0, showWeekNumber:true, numberOfMonths:3, disableDayFn:function(date) { return date.getDay() === 0 || date.getDay() === 6; }}}}

This format is useful for scheduling or booking systems where weekends are unavailable for selection. It shows three months at a time, making it easier for users to select dates within a broader range.

Date picker with 3 months view

{{type:'date', dateFormat:'MM/DD/YYYY', correctFormat:true, defaultDate:'01/01/1900', datePickerConfig:{firstDay:0, showWeekNumber:true, numberOfMonths:3}}}

It is ideal for applications that require users to pick dates from a wide range, such as project planning or timeline management, without any day restrictions.

Date picker with 3 months view, current date as default and minimum date

{{type:'date', dateFormat:'MM/DD/YYYY', correctFormat:true, defaultDate:'#date.dd/MM/yyyy#', datePickerConfig:{firstDay:0, showWeekNumber:false, numberOfMonths:3, minDate:new Date()}}}

Suitable for appointment or reservation forms where the default date should be today, and users cannot select past dates.

Selection from table

{{type: 'handsontable', handsontable: { colHeaders: ['Marque', 'Country', 'Parent company'], autoColumnSize: true, data: [{name: 'BMW', country: 'Germany', owner: 'Bayerische Motoren Werke AG'}, {name: 'Chrysler', country: 'USA', owner: 'Chrysler Group LLC'}, {name: 'Nissan', country: 'Japan', owner: 'Nissan Motor Company Ltd'}, {name: 'Suzuki', country: 'Japan', owner: 'Suzuki Motor Corporation'}, {name: 'Toyota', country: 'Japan', owner: 'Toyota Motor Corporation'}, {name: 'Volvo', country: 'Sweden', owner: 'Zhejiang Geely Holding Group'}], getValue: function() {var selection = this.getSelected(); return this.getSourceDataAtRow(selection[0]).name; }}}}

It is useful for forms that require selection from a predefined list, such as selecting a car brand and its details in a car dealership management system.

Custom renderer to change background color

{{renderer:function(instance, td, row, col, prop, value, cellProperties) { Handsontable.renderers.TextRenderer.apply(this, arguments); td.style.backgroundColor = 'green'; }}}

It is helpful for highlighting specific rows or cells based on criteria, such as marking approved items in a task list or inventory system.

Custom renderer to change text style to bold

{{renderer:function(instance, td, row, col, prop, value, cellProperties) { console.log(value); td.innerHTML='<b>'+value+'</b>'; }}}

It can emphasize important data, such as bolding the headers or key metrics in a financial report or dashboard.

Change color depending on integer value

When the integer value is less than 60, set the background color to green; otherwise, set it to red.

{{renderer:function(instance, td, row, col, prop, value, cellProperties) { console.log(value); if (parseInt(value) < 60) { td.innerHTML="<span style='color: white; background-color: green;'>"+value+"</span>"; } else { td.innerHTML="<span style='color: white; background-color: red;'>"+value+"</span>"; }}}}

Suitable for grading systems or performance tracking where different ranges of values need visual differentiation, such as student grades or sales targets.

Date picker with custom renderer

{{type:'date', dateFormat:'MM/DD/YYYY', correctFormat:true, defaultDate:'01/01/1900', datePickerConfig:{firstDay:0, showWeekNumber:true, numberOfMonths:3, disableDayFn:function(date) { return date.getDay() === 0 || date.getDay() === 6; }}, renderer:function(instance, td, row, col, prop, value, cellProperties) { Handsontable.renderers.TextRenderer.apply(this, arguments); td.style.backgroundColor = 'green'; }}}

It combines the functionality of a date picker with custom visual styling and is useful for visually indicating important dates in calendars, such as deadlines or blackout dates.

Date picker with custom renderer for different display and data formats

Display Format: DD-MM-YYYY
Data Format: YYYY-MM-DD

{{type:'date', dateFormat:'YYYY-MM-DD', renderer:function(instance, td, row, col, prop, value, cellProperties) { if (value != "") { const parts = value.split('-'); const day = parseInt(parts[2], 10); const month = parseInt(parts[1], 10) - 1 + 1; const year = parseInt(parts[0], 10); const formattedDate = day + "-" + month + "-" + year; td.innerHTML=formattedDate; }}}}

This is useful for international applications where the display format for dates needs to differ from the data storage format. It ensures clarity for users while maintaining consistency in the database.

These examples showcase various ways to enhance the functionality and interactivity of your Joget forms using custom field formats in spreadsheets.