Menu
I am looking for a way to remove only formatting on a cell range selection, not their content, using Google sheet API with python.
For now, the only solution I have is to apply the same logic as a normal format and setting the style to NONE. For example, when I set a border format to a specifical range, I use:
request_dict = {'requests': [{
"updateBorders": {
"range": {
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": raws,
"startColumnIndex": first_col,
"endColumnIndex": last_col},
"top": {
"style": "SOLID_MEDIUM",
"width": 1,
"color": {"blue": 0}},
"bottom": {
"style": "SOLID_MEDIUM",
"width": 1,
"color": {"blue": 0}},
"left": {
"style": "SOLID_MEDIUM",
"width": 1,
"color": {"blue": 0}},
"right": {
"style": "SOLID_MEDIUM",
"width": 1,
"color": {"blue": 0}},
"innerHorizontal": {
"style": "SOLID_MEDIUM",
"width": 1,
"color": {"blue": 0}},
"innerVertical": {
"style": "SOLID_MEDIUM",
"width": 1,
"color": {"blue": 0}}}}]}
body = {'requests': request_dict['requests']}
service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId,
body=body).execute()
And if I want to delete it, I replace the "style" field with 'NONE' just like this:
request_dict = {'requests': [{
"updateBorders": {
"range": {
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": raws,
"startColumnIndex": first_col,
"endColumnIndex": last_col},
"top": {
"style": "NONE",
"width": 1,
"color": {"blue": 0}},
"bottom": {
"style": "NONE",
"width": 1,
"color": {"blue": 0}},
"left": {
"style": "NONE",
"width": 1,
"color": {"blue": 0}},
"right": {
"style": "NONE",
"width": 1,
"color": {"blue": 0}},
"innerHorizontal": {
"style": "NONE",
"width": 1,
"color": {"blue": 0}},
"innerVertical": {
"style": "NONE",
"width": 1,
"color": {"blue": 0}}}}]}
body = {'requests': request_dict['requests']}
service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId,
body=body).execute()
But it means I need to define a function to erase format for each kind of format I define, which is not very practical... The first step would be to find a way to erase formatting on a whole sheet, and maybe after to be able to do it for a specifical range in my sheet.
391
Removing existing formatting from cells in a Google Sheet can be done very quickly! It's a two-step process. First select the cell or cells you want to clear from all formatting, then click on the Format tab at the top and select the Clear formatting option.
To do this, first, select the range of cells where you have conditional formatting applied. You will see all the rules that you created in the sidebar. Point your mouse to the condition that needs to be deleted and click the "Remove" icon. Conditional formatting will be cleared.
To clear a range's contents, first reference the range and then use the clearContent() method. The range's contents have been cleared but its formatting has been preserved.
This documentation seems to state that if you set fields to "userEnteredValue" in an updateCells request that it will remove all formatting. I haven't tested this yet but here you go:
request_dict = {
"requests": [{
"updateCells": {
"range": {
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": raws,
"startColumnIndex": first_col,
"endColumnIndex": last_col
},
"fields": "userEnteredValue"
}
}]
}
Had the same question and figured it out. The other answer is close but for anyone else who stumbles across this. In the api samples they indicate you can use updateCells to clear formatting which takes a gridRange argument. gridRange documentation says
Missing indexes indicate the range is unbounded on that side.
Therefor leave off all indexes to affect the entire worksheet.
To clear an entire worksheet:
body = {
"requests": [
{
"updateCells": {
"range": {
"sheetId": sheetId
},
"fields": "userEnteredFormat"
}
}
]
}
spreadsheet.batch_update(body)
To clear a range:
body = {
"requests": [
{
"updateCells": {
"range": {
"sheetId": sheetId,
"startRowIndex": 1,
"endRowIndex": raws,
"startColumnIndex": first_col,
"endColumnIndex": last_col
},
"fields": "userEnteredFormat"
}
}
]
}
spreadsheet.batch_update(body)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
