Home>

There are methods that you don't know how to use in Google Sheets.

We are currently working on creating data input rules using GAS.
So, I am using 'getAllowInvalid ()' that restricts input other than the specified value in the DataValidation class.
At the stage of applying the created rule, the set value is overwritten with 'true', resulting in a compilation error.
I looked up the reference etc., but I don't know how to use it because I didn't write any specific usage. How should I use it?
The code is as follows: In this case, the integer value is limited to 1 ~ 100.

function myFunc () {
  var cell = SpreadsheetApp.getActive (). getRange ("B2");
  var rule = SpreadsheetApp.newDataValidation ()
    .requireNumberBetween (1,100)
    .getAllowInvalid ()
    .build ();
  cell.setDataValidation (rule);
}


'setAllowInvalid ()' could be used, but this is being validated because the default error dialog cannot be edited.

That's all for the question.

  • Answer # 1

    I don't know much about myself, so I answer about the subject of the question.

    getAllowInvalid () is the information of the obtained validation rule.
    It is a thing to check what the setting of "in case of invalid data:" is.

    Display a warning if a rule is violatedIf "set": true
    Deny input if rule is violatedIf "set": false

    function TEST () {
      var cell = SpreadsheetApp.getActive (). getRange ("B2");
      var rule = cell.getDataValidation ();
      var log = rule.getAllowInvalid ()
      Logger.log (log)

    Look at this and try to check the operation by actually setting the input rules in the cell.
    If there is no input rule setting itself,
    TypeError: Cannot call null method "getAllowInvalid"
    It becomes.

    Take a look at this site as it seems to be helpful for a while.
    [GAS] Summary of spreadsheet data input rules [with sample source]


    I also don't know because I'm not actually doing it,
    Settings related to rule violations
    setAllowInvalid (allowInvalidData)
    It seems to fit in.

    And then "Show validation help text:"
    Is setHelpText (helpText) "
    It seems to be possible.

Related articles