Thursday, July 7, 2011

Datalogging with Google Spreadsheets

NOTE:

The information on this page may be out of date, so I've created an updated tutorial




I've seen many hacks that involve logging data to Twitter, but I've never thought highly of using Twitter as useful place to log things.  The posts are all public and it seems like it would be difficult to do anything with the resulting data.  Plus, do your followers really want to know how much power a device in your home is using (hacking the Kill-A-Watt is awesome though) or how often you flush the toilet?  Wouldn't it be nicer to log data directly into a private spreadsheet spreadsheet, which is much better suited for analyzing and graphing data?

I'm here to tell you that it is easy to log data remotely using Google Spreadsheets and any client that can send a HTTP POST request.

In some upcoming posts I will show you two ways I am using this method:
  • Log gym visits automatically whenever I arrive at the gym (from my Android phone with Locale and a plugin)
  • Log whenever my wireless router restarts
Google provides a Spreadsheets API already, but it seems intended for use in standalone applications.  Here I will present a slightly easier method of logging simple data to Google Spreadsheets.

1.  Go to Google Docs and create a new spreadsheet.
2.  Select Tools > Form > Create a form


3.  Edit the form as shown here to include whatever data you wish to log.  If you only want to log one piece of data (time and data is logged automatically by Google) you probably want to delete the second sample question in the form as shown.


At this point, what you need to know is the form key above in the red circle (mine is masked).  You can skip the rest and move down to step #12 if you want.

4.  Now you should see two columns.  Here is where your data will be populated.


5.  Now go to the live form by selecting Form > Go to live form.


6.  You will see a form like this.  Try it out by entering a number and clicking submit.


7.  Google will give you a note of thanks.  The idea here is that form is filled out and submitted by someone else, perhaps someone taking a survey.


8.  You should see your entry in the spreadsheet:


9.  Now let's see how we can populate this form without visiting it.  I'm using a tool an add-on for Firefox called Firebug that, among lots of other cool things, lets you watch requests from the browser.  Go back to the live form, open up Firebug, and make sure all panels are enabled.  Select the NET panel as shown:

10.  Now, fill in the form and hit submit.  You should see the communication between your browser and the page.


11.  The POST entry is the one you want.  Firebug makes this ridiculously easy to grab what was sent.  Just hover over the POST entry and select "Copy Location with Parameters".






12.  Now the URL for the form is in your clipboard.  I've highlighted in yellow the temperature I entered.  If you arrived here from step #3, you need to substitute your form key below (highlighted green):

https://spreadsheets.google.com/spreadsheet/formResponse?formkey=aabb112233445566ZZxxYYAABBccffGGRRttrr4455&ifq&backupCache=&entry.0.single=192&pageNumber=0&submit=Submit


13.  This is all you need.  Hit this URL with whatever mechanism you wish (from the Linux command line you can use curl), change the values highlighted with your values, and you can log data to Google Spreadsheets.

Note that the URL is HTTPS, not just HTTP, so you will need to use a method that supports SSL.

6 comments:

  1. cool, thanks a lot. I can really use this.

    ReplyDelete
  2. Hey William, is this also possible the other way round, from spreadsheet back to the mobile (e.g. somehow by GET). Do you have by any chance another tutorial therefore?
    Thanks a lot

    ReplyDelete
    Replies
    1. Hi Dieter, I thought I replied to this but I guess not... There should be a way to get your spreadsheet data back with GET and the Google Spreadsheets API. See this: https://developers.google.com/google-apps/spreadsheets/#working_with_list-based_feeds

      Delete
  3. Hello:

    Does this procedure still work? When I display Network-All in Firebug and then right-click on the POST statement, there is no option named "Copy Location with Parameters." The only options displayed are:
    Copy URL
    Copy POST Data
    Copy cURL
    And none of these options seem to work.

    I would love use Google sheets as a data storage location, so any help would be greatly appreciated. Thanks !


    ReplyDelete
    Replies
    1. Hi Roger, thanks for the question. I've updated the tutorial for the new version of sheets. Try this and let me know if it helps you: http://leftbraintinkering.blogspot.com/2017/04/updated-datalogging-with-google-sheets.html

      Delete