Gspread not clearing correct tab

Question

I have a DataFrame "budget" that im trying to upload in a heavy spreadsheet with 22 tabs and more than 1 with RawData in some form in their name: "Raw Data >>", "RawData", "RawData_TargetCompletion"

I have the following code:

class GoogleSheets():
    def __init__(self):
        google_service_account_path = 'some_path'
        scopes = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
        self.credentials = ServiceAccountCredentials.from_json_keyfile_name(google_service_account_path, scopes)
        self.sheets_connection = gspread.authorize(self.credentials)

    def load_spreadsheet(self, spreadsheet_key):
        self.sheet = self.sheets_connection.open_by_key(spreadsheet_key)

    def load_worksheet(self, worksheet_name):
        self.worksheet = self.sheet.worksheet(worksheet_name)

    def clear_range(self, data_range):
        self.sheet.values_clear(data_range)


spreadsheet_key = "this is a spreadsheet key"
worksheet_name = "RawData"
cell_ref = 'A:AT'
google_sheets = sheets.GoogleSheets()
google_sheets.load_spreadsheet(spreadsheet_key)
google_sheets.load_worksheet(worksheet_name)
google_sheets.clear_range(cell_ref)
google_sheets.upload_dataframe(budget)

I have a problem that in that heavy spreadsheet, its clearing the first tab (not the RawData), and updating in the RawData sheet.

This exact same code, but with another spreadsheet_key works fine and clears and updates the correct RawData tab regardless of the position of that RawData tab. But in this heavy one, RawData has to be the first tab in the document because the clear part is not mapping correctly and clears the first tab always.

Is there a problem you see in the code I'm not seeing or have you encountered the same problem when updating heavy spreadsheets?

Answer 1

I believe your goal as situation as follows.

• You want to clear the range using gspread.
• You have already been able to use Sheets API.

Modification points:

• When I saw values_clear(range) in the document of gspread, it seems that it is the method of class gspread.models.Spreadsheet. Ref And, range of values_clear(range) is A1Notation.
  • In your script, self.sheet.values_clear('A:AT') is run. In this case, 1st tab is always used because the sheet name is not used. I thouthg that this is the reason of your issue.
• In order to remove your issue, I would like to propose to use the sheet name to the A1Notation for values_clear(range).

When above points are reflected to your script, it becomes as follows.

Modified script:

From:

google_sheets.clear_range(cell_ref)

To:

google_sheets.clear_range("'{0}'!{1}".format(worksheet_name, cell_ref))

References:

• values_clear(range)
• A1 notation