Adding a value to a cell breaks conditional formatting in google sheets using gspread

Question

I have a google sheets worksheet which looks like this:

col1        col2        col3        col4
ABC         10          ABC         145.1
ABC         15.5        ABC         574.5
ABC         26.7        ABC         45.4

I mark cells red if their value is less than a given value and it works:

rule = ConditionalFormatRule(
    ranges=[GridRange.from_a1_range('B:D', worksheet)],
    booleanRule=BooleanRule(
        condition=BooleanCondition('NUMBER_LESS', ['100']),
        format=CellFormat(backgroundColor=Color(0.992, 0.529, 0.529))
    )
)

rules = get_conditional_format_rules(worksheet)
rules.append(rule)
rules.save()

I add % sign to col2 & col4 like so:

cell_list = [worksheet.range('B2:B4'), worksheet.range('D2:D4')]

sign_to_add = '%'

for item in cell_list:
    for val in item:
        val.value = val.value + sign_to_add 
        
for item in cell_list:
    worksheet.update_cells(item) 

col1        col2        col3        col4
ABC         10%         ABC         145.1%
ABC         15.5%       ABC         574.5%
ABC         26.7%       ABC         45.4%

But this breaks the conditional formatting I've done above. If I switch code places the formatting does not work as the cell looks like this once clicked on it in google sheets:

'10%

note the ' in the front

Is there a way to achieve the conditional formatting while being able to add % sign to a cell?

Answer 1

In your script, how about adding value_input_option='USER_ENTERED' to the method of update_cells as follows? It seems that the default value of value_input_option is RAW. By this, ' is added.

From:

worksheet.update_cells(item) 

To:

worksheet.update_cells(item, value_input_option='USER_ENTERED')

Note:

• In this case, when your script is run, 145.1 becomes 145.1%. By this, this value is less than 100. Please be careful about this.

Reference:

• update_cells(cell_list, value_input_option='RAW')