How to set formatting with Google sheets API for python?

Question

In theory this was answered in Remove only formatting on a cell range selection with google spreadsheet API but for the life of me I cannot get it to work.

I want to write a function that clears the formatting of a given Google Sheet. Here is what I think is the right way to do it based on the above:

def clear_sheet_formatting():
    spreadsheetID = "1pQoBG0q6f0Ni6yMiC9BSIXVLGMmvBRc07pGXqMA2VtA"

    global service_spreadsheets
    open_connection()

    body = {
        "requests": [
            {
                "updateCells": {
                    "range": {
                        "sheetId": "Sheet1",
                    },
                    "fields": "userEnteredFormat"
                }
            }
        ]
    }

    func = lambda: service_spreadsheets.batchUpdate(spreadsheetId=spreadsheetID,
                                                    body=body).execute()
    result = execute_query(func)

The sheet itself is very boring:

But I am getting the following error:

WARNING:root:Google API call failed with the following error, sleeping 20 seconds: Invalid value at 'requests[0].update_cells.range.sheet_id' (TYPE_INT32), "Sheet1"

What am I doing wrong? How do I make this work?

Giselle Valladares · Accepted Answer · 2022-12-26 13:17:26Z

1

I perform a test regarding this, use this sample code to see if that helps.

I'm currently using this scopes:

SCOPES = ['https://www.googleapis.com/auth/drive.file', 
            'https://www.googleapis.com/auth/drive',
            'https://www.googleapis.com/auth/spreadsheets' ]

And the sample code I'm using is:

    try:
        service = build('sheets', 'v4', credentials=creds)

        # Call the Sheets API and the sheet ID for "Sheet1"
        # the ID is not the name of the sheet but a number.
        call_sheet_with_format = service.spreadsheets().get(spreadsheetId=SPREADSHEET_ID).execute()
        sheets_with_format = call_sheet_with_format.get('sheets', '')
        sheet_id = sheets_with_format[0].get("properties", {}).get("sheetId", 0)

        request_body = {
            'requests': [
                {
                    'updateCells': {
                        'range': {
                            'sheetId': sheet_id
                        },
                        'fields':'userEnteredFormat'
                    }
                }
            ]
        }

        request = service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID, body=request_body)
        response = request.execute()



    except HttpError as err:
        print(err)

Or you can get the sheetId directly in the Google Sheet:

And remove this part of the

        sheets_with_format = call_sheet_with_format.get('sheets', '')
        sheet_id = sheets_with_format[0].get("properties", {}).get("sheetId", 0)

edited Dec 26, 2022 at 13:17

answered Dec 23, 2022 at 23:57

Giselle Valladares

2,2791 gold badge7 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

YGA Over a year ago

Excellent! I have no clue what I was doing wrong but this fixed it.

Collectives™ on Stack Overflow

How to set formatting with Google sheets API for python?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related