Remove only formatting on a cell range selection with google spreadsheet API

Question

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.

I have no clue... but may be you could copy the format from a known, unformatted cell. — Salman Arshad
– Salman Arshad, Commented Aug 22, 2017 at 8:07

Thomas Mouton · Accepted Answer · 2020-05-19 03:13:44Z

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)

soundstripe · Accepted Answer · 2017-11-13 00:47:23Z

6

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"
         }
     }]
}

answered Nov 13, 2017 at 0:47

soundstripe

1,50411 silver badges22 bronze badges

2 Comments

soundstripe Over a year ago

I had an edit that seems to indicate that userEnteredValue should be userEnteredFormat instead ... makes sense but again I haven't tested it. If someone tests it and comments here I'll update my answer.

Andreas Over a year ago

I am using fields: 'userEnteredFormat' for this but with a repeatCell instead of an updateCells command

user3394637 · Accepted Answer · 2021-05-17 21:23:57Z

0

I know this is old and could be referring to v3, but I thought I would add what worked for me:

{
repeatCell: {
    range: {
        sheetId: sheetId,
        startRowIndex: 0,
        endRowIndex: <rows>,
        startColumnIndex: 0,
        endColumnIndex: <columns>
    },
    fields: "userEnteredFormat"
}

answered May 17, 2021 at 21:23

user3394637

1

Collectives™ on Stack Overflow

Remove only formatting on a cell range selection with google spreadsheet API

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related