Batch update validation and formatting cells using pygsheets

Question

I am using pygsheets and would like to batch validate cells instead of looping through each cell and doing it iteratively. I have gone through the pygsheets documentation and have not found an example of this, would this be possible and if so how would one do this? I did see an example of batching in the documentation (through unlinking and then linking again), but this did not work for me instead no update happened.

Below I have a working example of the code that I am trying to optimise by batching the update.

A	B	C

import pygsheets

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)

header_list = ["A","B","C"]

     for index, element in enumerate(header_list):
            cell_string = str(chr(65+index)+"1")
            wksheet.cell(cell_string).set_text_format('bold', True).value = element
            header_cell = wksheet.cell(cell_string)
            header_cell.color = (0.9529412, 0.9529412, 0.9529412, 0) # set background color of this cell as a tuple (red, green, blue, alpha)
            header_cell.update()
            wksheet.set_data_validation(
                start=cell_string,end=cell_string, 
                condition_type='TEXT_CONTAINS',
                condition_values=[element], inputMessage=f"Value must be {element}", strict=True)

I have realised I can change the value in the cell by passing it in as a list of lists, but not sure how to batch the validation and batch format the cell.

header_list = ["A","B","C"]

list_of_lists = [[col] for col in header_list]

# update values with list of lists (working)
wksheet.update_cells('A1:C1',list_of_lists)

# batch update to bold, change the colour to grey and make sure values fit in cell (increase cell size) ?
# wksheet.add_conditional_formatting(start='A1', end='C1', 
#                                           condition_type='CUSTOM_FORMULA', 
#                                           format={'backgroundColor':{'red':0.5,'green':0.5, 'blue':0.5, 'alpha':0}}, 
#                                            condition_values=['=NOT(ISBLANK(A1))'])

# batch validate multiple cells so that the value is strictly the value provided ?

I also tried just unlinking, running the pygsheets commands then linking again as

wksheet.unlink()
header_list = ["A","B","C"]

     for index, element in enumerate(header_list):
            cell_string = str(chr(65+index)+"1")
            wksheet.cell(cell_string).set_text_format('bold', True).value = element
            header_cell = wksheet.cell(cell_string)
            header_cell.color = (0.9529412, 0.9529412, 0.9529412, 0) # set background color of this cell as a tuple (red, green, blue, alpha)
            header_cell.update()
            wksheet.set_data_validation(
                start=cell_string,end=cell_string, 
                condition_type='TEXT_CONTAINS',condition_values=[element], inputMessage=f"Value must be {element}", strict=True)

wksheet.link()

Answer 1

I believe your goal is as follows.

• Your showing 1st script works fine.
• You want to reduce the process cost of your script and want to achieve your multiple requests by one API call.
• You want to achieve this using pygsheets for python.

In this case, how about using batch_update of Sheet API Wrapper as follows?

Modified script:

header_list = ["A", "B", "C"] # This is from your script.

# I modified the below script.
values = [
    {
        "userEnteredValue": {"stringValue": e},
        "userEnteredFormat": {"textFormat": {"bold": True}},
        "dataValidation": {
            "condition": {"type": "TEXT_CONTAINS", "values": [{"userEnteredValue": e}]},
            "inputMessage": "Value must be " + e,
            "strict": True,
        },
    }
    for e in header_list
]
requests = [
    {
        "updateCells": {
            "range": {
                "sheetId": wksheet.id,
                "startRowIndex": 0,
                "startColumnIndex": 0,
                "endRowIndex": 1,
                "endColumnIndex": 3,
            },
            "rows": [{"values": values}],
            "fields": "userEnteredValue,userEnteredFormat,dataValidation",
        }
    }
]
service.sheet.batch_update(spread_sheet_id, requests)

• service is your client for pygsheets.
• When this script is run, the same result as your 1st script is obtained by one API call.

References:

• Sheet API Wrapper
• UpdateCellsRequest

Added:

From your following reply,

I was looking for a solution with the bolding of the cells in the first row, and grey coloring.

I was also hoping to be able to pass the formatting in individual methods without writing dictionaries with strings (if possible, I understand this may be the only way).

How about the following sample script?

Sample script:

class Sample:
    startRange = {}
    values = []
    userEnteredFormat = {"textFormat": {}, "backgroundColor": {}}
    dataValidation = {}

    def setStartCell(self, sheetId, row, col):
        self.startRange = {"sheetId": sheetId, "rowIndex": row, "columnIndex": col}

    def setValues(self, v):
        self.values = v

    def setTextFormat(self, v1, v2):
        self.userEnteredFormat["textFormat"][v1] = v2

    def setBackgroundColor(self, v1):
        self.userEnteredFormat["backgroundColor"] = {
            "red": v1[0],
            "green": v1[1],
            "blue": v1[2],
            "alpha": v1[3],
        }

    def setDataValidation(self, v1, v2):
        self.dataValidation = [v1, v2]

    def create(self):
        values = [
            {
                "userEnteredValue": {"stringValue": e},
                "userEnteredFormat": self.userEnteredFormat,
                "dataValidation": {
                    "condition": {
                        "type": self.dataValidation[0],
                        "values": [{"userEnteredValue": e}],
                    },
                    "inputMessage": self.dataValidation[1].replace("{element}", e),
                    "strict": True,
                },
            }
            for e in self.values
        ]
        return [
            {
                "updateCells": {
                    "start": self.startRange,
                    "rows": [{"values": values}],
                    "fields": "userEnteredValue,userEnteredFormat,dataValidation",
                }
            }
        ]

spread_sheet_id = "...insert...spreadsheet...id"
wks_name_or_pos = "...worksheet_name..."
spreadsheet = pygsheets.Spreadsheet(client=service, id=spread_sheet_id)
wksheet = spreadsheet.worksheet("title", wks_name_or_pos)
header_list = ["A", "B", "C"] # This is from your question.

s = Sample()
s.setStartCell(wksheet.id, 0, 0)  # cell "A1" (0, 0) of wksheet.
s.setValues(header_list)
s.setTextFormat("bold", True)
s.setBackgroundColor([0.9529412, 0.9529412, 0.9529412, 0]) # R, G, B, Alpha
s.setDataValidation("TEXT_CONTAINS", "Value must be {element}") # type, inputMessage
service.sheet.batch_update(spread_sheet_id, s.create())

• In this sample script, a request body for the batchUpdate method is created by Sample. And, the created request body is used with service.sheet.batch_update of pygsheets.