Codebox: Save sensor data to Google Spreadsheets

Arduino
Codebox: Save sensor data to Google Spreadsheets
MZ_Codebox.gif

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.

ano_sensor_spreadshee_120910.png

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 gdata directory/java/java/lib/gdata-client-1.0.jar

unzipped gdata directory/java/java/lib/gdata-core-1.0.jar

unzipped gdata directory/java/java/lib/gdata-spreadsheet-3.0.jar

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:

ano_spreadsheet_col_header_map_121010.png

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:

Makershedsmall

processingCover.jpg

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.

42 thoughts on “Codebox: Save sensor data to Google Spreadsheets

  1. Stairs says:

    When I go to run the sketch I get “Syntax error, maybe a missing semicolon?” with the following line highlighted?

    for (SpreadsheetEntry entry: feed.getEntries()) {

    1. Andrew Odewahn says:

      @Stairs: Try upgrading to the latest version of Processing — some of the new Java syntax isn’t supported in older versions.

      1. Stairs says:

        Thank you I will give that a try. Sorry for the short original comment as it was late and I was spent. I haven’t been able to use Processing in a little while and I have seen this error before but couldn’t remember how I used to handle it…

        For more information, I had copied and pasted the code just as it is above. I was going to run it with no arduino attached to see what would happen but I never got that far because of the syntax error. One question in that regard, what will the code do if there is no data coming from the serial port? Will it still write the time, or not write anything at all?

      2. Stairs says:

        So I downloaded the latest Processing…I got past the error described above but now it stops at the following line in void Draw():

        float val = “0.0”;

        and says “cannot convert from String to float”

        Any thoughts?

        1. Stairs says:

          …got past that one by removing the quotation marks. Then it got to

          String arduinoPort = Serial.list()[0];

          in void setup() and says “ArrayIndexOutOfBoundsExeption: 0” but then again I don’t have an Arduino hooked up to it right now so I’ll keep working at it. If I get a chance, I will pull my arduino out.

          1. Stairs says:

            Alright, it was b/c I didn’t have a serial port; arduino not plugged into usb. Seems like the problem is solved as it says, “Found worksheet sensor log.”

            Thanks for this great post! It is what I have been waiting for to really get started!

          2. Andrew Odewahn says:

            @stairs — Glad it’s working. You should be able to open the spreadsheet and watch it fill up with data from Arduino (or whenever you press a key, if you’re taking Arduino out.)

  2. Andrew Odewahn says:

    @Stairs. Removing the quotes is the right thing to do to fix that. Not sure how that slipped through in the final version, but I’ll update the code repo in a minute to fix it.

    On not having the arduino — yes, it just blows up, as you’ve observed. (I didn’t add any exception handling for that, which would have been a good idea.) If you want to just explore how to write to Spreadsheets, just tear out all the serial port stuff for Arguino and call the transmit() procedure in the keyPressed() method. (For example.) Just pass in a random value and you’ll see it showing up.

    Thanks for your persistence and good notes!

    1. Stairs says:

      Even without a sensor it is writing data, with the date, time and zeros for data! SUCCESS!!!

      Thanks again!

      1. David Trejo says:

        hey! I’m trying to use this code, could you please send e the code you used for this? thanks! :D

  3. kevbrown says:

    This rocks.

    My favorite part about your examples (and Processing in general) is the ease with which the skeleton can be converted to other tasks, especially ones for which I might just want a quick solution, but are FUN, like going through all my Picasa photos, downloading them, applying some change in Processing, then uploading the transformed pictures to a new album. Or maybe make a spreadsheet documenting the frequency of words in my photo titles. Pretty cool.

    Cheers!

  4. Iskander Ahmed says:

    Hi Andrew , first i want to thank you for an awesome tutorial , this was exactly what i was looking for! I am having a problem however, although ive thoroughly followed your steps i keep getting an error in my processing sketch! The error reads ” cannot convert string to float” and highlights the end bit of the code thhat reads ” float val = “0.0” ” ive parsed through the code itself and i cant seem to find an error . Could you possibly help me out? or anyone else who has those working perhaps ?
    Thanks !

    1. morten says:

      same problem…

  5. Haiyan Zhang says:

    This article is awesome. I just got it working with an arduino Fio communicating wirelessly over XBee. Which means I can place remote sensors and have the readings sent to a google spreadsheet.

    My only question is have you figured out how to access the spreadsheet without hardcoding in your google username and pwd? Seems like a big security hazard given how much more your google account is being used for.
     

  6. Haiyan Zhang says:

    This article is awesome. I just got it working with an arduino Fio communicating wirelessly over XBee. Which means I can place remote sensors and have the readings sent to a google spreadsheet.

    My only question is have you figured out how to access the spreadsheet without hardcoding in your google username and pwd? Seems like a big security hazard given how much more your google account is being used for.
     

  7. Andy Forsberg says:

    Great tutorial, especially for noobs like myself. I’m having some issues expanding the concept to drive data to additional spreadsheet columns. It appears as though it doesn’t like column header names to include spaces or special characters- am I right about this? I’m also having it work for some columns but not others, and I’m having trouble figuring out exactly why. Are there any special rules or naming conventions to adhere to?

    thanks!

  8. morten says:

    is a multiple sensor data sending also possible? how must change ….void transmitData(float val) ?

    1. ieeeup says:

      Hi Morten! Just a question… could you use this code? Im trying to make it work but I can’t. If you get this code working, could you please post it? Thanks! :D

  9. ieeeup says:

    Hi! Just a question… is this code still workind?? Im trying to make it work but I can’t. If you get this code working, could you please post it or give me a hand? Thanks!

  10. morten says:

    its working

  11. Glenn says:

    I got your sketch working and it’s great. I’m trying to add a read function but I’m struggling (new to this). This is the section I added and then I added a call to this in draw. Any ideas on how to do this?

    void retrieveData() {
    // Fetch the cell feed of the worksheet.
    URL cellFeedUrl = worksheet.getCellFeedUrl();
    CellFeed cellFeed = service.getFeed(cellFeedUrl, CellFeed.class);

    println(service.getCellValue(0,2));

    } catch (Exception e) {
    println(e.getStackTrace());
    }

Comments are closed.

Discuss this article with the rest of the community on our Discord server!
Tagged

ADVERTISEMENT

Maker Faire Bay Area 2023 - Mare Island, CA

Escape to an island of imagination + innovation as Maker Faire Bay Area returns for its 15th iteration!

Buy Tickets today! SAVE 15% and lock-in your preferred date(s).

FEEDBACK