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.