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):
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": "149345334675-md0qff5f0kib41meu20f7d1habos3qcu@developer.gserviceaccount.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 pip3 install gspread oauth2client pyasn1 pyasn1-modules
Create a new file called google_spreadsheet.py with nano or your favorite text editor and put the following in:
# SPDX-FileCopyrightText: 2019 Tony DiCola for Adafruit Industries
#
# SPDX-License-Identifier: MIT
"""
Google Spreadsheet DHT Sensor Data-logging Example
Copyright (c) 2014 Adafruit Industries
Author: Tony DiCola
Modified by: Brent Rubell for Adafruit Industries
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
"""
import sys
import time
import datetime
import board
import adafruit_dht
import gspread
from oauth2client.service_account import ServiceAccountCredentials
# Type of sensor, can be `adafruit_dht.DHT11` or `adafruit_dht.DHT22`.
# For the AM2302, use the `adafruit_dht.DHT22` class.
DHT_TYPE = adafruit_dht.DHT22
# Example of sensor connected to Raspberry Pi Pin 23
DHT_PIN = board.D4
# Example of sensor connected to Beaglebone Black Pin P8_11
# DHT_PIN = 'P8_11'
# Initialize the dht device, with data pin connected to:
dhtDevice = DHT_TYPE(DHT_PIN)
# Google Docs OAuth credential JSON file. Note that the process for authenticating
# with Google docs has changed as of ~April 2015. You _must_ use OAuth2 to log
# in and authenticate with the gspread library. Unfortunately this process is much
# more complicated than the old process. You _must_ carefully follow the steps on
# this page to create a new OAuth service in your Google developer console:
# http://gspread.readthedocs.org/en/latest/oauth2.html
#
# Once you've followed the steps above you should have downloaded a .json file with
# your OAuth2 credentials. This file has a name like SpreadsheetData-<gibberish>.json.
# Place that file in the same directory as this python script.
#
# Now one last _very important_ step before updating the spreadsheet will work.
# Go to your spreadsheet in Google Spreadsheet and share it to the email address
# inside the 'client_email' setting in the SpreadsheetData-*.json file. For example
# if the client_email setting inside the .json file has an email address like:
# 149345334675-md0qff5f0kib41meu20f7d1habos3qcu@developer.gserviceaccount.com
# Then use the File -> Share... command in the spreadsheet to share it with read
# and write acess to the email address above. If you don't do this step then the
# updates to the sheet will fail!
GDOCS_OAUTH_JSON = 'your SpreadsheetData-*.json file name'
# Google Docs spreadsheet name.
GDOCS_SPREADSHEET_NAME = 'DHT'
# How long to wait (in seconds) between measurements.
FREQUENCY_SECONDS = 30
def login_open_sheet(oauth_key_file, spreadsheet):
"""Connect to Google Docs spreadsheet and return the first worksheet."""
try:
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(oauth_key_file, scope)
gc = gspread.authorize(credentials)
worksheet = gc.open(spreadsheet).sheet1 # pylint: disable=redefined-outer-name
return worksheet
except Exception as ex: # pylint: disable=bare-except, broad-except
print('Unable to login and get spreadsheet. Check OAuth credentials, spreadsheet name, \
and make sure spreadsheet is shared to the client_email address in the OAuth .json file!')
print('Google sheet login failed with error:', ex)
sys.exit(1)
print('Logging sensor measurements to\
{0} every {1} seconds.'.format(GDOCS_SPREADSHEET_NAME, FREQUENCY_SECONDS))
print('Press Ctrl-C to quit.')
worksheet = None
while True:
# Login if necessary.
if worksheet is None:
worksheet = login_open_sheet(GDOCS_OAUTH_JSON, GDOCS_SPREADSHEET_NAME)
# Attempt to get sensor reading.
temp = dhtDevice.temperature
humidity = dhtDevice.humidity
# Skip to the next reading if a valid measurement couldn't be taken.
# This might happen if the CPU is under a lot of load and the sensor
# can't be reliably read (timing is critical to read the sensor).
if humidity is None or temp is None:
time.sleep(2)
continue
print('Temperature: {0:0.1f} C'.format(temp))
print('Humidity: {0:0.1f} %'.format(humidity))
# Append the data in the spreadsheet, including a timestamp
try:
worksheet.append_row((datetime.datetime.now().isoformat(), temp, humidity))
except: # pylint: disable=bare-except, broad-except
# Error appending data, most likely because credentials are stale.
# Null out the worksheet so a login is performed at the top of the loop.
print('Append error, logging in again')
worksheet = None
time.sleep(FREQUENCY_SECONDS)
continue
# Wait 30 seconds before continuing
print('Wrote a row to {0}'.format(GDOCS_SPREADSHEET_NAME))
time.sleep(FREQUENCY_SECONDS)
# Type of sensor, can be adafruit_dht.DHT11 or adafruit_dht.DHT22. # For the AM2302, use the adafruit_dht.DHT22 class. DHT_TYPE = adafruit_dht.DHT22 # Example of sensor connected to Raspberry Pi Pin 23 DHT_PIN = board.D4 # 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.DHT11oradafruit_dht.DHT22), and DHT_PIN is set to the GPIO pin number which is connected to your DHT sensor. If you're using an AM2302, use the adafruit_dht.DHT22 class.
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:
python3 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!
Text editor powered by tinymce.
