In gspread, how I do delete all worksheets and start over?-CodePudding

For my use case, I have to generate some reports dynamically, so I thought it would be easier to just create the report from scratch. Every time the user requests the report, I would like to first clear the entire spreadsheet and then generate the worksheets from fresh database queries. I tried to loop through every worksheet and delete each one, but I couldn't delete the last one. Here is my code:

import gspread
gc = gspread.service_account()
sh = gc.open("My Google Sheet")
for s in sh.worksheets():
    sh.del_worksheet(s)

Here is the error I'm getting:

---------------------------------------------------------------------------
APIError                                  Traceback (most recent call last)
~\AppData\Local\Temp/ipykernel_17768/423772843.py in <module>
      1 sh = gc.open(report_name)
      2 for s in sh.worksheets():
----> 3     sh.del_worksheet(s)

~\anaconda3\envs\analytics\lib\site-packages\gspread\spreadsheet.py in del_worksheet(self, worksheet)
    436         body = {"requests": [{"deleteSheet": {"sheetId": worksheet.id}}]}
    437 
--> 438         return self.batch_update(body)
    439 
    440     def reorder_worksheets(self, worksheets_in_desired_order):

~\anaconda3\envs\analytics\lib\site-packages\gspread\spreadsheet.py in batch_update(self, body)
    128         .. versionadded:: 3.0
    129         """
--> 130         r = self.client.request(
    131             "post", SPREADSHEET_BATCH_UPDATE_URL % self.id, json=body
    132         )

~\anaconda3\envs\analytics\lib\site-packages\gspread\client.py in request(self, method, endpoint, params, data, json, files, headers)
     78             return response
     79         else:
---> 80             raise APIError(response)
     81 
     82     def list_spreadsheet_files(self, title=None, folder_id=None):

APIError: {'code': 400, 'message': "Invalid requests[0].deleteSheet: You can't remove all the sheets in a document.", 'status': 'INVALID_ARGUMENT'}

CodePudding user response：

You can't delete the last sheet, but one way to work around this is to add a new blank sheet, and then delete all the sheets you want to delete, build your report, and at the end delete the blank sheet.

CodePudding user response：

I think that the reason for your issue is due to that all sheets cannot be removed by the current specification at Google Spreadsheet. This has already been mentioned in the comments and the existing answer.

About your script, when I saw the script of gsperad, it seems that sh.del_worksheet(s) is run by using one quota of Sheets API. Ref By this, when sh.del_worksheet(s) is used in a loop, Sheets API is continuously used. When the number of sheets is large, I'm worried that an error might occur.

So, in this answer, I would like to propose a sample script for achieving your goal with 2 API calls.

Pattern 1:

In this pattern, 1st sheet is cleared and other sheets except for the 1st sheet are removed.

worksheets = sh.worksheets()
reqs = [{"repeatCell": {"range": {"sheetId": s.id}, "fields": "*"}} if i == 0 else {"deleteSheet": {"sheetId": s.id}} for i, s in enumerate(worksheets)]
sh.batch_update({"requests": reqs})

Pattern 2:

In this pattern, a new sheet is inserted and all other sheets are removed.

worksheets = sh.worksheets()
reqs = [{"addSheet": {"properties": {"index": 0}}}]   [{"deleteSheet": {"sheetId": s.id}} for s in worksheets]
sh.batch_update({"requests": reqs})

Note:

In these sample scripts, sh.worksheets() uses one API call. And, sh.batch_update({"requests": reqs}) uses one API call.

Reference:

batch_update(body)