How can i create DataValidationRule in Google DOCS API via gspread-formatting?-CodePudding

I need to create DataValidationRule: only numbers in cell.

validation_rule = DataValidationRule(
        BooleanCondition(???),
        showCustomUi=True)

...

set_data_validation_for_cell_range(sheet_, 'A2:B2', validation_rule)

CodePudding user response：

I believe your goal is as follows.

You want to set the data validation rule to the cells "A2:B2" as only the numbers.

I think that gspread doesn't use Google Docs API. Ref From your script, I thought that you wanted to achieve your goal using Sheets API with gspread. And also, I thought that you are using a library of "gspread-formatting". Ref

If my understanding is correct, how about the following modification?

Modified script:

In this modification, a custom formula is used for achieving your goal.

validation_rule = DataValidationRule(
    BooleanCondition('CUSTOM_FORMULA', ['=ISNUMBER(A2)']),
    showCustomUi=True
)
set_data_validation_for_cell_range(worksheet, 'A2:B2', validation_rule)

When this script is run, only the numbers can be inputted to the cells of "A2:B2". When the string is inputted, the warning occurs.

Note:

This script supposes that you have already been able to get and put values for Google Spreadsheet using Sheets API. Please be careful about this.
As the additional information, for example, when you want to use a number more than 0, you can also modify the following script. You can see the detailed information at the official document.
```
  validation_rule = DataValidationRule(
      BooleanCondition('NUMBER_GREATER', ['0']),
      showCustomUi=True
  )
```
And, when strict=True is used, only the numbers can be inputted.

Modified script:

Note:

References: