Using Google Spreadsheets for Logging Sensor Data

UPDATE 8/6/2017:

IFTTT For Makers is also a great way to log data to a good spreadsheet. It does add an extra point of failure, but the interface is very clean and simple.

UPDATE 6/23/2015:

I’ve been using Wolfram DataDrop for about a month now for logging sensor data and I like it much better than Google Spreadsheets. It is reliable and well thought out, and super-duper easy to use. If you take the extra step and drink the Wolfram cool-aid (not necessary to just use the DataDrop), then you can really do some very cool things with shockingly little work.

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.

15 comments

  1. Roger

    I looked at Wolfram as you suggested above. Apparently, they only save data for a month for free. Is that correct? Thanks !

    • bigjosh2

      I don’t know how they price it. It might be different for different users, or might be base don what kind of account you have. I have a data drop that has been running since July and has 10,000+ entries and I have never paid any money.

      • Roger

        That seems odd to me. I created a data drop and the email response says:
        “Expires On: Tuesday 22 December 2015” Do you know that this means? Thanks again.

    • bigjosh2

      Great idea, but just the first two things I tried to do didn’t work. My guess is that Sheetsu is running into some of the same Google Spreadsheet flakiness that I did. Even makes me more hesitant to use these for anytime that matters.

  2. Tim

    Do you have any code for Wolfram, I am using an Osepp Uno R3 Plus and a Dragino Yun Shield. I want to log sensor data, temp etc to a cloud Drive. Personal use only.

    • bigjosh2

      DataDrop is super simple- there is not code. When you create the drop they will give you a cut/paste `curl` command to add data to the drop. There are fancy things you can do like adding units to the data, but you can start doing useful logging with like 1 minute’s work. I’ve also seen that Microsoft has a new Azure IOT service that could be interesting as well.

  3. Phlow

    Hi Josh,
    Thanks for the neatly laid out steps. I am new to programming and want to test with a simple LDR without needing the temp sensor library. Can you help strip down? Also, couldn’t get your sketch to upload on my IDE(1.6.9)-giving various errors e.g sketch_jul25a:108: error: ISO C++ forbids declaration of ‘type name’ with no type [-fpermissive]

    strcpy_P( d , (const prog_char *) s );

    Any help?

    • bigjosh2

      What is an LDR? Are you trying to use a Yun? If so, I would strongly encourage you to NOT. This is not a good platform and the above error is just the beginning of the long journey of pain you will be embarking on. Have you looked at the Raspberry Pi or BeagleBone? Both cheaper and vastly better than a Yun. LMK!

      • Phlow

        Thanks for the quick response. LDR is light dependent resistor.
        I actually have used the RasPi but was just trying to explore the Yun based on interrupt functionality and combination of arduino and Linux.
        Thanks for the advice though. If you still can, could you strip the code just for a simple adc? It would be nice even if just for knowledge.
        Thanks again.

          • Phlow

            Hi Josh,

            You must have really had a hard time with the Yun. Actually, wasn’t suggesting you hook up your Yun again, just looking at your examle code for the logger without the relevant debug and DS1820 codes which made the whole thing kind of confusing for me when I tried to relate it to logging from a simple ADC such as an LDR. Just wanted to try my hands on it and be satified it’s a success.
            Thanks.

            Phlow

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