How do I clear the formatting in gspread library?

worsksheet.clear() is not clearing the formatting on the cells. It is only clearing the values. Is there a way to clear the formatting on either all cells or a specified range?

I have tried cellformat bold none and passing it to the format func of gspread but its not working. Here is my code that I have tried:

if len(list_of_lists) != 0:
    num_rows = len(list_of_lists)
    num_cols = len(list_of_lists[0]) if num_rows > 0 else 0
    start_cell = 'A1'
    end_cell = gspread.utils.rowcol_to_a1(num_rows, num_cols)
    print("start cell",start_cell,"end cell:", end_cell)
    data_range = f"{start_cell}:{end_cell}"
    print("data range",data_range)
    clear_format = cellFormat(
    backgroundColor=None,  # Clear background color
    textFormat=textFormat(bold=False, foregroundColor=None),  # Reset text format
    horizontalAlignment=None  # Reset horizontal alignment
    )
    format_cell_range(worksheet, data_range, clear_format) 
    worksheet.clear()
    print("Formatting cleared")
    cleared = True

Solution

Although I'm not sure whether I could correctly understand your expected result, how about the following modification?

Modified script:

From

if len(list_of_lists) != 0:
    num_rows = len(list_of_lists)
    num_cols = len(list_of_lists[0]) if num_rows > 0 else 0
    start_cell = 'A1'
    end_cell = gspread.utils.rowcol_to_a1(num_rows, num_cols)
    print("start cell",start_cell,"end cell:", end_cell)
    data_range = f"{start_cell}:{end_cell}"
    print("data range",data_range)
    clear_format = cellFormat(
    backgroundColor=None,  # Clear background color
    textFormat=textFormat(bold=False, foregroundColor=None),  # Reset text format
    horizontalAlignment=None  # Reset horizontal alignment
    )
    format_cell_range(worksheet, data_range, clear_format) 
    worksheet.clear()
    print("Formatting cleared")
    cleared = True

To:

if len(list_of_lists) != 0:
    num_rows = len(list_of_lists)
    num_cols = len(list_of_lists[0]) if num_rows > 0 else 0
    start_cell = 'A1'
    end_cell = gspread.utils.rowcol_to_a1(num_rows, num_cols)
    print("start cell",start_cell,"end cell:", end_cell)
    data_range = f"{start_cell}:{end_cell}"
    print("data range",data_range)

    requests = [
        {
            "repeatCell": {
                "range": {"sheetId": worksheet.id, "startRowIndex": 0, "endRowIndex": num_rows, "startColumnIndex": 0, "endColumnIndex": num_cols},
                "fields": "*"
            }
        }
    ]
    worksheet.spreadsheet.batch_update({"requests": requests})

    worksheet.clear()
    print("Formatting cleared")
    cleared = True

When this modified script is run, the cell values and formats of data_range are cleared.
I couldn't see your whole script. So, I used worksheet.spreadsheet as the class Spreadsheet object. If you have already declared it as spreadsheet, you can also use it.

Note:

If you want to clear all cells in a sheet, please modify requests in the above modified script as follows.

requests = [
    {
        "repeatCell": {
            "range": {"sheetId": worksheet.id},
            "fields": "*"
        }
    }
]

How do I clear the formatting in gspread library?

Modified script:

From

To:

Note:

References: