clear validated cells using a batch update using pygsheets

December 31, 2022

I have a pygsheets work sheet with cells that have been filled in and would like to clear the cells after pulling the data into python.

My issue is that I have validated the cells with a particular data format then when I try to batch update the sheet the cells are either not going empty (if I use None for the batch update) or an error occurs when I use "".

Please can you help me update the cells to be empty. I have read the documentation and haven’t come across how this is done. I have seen a values_batch_clear method but it seems to be on the spreadsheet.

import pygsheets

spread_sheet_id = "...insert...spreadsheet...id"
spreadsheet_name = "...spreadsheet_name..."
wks_name_or_pos = "...worksheet_name..."

spreadsheet = pygsheets.Spreadsheet(client=service,id=spread_sheet_id)
wksheet = spreadsheet.worksheet('title',wks_name_or_pos)


# trying to batch update cells (that have a data validation format) to make them empty again
wksheet.update_values_batch('C2:F6', 
    [[None, None, None, ""], 
     [None, None, None, ""], 
     [None, None, None, ""], 
     [None, None, None, ""], 
     [None, None, None, ""]])

>Solution :

pygsheets.Worksheet.clear() should work:

import pygsheets

spread_sheet_id = "...insert...spreadsheet...id"
spreadsheet_name = "...spreadsheet_name..."
wks_name_or_pos = "...worksheet_name..."

spreadsheet = pygsheets.Spreadsheet(client=service,id=spread_sheet_id)
wksheet = spreadsheet.worksheet('title',wks_name_or_pos)

wksheet.clear(start='C2', end='F6')