Connecting to Googles Docs (Updated)

Google sometimes will update their API and cause issues with the gspread library. Consult this thread for information on converting a spreadsheet to an old-style spreadsheet if you have problems accessing your sheet:
As of April 2015 Google has deprecated an old authentication interface for updating Google Sheets. You must carefully read the new steps below to make your Google Sheets work with the new OAuth2 authentication scheme.

Create and prepare spreadsheet

First up you will need to sign up for Google Docs and create a spreadsheet. We're going to call ours DHT Humidity Logs.

Once you've created it, delete all but one line (since we don't want 1000 empty rows):

Then make the one remaining line a header with row names:

Get OAuth2 credentials

As of April 2015 Google has deprecated the older simple authentication interface for accessing Google spreadsheet data.  You must carefully follow the steps below to enable OAuth2 access to your Google spreadsheet.  Unfortunately these steps are somewhat complex, so go through them very carefully to make sure you don't miss a step.  If you run into problems try consulting the gspread python library that this script uses.

To get your OAuth2 credentials follow the steps on this page:

After you follow the steps in the document above you should have downloaded a .json file, like SpreadsheetData-(gibberish).json.  Place this .json file in the same directory as the google_spreadsheet.py example.  If you don't place this file in the same directory then authentication will fail and you will not be able to update your spreadsheet!

One last step that must be completed is to share your Google spreadsheet to the email address associated with the OAuth2 credentials.  Open the .json file and search for the "client_email": line that looks like this (but with a different email address):

"client_email": "[email protected]account.com",

Take note of that email address value and go to your Google spreadsheet in a web browser.  Using the File -> Share... menu item share the spreadsheet with read and write access to the email address found above.  Make sure to share your spreadsheet or you will not be able to update it with the script!

Run Python Code

First up we will have to install the gspread python library, which will do the heavy lifting of connecting to google docs and updating the spreadsheet! With your board connected and online, run the following:
sudo apt-get update
sudo apt-get install python-pip
sudo pip install gspread oauth2client
Next, in the examples directory again, edit google_spreadsheet.py and adjust the configuration values towards the top of the file:
# Type of sensor, can be Adafruit_DHT.DHT11, Adafruit_DHT.DHT22, or Adafruit_DHT.AM2302.
DHT_TYPE = Adafruit_DHT.DHT22

# Example of sensor connected to Raspberry Pi pin 23
DHT_PIN  = 23
# Example of sensor connected to Beaglebone Black pin P8_11
#DHT_PIN  = 'P8_11'

# Google Docs OAuth credential JSON file.  Note that the process for authenticating
# ...
GDOCS_OAUTH_JSON       = 'your SpreadsheetData-*.json file name'

# Google Docs spreadsheet name.
GDOCS_SPREADSHEET_NAME = 'your google docs spreadsheet name'

Make sure DHT_TYPE is set to the type of sensor you are using (either Adafruit_DHT.DHT11, Adafruit_DHT.DHT22, or Adafruit_DHT.AM2302), and DHT_PIN is set to the GPIO pin number which is connected to your DHT sensor.

In the example above a Raspberry Pi GPIO pin #23 is shown, however commented below it is an example of a Beaglebone Black using GPIO pin P8_11.

Next make sure to set the GDOCS_OAUTH_JSON to the name of the SpreadsheetData-*.json file in the same directory as the google_spreadsheet.py file.  If you don't have a SpreadsheetData-*.json file then you accidentally missed the steps above.  Go back and carefully follow the OAuth2 credential steps to get an OAuth2 credential .json file before continuing!

Finally set GDOCS_SPREADSHEET_NAME to the name of your spreadsheet, like 'DHT Humidity Logs'.

Save the file and execute the Python script by running:

sudo ./google_spreadsheet.py

You should see the program run and after about 30 seconds a humidity and temperature measurement is displayed and written to the spreadsheet. The program will continue to run and log a measurement every 30 seconds until you force it to quit by pressing Ctrl-C.

The measurement frequency can be adjusted by changing the FREQUENCY_SECONDS configuration in the python code.

Open the spreadsheet on Google's site and you should see measurements added in real time!

This guide was first published on Aug 24, 2012. It was last updated on Nov 16, 2018. This page (Connecting to Googles Docs (Updated)) was last updated on Nov 20, 2015.