1

I have a script that uses a formula for validation. This prevents the dropdown suggestions from appearing,but still validates/shows the red icon in the upper-left corner of the cell if what's entered is invalid. I want to do this for privacy reasons.

  const range = coachSheet.getRange(currentValuesFirstRow, 2, currentValuesNumRows);
  const topLeftCellA1 = range.offset(0,0,1,1).getA1Notation();
  const _escapeRegExCharacters = (s) => s.toString().replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  const dropdownClientsRegex = dropdownClients.map(_escapeRegExCharacters).join('|');
  const formula = `=regexmatch(${topLeftCellA1}, "(?i)^(${dropdownClientsRegex})$")`;
  const rule = SpreadsheetApp.newDataValidation()
    .requireFormulaSatisfied(formula) //this is what I need
    .setAllowInvalid(true)
    .build();
  range.setDataValidation(rule);

Is there a way to make this so that it will show suggestions, but only after 3 matching letters are entered?

I'm guessing this can't be done because GAS can't process the code while I'm typing, so I realize I'm testing the limits, but just wanted to make sure. I didn't think the code above was possible either.

I tried changing the formula on the 5th line this but it didn't work:

const formula = `=IF(LEN(${topLeftCellA1})>=3, REGEXMATCH(${topLeftCellA1}, "(?i)^(${dropdownClientsRegex})$"), TRUE)`;
SputnikDrunk2
  • 3,398
  • 1
  • 5
  • 17
user13848403
  • 43
  • 5
  • If I understood your question correctly, you are asking regarding the dropdown on Google Sheets, wherein if you type in the cell with a data validation it auto suggests the options you have but you want it to only happen once the user enters 3 letters? – Logan Apr 28 '23 at 08:25
  • `Is there a way to make this so that it will show suggestions, but only after 3 matching letters are entered?` This is an interesting security feature - have you seen/heard this implemented in _any_ other system? – Tedinoz Apr 30 '23 at 01:20
  • Related question: [Use JQuery to hide text at the start of a string](https://stackoverflow.com/q/59172740/1330560) – Tedinoz Apr 30 '23 at 01:27
  • @Logan yes, that's correct. – user13848403 May 01 '23 at 22:09
  • @Tedinoz, no I haven't seen it, but the person I'm helping has asked if this would be possible. Some level of privacy would be nice to have but not a requirement. – user13848403 May 01 '23 at 22:19

0 Answers0