Using Google Spreadsheets for Logging Sensor Data

UPDATE 8/7/2104:

It appears that Google occasionally will randomly and silently drop the authorization for an Apps Script web app. If you notice that your spreadsheet has stopped updating and you know that your logger is still working, then you probably need to log into your spreadsheet from a web browser, go into the scripts editor, and manually execute any function in the script. This will cause a popup that will reauthorize the script and everything will then start working again.

I think this is the last straw. I can not recommend using Google for logging (or any other non-trivial application) any more. This stuff is just too flaky. Sorry.

 

You want to…

  • remotely monitor the temperature in your greenhouse for the past 24 hours, but only have a dynamic IP address on premises
  • keep a historical record of 1,500 wind readings readings per hour from net-connected anemometers at your favorite surfing spots
  • create an earthquake monitoring board that you can distribute to 1,000’s of people and have all the boards report back any earthquakes to a central data store that everyone can see and run analysis on in real-time

Now that Google Spreadsheets can handle up to 2 million cells, they are a great place to log all your sensor data. You can easily view the live data from any computer on the internet and even publish fancy live charts and dashboards. All for free!

Benefits include:

      • You can view the data in real-time from any computer on the internet.
      • You can make and publish live charts and graphs.
      • You can easily share the data, or keep the data private.
      • You can analyze the data directly using spreadsheet functions and Apps Scripts.
      • You can set up triggers to email/text you based on incoming data.

  • You can log data from any platform that can generate an HTTP GET or PUT request (pretty much any thing that can access web pages).
  • You do not need any servers or port forwarding.
  • The logging device does not need to know your Google username or password – just a special URL that logs directly to a specific sheet.
  • Lots of storage space.
  • Free!

For an example, open this logging example sheet in a different browser window and scroll down to the bottom. Next, go here….

https://script.google.com/macros/s/AKfycbybz3CqE3qphh2VUwr4n6WofIIbbDKWKcF7KTrgQMlVWKjuI4A6/exec?TEMP=70.2&WIDTH=202&SPEED=77

You should see a new line of data appear at the bottom of the spreadsheet.  Next try this URL…

https://script.google.com/macros/s/AKfycbybz3CqE3qphh2VUwr4n6WofIIbbDKWKcF7KTrgQMlVWKjuI4A6/exec?TEMP=212.0&WIDTH=15&SPEED=25

If your logging client is running Linux, you could execute a command like…

curl -k "https://script.google.com/macros/s/AKfycbybz3CqE3qphh2VUwr4n6WofIIbbDKWKcF7KTrgQMlVWKjuI4A6/exec?TIMESTAMP=$(date +%F@%T)&TEMP=68.2&WIDTH=190&SPEED=55"

(note that the quotes around the URL are important)
Get the idea? You can play more here. Note that knowing the URL does not let you modify the spreadsheet- only append data to it. Conversely, you also do not need to have access to the logging spreadsheet to be able to add data to it using the URL – you do not even need to be logged into Google (good for embeded devices).

Setting up your own Google spreadsheet for logging

Thanks to several bugs and missing features, this turns out to be a pain to set up. Luckily, you can forget all the hassle once it is set up – and I am about to tell you exactly how to set it up.

You will need a free Google Drive account to set up your own logging spreadsheets.

Step-by-step

    1. Open this template spreadsheet…

https://docs.google.com/spreadsheets/d/1k6PqkijdBL94LuuoGCvwFkGDsOZLb1pX6mP2QJJ5pm0/edit?usp=sharing

  1. Choose “Make a copy” to make a copy into your account…image
  2. From the new copy you just make, choose “Setup Logging –> Setup Script”…image
  3. Follow the prompts to authorize the script.
  4. Choose “Tools->Script Editor”…image
  5. Choose “Deploy as Web App”…
    image
  6. Click “Save New Version”…image
  7. Select “Anyone, even anonymous” for “Who has access to the app”…image
  8. Click “Deploy”…
    image
  9. You win! Copy the resulting URL. Make sure you get the whole thing.image

You can now use this URL to log data your spreadsheet!

How it works

You can add any parameters you want to the end of the URL you got above. The script will automatically check the top row of the spreadsheet for parameter names and add any it does not find. It will then add a new row to the bottom of the spreadsheet with the parameters in the appropriate columns. The order of the parameters in the URL is not important.

If you are using HTTP GET method (as shown above) to make the call, you might want to add a timestamp or sequence number field to avoid getting dupes if the GET request gets resent.

Alternately, you can also do an HTTP POST to the same URL and the data inside the POST will be appended to the spreadsheet. You can even use the logging URL as the action for HTML forms, so you can use this technique to make your own more flexible version of Google Forms.

FAQ

Q: Couldn’t you just use Plot.ly or something like that?
A: I bet you could, but spent a good hour trying to get Plot.ly set up and failed. I tried a few others too, and nothing is as convenient, easy, or free as a Google spreadsheet – and once you have the data in the spreadsheet there is a ton of other stuff you can then do on Google (sharing, triggers, analysis, graphing) very easily as well.

Q: How about TEMBOO?
A: Temboo is cool and in theory could make all this stuff very simple, unfortunately it has two deal breakers for me.

First breaker is that they require me to put my Google username and password into the program that will go into the embedded device. Yikes! I don’t tell my google password to anyone – much less put it in plaintext inside a portable device. They could have mitigated this problem by doing OAUTH on their servers to get access to the spreadsheet, and then given you a token to put into the device that can only do the specific things you specify and could easily be monitored and revoked.

Second breaker is their pricing model. The first 1,000 calls/month are free and then it starts costing money for every call. This just doesn’t work for an application where I want to log a data point every few seconds- especially when you can do it faster, more reliably, and more securely yourself. It especially doesn’t work when I want to have LOTS of logging things in my life.

Q: Can’t you just script the above steps for me in Apps Script and give me an easy menu choice?
A: Google Apps Script does have a a Service Class that do exactly that, unfortunately is has been broken for more than a year and there is no sign it will be fixed anytime soon.

Q: How can I control who can log to my spreadsheet?
A: First off, you can control who you give the logging URL to. It is presumed that google generates these URL securely, so as long as you do not publish the URL then no one can push data into your spreadsheet.

But what if you wanted to make a logging application where lots of independent devices could all add data to a single spreadsheet? In that case, you could a token parameter to the URL and a bit of code in the processing script to check the incoming token against a private list of authorized tokens. You’d only add the data if the token was found on the list. You’d then add a new token to the list (possibly automatically via another script) each time you wanted to authorize a device, and you could always delete the token (and any data it had previously logged) if you suspect it was compromised.

UPDATE 7/14/14 – Looks like SparkFun has just set up a service that also lets you log sensor using a simple URL-based scheme. It looks well done, but I think I still prefer using a Google spreadsheet because you get way, way more space and you can easily do stuff with the data like calculations, graphs, and alerts.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s