I am using pygsheets and have a column that I'd like to conditionally format so that its either a string ending in "@gmail.com" or a string ending in "@yahoo.com".

It seems to work for one but I am not sure how to extend this to 2 conditions.

I went through these resources and could not figure out how this would be done.

• ConditionType
• pygsheets
• worksheet.add_conditional_formatting

spread_sheet_id = "...insert...spreadsheet...id"
spreadsheet_name = "...spreadsheet_name..."
wks_name_or_pos = "...worksheet_name..."

spreadsheet = pygsheets.Spreadsheet(client=service,id=spread_sheet_id)
wksheet = spreadsheet.worksheet('title',wks_name_or_pos)

wksheet.cell('A1').set_text_format('bold', True).value = 'email (double conditional)'
wksheet.add_conditional_formatting(start='A2', end='A6', 
                                     condition_type='TEXT_ENDS_WITH', 
                                     format={'backgroundColor':{'red':0.6, 'green':0.9, 'blue':0.6, 'alpha':0}}, 
                                     condition_values=['@yahoo.com','@gmail.com'])

CodePudding user response：

I thought that at TEXT_ENDS_WITH, only one value can be used. In your situation, how about using a custom formula? When this is reflected in your script, how about the following modification?

From:

wksheet.add_conditional_formatting(start='A2', end='A6', 
                                     condition_type='TEXT_ENDS_WITH', 
                                     format={'backgroundColor':{'red':0.6, 'green':0.9, 'blue':0.6, 'alpha':0}}, 
                                     condition_values=['@yahoo.com','@gmail.com'])

To:

wksheet.add_conditional_formatting(start='A2', end='A6', 
                                     condition_type='CUSTOM_FORMULA', 
                                     format={'backgroundColor':{'red':0.6, 'green':0.9, 'blue':0.6, 'alpha':0}}, 
                                     condition_values=['=REGEXMATCH(A2,"^.*@gmail.com$|^.*@yahoo.com$")'])

Reference:

• ConditionType