Conditionally format cells in a google sheet using pygsheets

Question

I am trying to learn how to conditionally format cells in a worksheet using pygsheets. The reason for this is to only allow the end user interacting with a google sheet to only be able to input values that are in the correct format.

For example, how can we format the sheet so that:

cells A2 to A5 to be numbers between 0 and 50.
cells B2 to B5 to be an email address (a string say having @gmail.com or @yahoo.com).
cells C2 to C5 to be some string corresponding to a date.

If the cell does not meet the correct format the colour can change to a slight red to signify that it is not the correct format.

Using the two documentation for pygsheets I was able to create the sheet and start with formatting for the headers. I was however unable to use a conditional statement to change the colour as need be, I think I am supposed to use the set_data_validation function to do this but I could not get it to work. The documentation referred me to google sheets API spreadsheets conditional page but I could not understand this documentation well enough to do set up these conditions.

I have added my example and solution for the first point which did not do what I thought it should.

Working example

number int	string	date

import pygsheets
import pandas as pd

# get credentials from the json file
service = pygsheets.authorize(service_file='key.json') 
spread_sheet_id = ".... insert id here ...." # id to get example worksheet

spreadsheet = pygsheets.Spreadsheet(client=service,id=spread_sheet_id)

# open work sheet
worksheet = spreadsheet.worksheet(0)

header_list = ["number int","string","date"]
for index, element in enumerate(header_list):
    # get the cell A1,B1 and C1 and set elements to header_list elements
    header_cell = wksheet.cell( str(chr(65+index)+"1"))
    header_cell.value = element
    
    # format colour and make bold
    header_cell.text_format['bold'] = True # make the header bold
    header_cell.color = (0.9529412, 0.9529412, 0.9529412, 0) # set background color of this cell as a tuple (red, green, blue, alpha)
    # update
    header_cell.update()

# format A2 to A5 to be an integer between 0 and 53 (DIDN'T WORK)
for index in ["A2","A3","A4","A5"]:
    model_cell = pygsheets.Cell(index)
    print(index)
    model_cell.set_number_format(format_type = pygsheets.FormatType.NUMBER, pattern = "^(?:[1-9]|[1-4][0-9]|5[0-3])$") # This pattern will match any integer between 0 and 53, inclusive
    header_cell.update()

# (TRY OTHER WAY as a number between 0 and 53)  (DIDN'T WORK)

cells = worksheet.range('B2:B5') # select the cells to be formatted
# create the data validation rule
# rule = pygsheets.data_validation.RangeRule(minimum=0, maximum=53)
# model_cell = pygsheets.Cell("A1")
# model_cell.set_number_format(format_type = pygsheets.FormatType.NUMBER,pattern = "0")
# worksheet.add_conditional_formatting('C2', 'C6', 'NUMBER_BETWEEN', {'backgroundColor':{'red':1}}, ['0','53'])

# ... format B2 to B5 to be a string less than 30 characters
# ... format C2 to C5 to be a string that is a relevent email address less than 50 characters

From the google sheets API documentation, I think that each rule can be set up as:

|# create pygsheets conditional formatting rule using 
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#conditiontype

rule = {
  "type": enum (NUMBER_BETWEEN),
  "values": [
    {
      object (ConditionValue)
    }
  ]
}

score 1 · Accepted Answer · answered Dec 27 '22 at 18:46

To accomplish this you'll need to combine data validation rules (shows a warning when the users do not comply with the rule or prevents entering a value) with conditional formatting rules (changes cell format depending on the rule).

You seem to be using data format functions, but according to the pygsheets documentation, you can use Worksheet.add_conditional_formatting() and Worksheet.set_data_validation() to apply both types of rules. Here's a sample that does what you're looking for if you add it to your code after setting the headers:

Between 0 and 53:

worksheet.add_conditional_formatting(start='A2',
                                    end='A5', 
                                    condition_type='NUMBER_NOT_BETWEEN', 
                                    format={'backgroundColor':{'red':0.9, 'green':0.6, 'blue':0.6, 'alpha':0}}, 
                                    condition_values=['0','53'])
worksheet.set_data_validation(start='A2', 
                              end='A5', 
                              condition_type='NUMBER_BETWEEN', 
                              condition_values=['0','53'], 
                              inputMessage="Value must be between 0 and 53", 
                              strict=False)

Is email:

worksheet.add_conditional_formatting(start='B2', 
                                     end='B5', 
                                     condition_type='CUSTOM_FORMULA', 
                                     format={'backgroundColor':{'red':0.9, 'green':0.6, 'blue':0.6, 'alpha':0}}, 
                                     condition_values=['=NOT(ISEMAIL(B2))'])
worksheet.add_conditional_formatting(start='B2', 
                                     end='B5', 
                                     condition_type='CUSTOM_FORMULA', 
                                     format={'backgroundColor':{'red':1, 'green':1, 'blue':1, 'alpha':0}}, 
                                     condition_values=['=ISBLANK(B2)'])
worksheet.set_data_validation(start='B2', 
                              end='B5', 
                              condition_type='TEXT_IS_EMAIL', 
                              inputMessage="Value must be an email", 
                              strict=False)

Is date:

worksheet.add_conditional_formatting(start='C2', 
                                     end='C5', 
                                     condition_type='CUSTOM_FORMULA', 
                                     format={'backgroundColor':{'red':0.9, 'green':0.6, 'blue':0.6, 'alpha':0}}, 
                                     condition_values=['=NOT(ISDATE(C2))'])
worksheet.add_conditional_formatting(start='C2', 
                                     end='C5', 
                                     condition_type='CUSTOM_FORMULA', 
                                     format={'backgroundColor':{'red':1, 'green':1, 'blue':1, 'alpha':0}}, 
                                     condition_values=['=ISBLANK(C2)'])
worksheet.set_data_validation(start='C2', 
                              end='C5', 
                              condition_type='DATE_IS_VALID', 
                              inputMessage="Value must be a date", 
                              strict=False)

Based on the documentation I linked above, you define the cell range with the start and end parameters. The condition_type is a string that you can get from the Google documentation. You can see the valid types there and what they do. The conditional formatting rule also takes a CellFormat object that holds the cell formatting. The condition_values parameter defines the value or values, which depend on the condition type. You'll notice that NUMBER_BETWEEN requires two values, CUSTOM_FORMULA takes one value and DATE_IS_VALID takes no values. The documentation is your friend to figure these out.

You'll notice that I also used custom formulas for some of these. This is because some of the conditions are not supported by conditional formatting so I had to use a formula as a workaround.

Here's what the sheet looks like after applying the rules:

I suggest you first get acquainted with the pygsheets documentation to know the required parameters for each function. The Google docs can help to figure out what values these parameters can take.

References:

Hi Daniel, thank you for your answer, this is great. I have a few questions, with your current implementation the number turns green if it is small but white if it is larger but not small. Can I change it so that it is green whenever it it in the acceptable range, red otherwise and white if empty? — user4933, Dec 28 '22 at 07:50
Yes, you can add multiple conditional formatting rules to the same range. I only added a rule to turn the background red when the number is outside the 0-53 range, but you can also add another rule with `condition_type='NUMBER_BETWEEN'` for the same cells and a `format` with green RGB values to turn it green when the number is within the 0-53 range. There's also a sample with an `ISBLANK()` custom formula to check when the cell is empty. It's up to your imagination how many rules you can combine to achieve the results you want. — Daniel, Dec 28 '22 at 14:21

Conditionally format cells in a google sheet using pygsheets

1 Answers1

References: