Search code examples
python-3.xgoogle-sheetsgoogle-sheets-apigspread

gspread clear specific cell value

I want to clear the value of a specific cell. Currently my script runs roughly as follows:

cell_list = worksheet.range(1, 1, len(rows), column_length)
for cell in cell_list:
    cell.value = rows[cell.row - 1][cell.col - 1]

worksheet.update_cells(cell_list)

I want just overwrite my old Data to save some API calls, therefore I don't use a worksheet.clear() before. Some of the new values could be None, these cells should be cleared then.

My Problem is: If I have e.g in cell B2 the value "a" and I set now to B2 to the value "None" (cell.value = None) and I update the Worksheet "a" will stay in B2. So it didn't update the cell, the old value stay.

I could set B2 to an empty string, but the problem with that is, that that is not an empty cell, therefore If I want to sort after it (in the Browser) it will list it at the top because it is an empty string and not empty. If I then clear the cell manually in the browser and sort it again, it sorts it how I want to.

enter image description here

Updated description:

I have a cell list that I want to update with new values in my table. It can happen that a cell contains a value before, but the updated cell should not contain a value anymore.

So the old value of A1 is e.g. "5" and the new value for A1 should be nothing. The problem is, if I assign the value None to the cell in the cell list, the cell will keep the old value after the update. So the value is still "5".

If I pass an empty string into the cell for updating, the new cell is empty, as desired. But now there is the problem that sorting doesn't work properly for me, so this is not an option for me. I could also clear the whole table before, but I try to avoid this to save API calls.

So I'm looking for a way to tell a cell that it does not contain a value

Solution

  • I think that the reason of your issue is worksheet.update_cells(cell_list). In gspread, it seems that the default value of ValueInputOption is RAW. I think that by this, such error occurs. So in order to avoid this, please modify as follows.

    From:

    worksheet.update_cells(cell_list)

    To:

    worksheet.update_cells(cell_list, value_input_option='USER_ENTERED')
    • By this modification, I think that "" which is the empty string can be used for clearing the cells.

    References: