Add empty option to List Validator

Question

I'm trying to add the option to my users that, on a List Validator, to allow select any of the options or a blank option. Spreadjs has the IgnoreBlanks setting, which I use, so when the user uses the delete key or the backspace and deletes the cell it validates correctly. However, I would love to use the same functionality as in Excel, which allows blank options in the list validator, in part of the list.

I've tried to target the <select> element that holds the list and programmatically add the empty element, however, it crashes after the user selects the empty option. I've also tried to add different escaped characters to the list. If I select a character that represents an empty string or a tab, it won't add a new option to the list. If I use any strange character, or even the null character \0 you get a new option to select, but the content is that typical rectangle you see when your font doesn't have the character you're trying to display.

I've also tested using a regular ListValidator like in the example pages, not our custom functionality and doesn't work either.

https://www.grapecity.com/demos/spread/JS/TutorialSample/#/demos/basicDataValidator

I have also tried creating a FormulaListValidator, and if my range has empty cells I could then get an empty option on my list, however, because the range may have duplicates, I get duplicated options.

Answer 1

After researching a little bit I found a workaround in a different language which I adapted to Typescript (Angular 6)

export const getListValidatorFromArray = (spread: GC.Spread.Sheets.Workbook, data: any[]) => {
  // saving validation list values in a hidden sheet
  spread.addSheet(spread.getSheetCount());
  const sheet = spread.getSheet(spread.getSheetCount() - 1);
  sheet.visible(false);
  for (let i = 0; i < data.length; i++) {
    sheet.setValue(i, 0, data[i]);
  }
  // create validator based on the values
  const dv = GC.Spread.Sheets.DataValidation.createFormulaListValidator(
    '=' + sheet.name() + '!$A$1:' + sheet.name() + '!$A$' + data.length
  );
  return dv;
};

Note: This creates an extra sheet for each validator you create. Makes sure you reuse them as much as possible (i.e. assigning it to a variable when it's created, and reusing the variable for other columns/rows that use the same one).