How to clear a range in google sheet via gspread

Question

Hi I would like to clear a range of A3:J10000 in google sheet by using gspread.

Doing a loop like this takes too much time:

for cell in range_to_clear:
cell.value=''
sh.worksheet('WorksheetX').update_cells(range_to_clear,value_input_option='USER_ENTERED')

I found the values_clear() method but wasn't able to make it working:

range_2_delete = sh.worksheet("WorksheetX").range("A3:J10000")
sh.values_clear(range_2_delete)

The above code giving this error: AttributeError: 'list' object has no attribute 'encode'

score 7 · Accepted Answer · answered Nov 15 '19 at 12:35

You want to clear the values of range on Google Spreadsheet.
You want to achieve this using gspread with python.
You have already been able to put and get values for Spreadsheet using Sheets API.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

I think that the method of values_clear() can be used for your situation.

Please modify your script as follows.

From:

range_2_delete = sh.worksheet("WorksheetX").range("A3:J10000")
sh.values_clear(range_2_delete)

To:

sh.values_clear("WorksheetX!A3:J10000")

or

sh.values_clear("'WorksheetX'!A3:J10000")

This answer supposes as follows.
- The latest version (v3.1.0) of gspread is used.
- sh is declared. If sh is not declared, please use sh = gc.open('My poor gym results') and sh = client.open_by_key(spreadsheetId). Ref

If this was not the result you want, I apologize.

Thanks, that worked but what was the problem with my code? I defined a range called range_2_delete and put that range into values_clear(), shouldn't it be correct? — zeppelin11, Nov 15 '19 at 13:53
@zeppelin11 Thank you for replying. I'm glad your issue was resolved. At the method of `values_clear(range)` of gspread, `range` is required to be the string value of a1Notation. [Ref](https://gspread.readthedocs.io/en/latest/api.html#gspread.models.Spreadsheet.values_clear) `sh.worksheet("WorksheetX").range("A3:J10000")` returns the list type which is not the string. By this, an error occurs. If I misunderstood your question, I apologize. — Tanaike, Nov 15 '19 at 23:43

score 0 · Answer 2 · answered Nov 15 '19 at 12:35

0

You should try re-writing the code as follows:

    range_2_delete = sh.worksheet("WorksheetX").range("A3:J10000")
    values_clear(range_2_delete)

answered Nov 15 '19 at 12:35

Samiie

1

sorry this one gives **NameError: name 'values_clear' is not defined** error – zeppelin11 Nov 15 '19 at 13:55

2 Answers2