With an increasing number of users using google sheets to maintain data, it has become essential to access google sheets in your development environment.

You have a few options:

1. Use API from services like sheetsu, sheety, etc

2. Use Official Google sheets API documented here https://developers.google.com/sheets/api/quickstart/python

3. Use a python wrapper library like PyDrive2 (PyDrive is not maintained anymore)

In this blog, we will look at PyDrive2 and how you can use service account credentials to access google sheets.

PyDrive2 docs do not provide enough documentation on how to use service account credentials. I had to dig up its implementation to identify how to do that. To make it simpler for you, here’s the code to do that.

Step 1 — Service Account Configurations

1. Create a new service account https://cloud.google.com/iam/docs/creating-managing-service-accounts
   No need to give any permissions to the account.

2. Create and JSON Key https://cloud.google.com/iam/docs/creating-managing-service-account-keys

3. Create the sheet on google sheets and give Editor rights to the service account email which might look like xxxxxxx@xxxxxxx.iam.gserviceaccount.com
   You can find this in the JSON key you downloaded in client_email property

Step 2 — Code to access the sheet

credential_path = "credentials.json" # ensure the path is correct. 
sheet_url = "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" # replace with the sheet url
worksheet_name = "Sheet1" # Name of the worksheet you want to access

gc = gspread.service_account(pkg_resources.resource_filename(__name__, credential_path))
sh = gc.open_by_url(sheet_url)
worksheet = sh.worksheet(worksheet_name)
records = worksheet.get_all_records()
df = pd.DataFrame(records)

# Now you can use df to do your operations.

That’s all you need to do.