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()
I believe your goal is as follows.
In this case, how about using batch_update of Sheet API Wrapper as follows?
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.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?
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())
Sample. And, the created request body is used with service.sheet.batch_update of pygsheets.