2

I have the google sheet in which sheet has all data but how to reset the google sheet before adding the data in same google sheets any help would be appreciated

Google Sheet Image Link Given Here :https://i.stack.imgur.com/6sbvr.png

1 Answers1

3
  • You want to reset the sheet using gspread.
  • You have already been able to get and put values for Google Spreadsheet using gspread.

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

Pattern 1:

If reset in your question is to clear all values of cells on the sheet in the Spreadsheet, how about the following sample script? In this pattern, all values in the cells are cleared.

Sample script:

spreadsheetId = "###"  # Please set the Spreadsheet ID.
sheetName = "Sheet1"  # Please set the sheet name.

spreadsheet = client.open_by_key(spreadsheetId)
worksheet = spreadsheet.worksheet(sheetName)
worksheet.clear()

Pattern 2:

If reset in your question is to clear all values and format of cells on the sheet in the Spreadsheet, how about the following sample script? In this pattern, all values and cell formats in the cells are cleared.

Sample script:

spreadsheetId = "###"  # Please set the Spreadsheet ID.
sheetName = "Sheet1"  # Please set the sheet name.

spreadsheet = client.open_by_key(spreadsheetId)
worksheet = spreadsheet.worksheet(sheetName)

requests = {"requests": [{"updateCells": {"range": {"sheetId": worksheet._properties['sheetId']}, "fields": "*"}}]}
res = spreadsheet.batch_update(requests)
print(res)

References:

If I misunderstood your question and this was not the result you want, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • well your code works well but it clears all data including columns names in sheet but i only want to reset data of all rows not columns name in sheets how to to it? –  Feb 03 '20 at 11:14
  • @umair mubeen Thank you for replying. About your additional question, when the header row is the 1st row, please add `"startRowIndex": 1` to the range property. By this, all rows except for the 1st row are removed. [Ref](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#updatecellsrequest) – Tanaike Feb 03 '20 at 22:35
  • 1
    Yes i did the same things and it clears only rows data not columns thanks for the help –  Feb 04 '20 at 08:37
  • @umair mubeen Thank you for replying. I'm glad your issue was resolved. – Tanaike Feb 04 '20 at 22:11
  • I am having a problem while on python 3.8 with the exact code, I am receiving the following error `File "/root/.local/lib/python3.8/site-packages/gspread/models.py", line 1171, in` `batch_update` `data = [` `File "/root/.local/lib/python3.8/site-packages/gspread/models.py", line 1172, in` `` `dict(vr, range=absolute_range_name(self.title, vr['range']))` `TypeError: string indices must be integers ` – E_K Jul 01 '20 at 14:22