Skip to main content Skip to footer

How to Use Cell Values as Dropdown Items

It is possible to set cell values as items for a downdown list. For example, we might have the following cell values, with an array to put them in:

sheet.setValue(1, 2, 'item1');
        sheet.setValue(2, 2, 'item2');
        sheet.setValue(3, 2, 'item3');

        let items = [];

We'll want to set up a drowndown list with a command to open the dropdown similar to the following:

        let verticalStyle = new GC.Spread.Sheets.Style();
        verticalStyle.cellButtons = [
            {
                imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
                command: openDropdown,
                useButtonStyle: true,
            }
        ];
        verticalStyle.dropDowns = [
            {
                type: GC.Spread.Sheets.DropDownType.list,
                option: {
                    multiSelect: multiSelect,
                    items: items,
                }
            }
        ];

For the range that our cell values are in, we will create a function to get those values and add them to our items:

function populateList() {
            items.splice(0, items.length);
            for (let row = 1; row < 4; row++) {
                let value = sheet.getValue(row, 2);
                items.push({ text: value, value: value });
            }
        }

Last but not least, we'll create our custom open dropdown function:

        function openDropdown() {
            populateList();
            sheet.setStyle(1, 0, verticalStyle);
            spread.commandManager().execute({ cmd: "openList", row: 1, col: 0, sheetName: sheet.name() });
        }

See the following example here for the working code (Thanks to Ankit Kumar), as well as an alternative method for using formula list validator.

Tye Glenz