I’ve written quite a bit about the ways that Raspberry Pis can be used as dataloggers, and how their data can be instantly posted on the internet. ThingSpeak is great for this, but I wanted to work on this with Google Sheets. This tutorial walks you through the process of setting up the Raspberry Pi to take data from a sensor and immediately plug it into Google Sheets using the gspread library.
Set up the Sensor
I’ve already written a tutorial for the BME280 sensor. It is very thorough, and you can find it here. If you haven’t set it up already, you will need to do this first.
Set Up Spreadsheet
You will need a Google account with developer access. There is an excellent tutorial in the Twilio Blog, and I have summarized the important steps here. I would also recommend checking out the gspread docs. This is a little complicated, but if it is important to get your data on the internet quickly, its worth it.
- Open a new blank spreadsheet in Google Drive.
- Give it a name, and make some column labels.
- Log in to the Google API Console. It is part of the Google Cloud Platform. You will need to use an existing project, or set up a new one. Click on the symbol next to the Google API logo.
We will make a new project.
- Give the project a name. I’ve picked Sensor.
- Once it is created, select that project from the same place where you made it. You will come to this screen. Click on ENABLE APIS AND SERVICES. Then click on Google Drive API.
You will come to this screen.
- Now you will need to set some credentials so the Raspberry Pi can interact with Google on your behalf.
Click on Credentials.
- Create a service account key. Click on the prompt for it.
You will need to create a new service account that will need a name and a role. I’ve named mine Raspberry Pi and given it the role of Editor.
- Once you click on Create, a json file will be downloaded automatically. You will need that file.
- Inside that file there is an email address. You will need to find it. I’ve opened it in Sublime Text, but notepad is also fine.
Copy that email address. We will need it in the Google Sheet you created.
- Reopen your sheet. Click on File>Share. Add that email address. Your RPi will use it to identify itself.
- Finally, rename the downloaded file. I called mine client_secret.json. You will need to reference it in the python program below. Put it in this folder (you set this up when you set up the sensor).
That is pretty much it from the Google side of things. You can move on to the Pi to load a couple libraries. Maybe get some coffee.
Set up the Raspberry Pi
You’ll need to download a couple python libraries to make this work. Again, if you plan on doing this with the Adafruit BME280 Pressure, Temperature, and Humidity sensor, you hopefully already downloaded those libraries. For this project it is important, due to the location of various files in our final python program.
Download the gspread and the oauth2client python libraries
sudo pip install gspread oauth2client
You’ll also need to update oauthlib
sudo pip install --upgrade google-auth-oauthlib
At this point your Pi is ready to go, and your spreadsheet is all set up. This is the python program I used to run the program.
For this to work, this python program needs to be in the same folder from when the sensor got set up. Also, be sure that the json file that you downloaded previously is in the same folder. I changed the name of the file to client_secret.json.
import gspread from oauth2client.service_account import ServiceAccountCredentials from Adafruit_BME280 import * scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json',scope) client = gspread.authorize(creds) sheet = client.open("Sensor").sheet1 sensor = BME280(t_mode=BME280_OSAMPLE_8, p_mode=BME280_OSAMPLE_8, h_mode=BME280_OSAMPLE$ degrees = sensor.read_temperature() pascals = sensor.read_pressure() hectopascals = pascals / 100 humidity = sensor.read_humidity() values=[degrees,hectopascals,humidity] sheet.append_row(values)
This is a simple program, and every time you execute it, the spreadsheet gets updated.
From here, you can do pretty much anything you want. You can update the python program to include a column for time, and make interesting plots. You can also embed this sheet or a plot into another webpage, so you can make your own dashboard.