How to use PyDrive2 to access google sheets inside a data frame in python

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:

Use API from services like sheetsu, sheety, etc
Use Official Google sheets API documented here https://developers.google.com/sheets/api/quickstart/python
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

Create a new service account https://cloud.google.com/iam/docs/creating-managing-service-accounts
No need to give any permissions to the account.
Create and JSON Key https://cloud.google.com/iam/docs/creating-managing-service-account-keys
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.

How to use PyDrive2 to access google sheets inside a data frame in python

Comments

More from this blog

How to get, upload and delete an object from s3 using aws-sdk for nodejs

How to use MongoDB profiler

How to use PyDrive2 to upload a file to google drive folder

How to write good comments in the code?

Step 1 — Service Account Configurations

Step 2 — Code to access the sheet

Command Palette

Comments

More from this blog

Step 1 — Service Account Configurations

Step 2 — Code to access the sheet