Check if workbook exists using gspread library?

Question

How to check if workbook exists using gspread library? My problem is that if I run gspread.create(title) second time it does not rewrite the previous file but creates one more with the same name. Any aidea how to avoid it?

Thanks. Petro.

Answer 1

You can check it with try/except + gc.open():

from gspread.exceptions import SpreadsheetNotFound

spreadsheetName = '' # Enter your spreadsheet name
gc = gspread.authorize(credentials)

try:
    gc.open(spreadsheetName)
    # Code if spreadsheet exists:
    print("Spreadsheet exists")
except SpreadsheetNotFound:
    # Code if spreadsheet doesn't exist:
    print("Spreadsheet doesn't exist")

If spreadsheet exists, then gc.open() returns value and try block is run.

If spreadsheet doesn't exist, then gc.open() raises error "SpreadsheetNotFound" and except block is run.

Answer 2

I believe your goal as follows.

• You want to check whether the Spreadsheet is existing using the Spreadsheet name.
• When the Spreadsheet is not existing, you want to create new Spreadsheet, and retrieve the created Spreadsheet.
• When the Spreadsheet is existing, you want to retrieve the existing Spreadsheet.
• You want to use gspread.

In this case, in order to checking whether the Spreadsheet is existing using the Spreadsheet name, it is required to use Drive API. The sample script is as follows.

Sample script:

Please copy and paste the following script. This script doesn't include the authorization script. So, please add your script for retrieving client = gspread.authorize(credentials).

spreadsheetName = "sample Spreadsheet name" # Please set the Spreadsheet name you want to check.

client = gspread.authorize(credentials)
url = "https://www.googleapis.com/drive/v3/files?q=mimeType%3D%27application%2Fvnd.google-apps.spreadsheet%27%20and%20name%3D%27" + spreadsheetName + "%27%20"
res = requests.get(url, headers={"Authorization": "Bearer " + credentials.access_token})
files = res.json().get("files")
spreadsheet = client.open_by_key(files[0]['id']) if files else client.create(spreadsheetName)

# Sample script for testing "spreadsheet".
sheetNameOf1stTab = spreadsheet.sheet1.title
print(sheetNameOf1stTab)

• In this case. please add import requests. The method of "Files: list in The Drive API is used with request library.
• When above script is run, at first, the Spreadsheet name of spreadsheetName is searched using the method of "Files: list in The Drive API v3. And, when the Spreadsheet is existing, the existing Spreadsheet is retrieved as the Spreadsheet object for gspread. When the Spreadsheet is NOT existing, new Spreadsheet is created and the created Spreadsheet object for gspread is retrieved.
• If you want to search the Spreadsheet from the specific folder, please use url = "https://www.googleapis.com/drive/v3/files?q=mimeType%3D%27application%2Fvnd.google-apps.spreadsheet%27%20and%20name%3D%27" + spreadsheetName + "%27%20and%20%27" + folderId + "%27%20in%20parents".
  • In this case, please set folderId.

Note:

• If an error related to the scope occurs, please include https://www.googleapis.com/auth/drive or https://www.googleapis.com/auth/drive.readonly.

Reference:

• Files: list

Updated on March 17, 2023:

In the current stage, openall method can be used for checking whether the spreadsheet is existing using the Spreadsheet title. openall returns the spreadsheet object in a list. In this case, when no spreadsheet object is found, the empty list is returned. I thought that this can be used for checking the existing spreadsheet with the title. The sample script is as follows.

Sample script:

import gspread

client = gspread.oauth(###) # Please use your client.

spreadsheetName = "sample Spreadsheet name" # Please set the Spreadsheet name you want to check.
spreadsheet_list = client.openall(spreadsheetName)
spreadsheet = client.create(spreadsheetName) if spreadsheet_list == [] else spreadsheet_list[0]

print(spreadsheet.id) # Here, the Spreadsheet ID of the retrieved Spreadsheet is shown.

• When this script is run, when the spreadsheet of spreadsheetName is existing, the existing spreadsheet is retrieved. When the spreadsheet of spreadsheetName is not exist, a new spreadsheet is created by the title of spreadsheetName.

Reference:

• openall