Font Size:
Ask Joget AI

Hide Selected Values in Spreadsheet Drop-Downs

Enables the Spreadsheet Cascading Drop-Down List to hide a value selected in the previous row. This ensures that the value is not be shown as an option for the next row, preventing users from selecting the same value consecutively. However, the selected value remains available once the form is saved and reloaded.

This tutorial addresses examples that require a highly customized spreadsheet. The Spreadsheet form element in Joget uses the Handsontable library, specifically version 15.2.0 for Joget DX 8.2.0 onward. The library offers a wealth of plugins and APIs to customize cell appearance, selection, dynamic data, validation, and much more.

You can add more spreadsheet properties that are available in the library documentation. The configuration is formatted as JSON. Usually, a single object and value in Custom Settings would suffice for simple configurations. Combine this with a Custom HTML form element for more complex configurations to complete the functionality.

See the section Spreadsheet Deep Customizations for more examples.

To achieve the desired result, you use a Custom HTML form element and Custom Settings within the form element properties.

Instructions

Each option in the spreadsheet is a list of data from another form with a Form Data Store configuration. This tutorial will guide you step-by-step on configuring the Spreadsheet Form Element for this example.

  1. Paste the following code into the HTML elements from Form Builder > Spreadsheet Configuration > UI > Custom Settings (JSON). The following code adjust the content of the Continent drop-down menu dynamically based on user edits in the table. This code removes values from the drop-down if they are edited to be non-empty and adds them back if they are edited to be empty.
    {
        afterInit: function() {
            var hot = this;
            $(hot.rootElement).data('hot', hot);
        },
        cells: function(row, col, prop) {
            var cellProperties = {};
            if (prop === 'continent') {
                cellProperties.source = function(query, process) {
                    var hot = this.instance;
                    var data = hot.getSourceData();
    
                    var usedCountries = {};
                    data.forEach(function(r, i) {
                        if (i !== row && r.country) usedCountries[r.country] = true;
                    });
    
                    var byContinent = {};
                    (window.countryContinentMap || []).forEach(function(r) {
                        if (!byContinent[r.continent]) byContinent[r.continent] = [];
                        byContinent[r.continent].push(r.country);
                    });
    
                    var keyOptions = hot.getCellMeta(row, col).keyOptions.filter(function(opt) {
                        if (opt.value === '') return true;
                        var countries = byContinent[opt.value] || [];
                        return countries.some(function(c) { return !usedCountries[c]; });
                    });
    
                    hot.setCellMeta(row, col, 'keyOptions', keyOptions);
                    process(keyOptions.map(function(o) { return o.label; }));
                };
            }
            return cellProperties;
        }
    }

  2. Add a Custom HTML form element to the form and paste the following script.
    <script>
    window.countryContinentMap = #beanshell.continentCountryMap#;
    </script>

  3. The Continent drop-down only shows values not selected by the user in previous rows. This ensures that each value is unique per row. However, the values reappear after the form is saved and reloaded.

This tutorial closely relates to creating a Spreadsheet Cascading Drop-Down List and uses the same app with adjustments as shown above.

Related documentation

For further reading related topics, explore the following articles:

Download sample app

Download the demo app for Hide Selected Values in Spreadsheet Drop-Downs:
Created by Julieth Last modified by Nur Baizura Badrul Sham on Nov 06, 2026