Codebox: Save sensor data to Google Spreadsheets

The “Hello Arduino” section in Chapter 11 of Getting Started with Processing shows how to read data into Processing from Arduino. In a nutshell, the Arduino code (example 11-6 in the book) reads data from a light sensor and writes it out to the serial port. The section then goes on to describe a number of increasingly sophisticated sketches that retrieve and visualize the sensor data using Processing’s Serial library.

This Codebox shows you how to save this sensor data to a Google Spreadsheet. The cool thing is that you can then use any of the goodies that Google provides (charts, gadgets, maps, etc) directly with your data. While the light sensor is pretty basic, you can use this basic setup to record data from more sophisticated sensors, such as a Parallax GPS receiver module into Google Spreadsheets, and then create a map of where you’ve been that you could post as a gadget.

The sketch relies on the Google API Client Library for Java, which is a set of code libraries for interacting with various Google’s services (not just Spreadsheets). In researching this article, I found Processing guru Jer Thorpe‘s article Open Science, H1N1, Processing, and the Google Spreadsheet API a great inspiration. While it’s based on an older version of the API (version 1.0, while the APIs are now up to version 3.0), it’s a great introduction to interacting with Google. Thanks, Jer!

Create your spreadsheet

The first step in the project is to set up a Google Spreadsheet. To do this, you’ll need a Google account (obviously!). Sign in, and then go into documents to create a new spreadsheet named “sensor log.” Then, add the following columns in first row: “date,” “time,” and “reading.” As we’ll see shortly, these column headers are used in your code to interact with the data in the spreadsheet. Then save the spreadsheet. (Leave it open, though.)

Set up the sketch

Next, a bit of housekeeping. The Google data client libraries use generics and other goodies introduced in Java 1.5, so you may need to update your version of Processing to release 0136 or higher. If you’re not sure which version you have, download the latest version of Processing to make sure. Otherwise, you’re in for a lot of head scratching.

Once you’ve confirmed you’re at release 0136+, download the Java client library gdata-samples.java-1.40.0.zip. [NB: This is a slightly older version of the libraries — there are some problems with the most recent version hanging on a Mac, so people recommended downgrading until Google fixes the problem.] You will also need Guava, the Google Collection library.

Once you’ve downloaded and unzipped the files, fire up Processing and paste google_spreadsheet_writer.pde into the sketch area:

Next, add the following four files to your project from the libraries you downloaded earlier (use the Sketch -> Add files menu item):

unzipped guava directory/guava-r07.jar


Finally, you’ll need to update a few variables in the sketch to set your username, password; you’ll also need to make sure that you enter the *exact* name of the spreadsheet you created at the start of he project. Here are the lines you need to modify:

String uname = "[enter your google account here]";  //Your google account user name
String pwd = "[enter your google account password here]";  //Your google account password
String spreadsheet_name = "sensor log";  //Name of the spreadsheet you want to write data to.  Must match exactly, including case.

Once you’re done, fire up the sketch. Assuming you’re running the Arduino code from Example 11-6 of Getting Started with Processing, you should start to see your spreadsheet filling up with sensor data every 2 seconds.

Discussion

I’ll be honest: the hardest part of this project is getting all the libraries set up correctly. Once you do, there’s really not that much to the code itself. The Developer’s Guide: Java documentation does a good job describing most of what you need to know. (Note that this is an older version of the documentation. I found it much more clearer than the most recent version, which you can find at Developer’s Guide (v3.0).)

As you’ll see in the setup() method, the sketch begins by authenticating your credentials. Assuming you check out, it then pulls a list of all your spreadsheets and finds the one that has the same title as that defined in the spreadsheet_name variable. Finally, it pulls out the first worksheet (these are the various tabs in the spreadsheet) and saves it for future use. From there, the sketch drops into the draw() method, which uses a timer to periodically call the transmit() function.

transmit() is where most of the work happens. It starts by creating a couple of strings that hold the current date and the time, and then creates a new ListEntry, which is the primary data class used to interact with worksheets. The class method getCustomElements().setValueLocal() is used to map the Processing variables to the column names in the worksheet, as shown the next figure:

Once you get this sketch working, the possibilities are endless. Well, OK, they’re not endless. They’re finite. But, there are a lot of possibilities, nevertheless.

Enjoy!

More:
All of the Codebox columns can be found here

In the Maker Shed:

Getting Started with Processing
Learn computer programming the easy way with Processing, a simple language that lets you use code to create drawings, animation, and interactive graphics. Programming courses usually start with theory,but this book lets you jump right into creative and fun projects. It’s ideal for anyone who wants to learn basic programming, and serves as a simple introduction to graphics for people with some programming skills.