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?
CodePudding user response:
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
becomes145.1%
. By this, this value is less than100
. Please be careful about this.