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
You can find this in the JSON key you downloaded in
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.