Google Sheets

This article covers all the necessary steps to access data that lives in a Google Sheet spreadsheet, analyze it, and write it back to a spreadsheet.
Setup
Before you can run Python code to programmatically access data in Google Sheets, you need to:
Enable the Google Sheets API
Create a Google service account for programmatic access.
Ensure you have a Google spreadsheet with data that the service account can access.
Store the service account credentials in Workspace
In what follows, all these steps are laid out in detail.
Enable the Google Sheets API
Make sure you’re signed in with your Google account.
Navigate to the Google API Library​
Create a new project (if you haven't created one before) by clicking in the dropdown on the navbar.
Search for the “Google Sheets API” and enable it. This can take up to 10 seconds.
Create a new Google Cloud project and enable the Google Sheets API
Configure a Google Service Account
A Google service account is a special kind of account that can be used by programs to access Google resources like a spreadsheet. You will use this service account to connect DataCamp Workspace to Google Sheets.
You only have to set up this Google service account once for every Google account that you want to access Google resources with; you can skip this step the next time.
Create a google service account
Follow the steps below to create the service account and generate the necessary credentials:
In the “APIs and services” navbar on the left, go to the “Credentials tab”
Click on “+ CREATE CREDENTIALS” and select “Service Account”
In the first step (service account details), provide a name for the service account, e.g., “gsheet-operator” and click on “Create and continue”
In the second step, select the “Owner” role and click “Continue”
In the third step, don’t change anything and click “Done”
Once back on the Credentials page, click on the service account you just created.
Go to the Keys tab, click “Add Key > Create new key”
Choose “JSON”, then click “Create.” The JSON file with your service account credentials will automatically download to your computer.
You now have a service account and a JSON credentials file! Head over to your Downloads folder or wherever the JSON file was downloaded, open it up, and have a look. It should look something like this:
{
  "type": "service_account",
  "project_id": "<your-project-name>",
  "private_key_id": "<something-private>",
  "private_key": "-----BEGIN PRIVATE KEY-----\nM<some-very-private-stuff\n",
  "client_email": "g[email protected]",
  "client_id": "123456789012345678901",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/gsheets-operator%40<project-name>.iam.gserviceaccount.com"
}
​
There’s a client_email field in there: gsheets-operator@<google-project-name>.iam.gserviceaccount.com. Copy this email to your clipboard; you’ll need it in the next step.
Create a Google Spreadsheet With Data
Before you can analyze data in spreadsheets, you need to make sure you have a spreadsheet with data in it. If you don’t have a dataset lying around, you can start from a Google Sheet that we prepared for this tutorial: Open the example spreadsheet and once in the Google Sheet, click “File > Make a copy,” specify a name, and click “Make a copy.” If you already have a spreadsheet with data you want to analyze, just open up that spreadsheet.
Regardless of whether you’re working with a duplicate of the example spreadsheet or your own spreadsheet, you need to give the Google service account that you created in the first step access to the spreadsheet:
Click “Share”
Add the email of the service account that you copied to your clipboard in the previous step as an editor to the spreadsheet (e.g. gsheets-operator@<google-project-name>.iam.gserviceaccount.com)
Click “Send”
Share a Google Spreadsheet with a service account
Alright, service account: check. Google spreadsheet with the right access: check.
Create a new workspace
Click this link to create a workspace in your own account that contains all the Python code you need to connect to Google Sheets.
Store service account credentials in Workspace
To use the service account credentials JSON in your newly created workspace, you need to store it in Workspace. To do so securely, you can use Environment Variables. In your new workspace, click on "Environment", and click on "+" next to "Environment variables":
Set Name to GOOGLE_JSON
Set Value to the full contents of the service account JSON file that was downloaded. You can do this by opening the JSON file, selecting all, copying it to your clipboard, and then pasting it in the Value field.
Set the “Environment Variable Set Name” to “Google Service Account” (this can be anything, really)
Set up GOOGLE_JSON environment variable
After filling in all fields, click “Create,” “Next,” and finally, “Connect.” Your workspace session will restart, and GOOGLE_JSON will now be available as an environment variable in your workspace. 
If you want to reuse the same services account credentials in another workspace, you don’t need to set up the environment variable again: you can connect the environment variable to your other workspaces as well.
Read from Google Sheets and write back to Google Sheets
Use the Python code snippets in the workspace that you can create from this link to install the necessary packages, read data from the Google spreadsheet, analyze it and write data back to the Google spreadsheet. All from Python!
Last modified 1mo ago