def set_worksheet_values(worksheet: Worksheet, values: List[list]):
_log.debug("clearing worksheet: %s ...", worksheet)
worksheet.clear()
cells = convert_values_to_cells(values)
_log.debug("updating worksheet values...")
worksheet.update_cells(cells)
_log.debug("worksheet values has been set!")
def __remove_data_and_formatting(self, wb: gspread.Spreadsheet,
ws: gspread.Worksheet) -> tuple:
"""
This *private* method removes all data and formatting from the sheet.
Without this, when new rows and columns are added, the format from the existing cells is used.
:param wb: the Google Spreadsheet file
:param ws: the one Google Worksheet in the file
:return: None
"""
# See the Google Sheets API and gspread documentation for help
sheet_id = ws._properties['sheetId']
if sheet_id:
# Clear all formatting on the sheet
d0 = {
'updateCells': {
'range': {
'sheetId': sheet_id
},
'fields': 'userEnteredFormat'
}
}
body = {'requests': [d0]}
if ws.col_count > 0:
# Reset the column width to 100 pixels (default) so that new columns are added with this default size.
d1 = {
'updateDimensionProperties': {
'range': {
'sheetId': sheet_id,
'dimension': 'COLUMNS',
'startIndex': 0,
'endIndex': ws.col_count
},
'properties': {
'pixelSize': 100
},
'fields': 'pixelSize'
}
}
body.get('requests', []).append(d1)
if ws.row_count > 0:
# Reset the row height to 21 pixels (default) so that new rows are added with this default size.
d2 = {
'updateDimensionProperties': {
'range': {
'sheetId': sheet_id,
'dimension': 'ROWS',
'startIndex': 0,
'endIndex': ws.row_count
},
'properties': {
'pixelSize': 21
},
'fields': 'pixelSize'
}
}
body.get('requests', []).append(d2)
d3 = {
'updateSheetProperties': {
'properties': {
'sheetId': sheet_id,
'gridProperties': {
'frozenRowCount': 0,
'frozenColumnCount': 0
}
},
'fields':
'gridProperties(frozenRowCount, frozenColumnCount)'
}
}
body.get('requests', []).append(d3)
try:
# Clear all data on the sheet
# ws.clear() cannot be completed with a ws.batch_update() call, so it done separate
response = ws.clear()
if len(body.get('requests', [])) > 0:
wb.batch_update(body)
return True, ''
except Exception as e:
return False, 'There was an error removing the previous data and formatting from the Google Sheet.'