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 A, Aug 22 '17 at 08:07

score 8 · Answer 1 · answered May 19 '20 at 03:13

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)

score 5 · Answer 2 · answered Nov 13 '17 at 00:47

5

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 '17 at 00:47

soundstripe

1,454
11
19

1

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. – soundstripe Aug 27 '18 at 14:48
I am using `fields: 'userEnteredFormat'` for this but with a `repeatCell` instead of an `updateCells` command – Andreas Apr 20 '20 at 18:32

score 0 · Answer 3 · answered May 17 '21 at 21:23

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

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

3 Answers3

Linked