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

Question

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)

Answer 1

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.

References:

• gspread
• gspread-formatting