Temperature Logging to a Google Spreadsheet with an Arduino Yun

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.

If you are like me, you often find yourself in a situation where you need to log multiple channels of temperature data. From testing nano-insulating paint to debugging an overheating geothermal well, most of us will have need of accurate and frequent temperature logs at some point in our short and brutish lives.

Here is a recipe to make a reliable, cheap, and easy cloud-based, multi-channel temperature logger using an Arduino Yun and DS18B20 temperature sensors. I chose the Yun because everyone loves Arduinos and this one can connect to the internets. I choose the DS18B20 sensors because they are awesome and cheap and accurate and you can hook up lots of them to a single pair of wires.

We will be using a Google Spreadsheet to store our logged temperature data for a wide array of reasons detailed in my previous previous article.

Quick Start

  1. Get a Yun and plug it into power and the internet. I use the Ethernet port because it is easy, but it should work fine if you connect over Wifi.
  2. Connect a DS18B20 sensor (or lots of them!). The middle pinis data and goes to to pin11 on the Yun and the other two pins (power and ground) can go to any of the pins labeled “GND” on the Arduino. (You don’t even need a pull-up resistor for just a couple of sensors!)
  3. Install the Dallas Temp Library and 1-Wire library into your Arduino IDE if you don’t already have them.
  4. Load up the SimpleTempLoggerYun code into your Yun using the Arduino IDE.
  5. Check the example sheet here and you should see your data show up a few seconds after the Yun boots!
  6. When you are ready, go ahead and make your own exclusive logging spreadsheet and then copy/paste the URL into the SimpleTempLoggerYun code (near the top where it says GOOGLE_URL) and download that new code into your Yun.
  7. If you are happy and plan on using you new logger for a while, you will need to fix this OpenWRT bug on your Yun.

Done! Now make some cool live graphs and share them with your friends.


There are some very nice things about using DS18B20 sensors.

  • They are very cheap. I got 10 waterproof sensors with wire harnesses for $2 each including postage. How is that possible? Loose parts are as cheap as $1.50.
  • They only take 2 wires to connect – that’s only a single digital pin.
  • You can hook them up in pretty much any configuration you need.
  • They are amazingly responsive. I can tell within 5 seconds when someone opens the door to my roof because of the detected temperature change.

Each sensor has a data line, a power line, and a ground line. All you care about is knowing which one is data – you will connect the other two together anyway.

Here is what the loose part looks like….


The middle pin is the data. I used to think that soldering wire directly to the pins was the fastest and easiest way to get these connected…


…but after doing a lot of them this way, I finally figured out that is is much easier to use a board….


AdaFruit is nice enough to send me free boards every time I order something, so I cut these boards up into little pieces to make DS18B20 carriers. It seems like more work, but it is so much easier in the end.

Note that in the first photo and I using crappy Radio Shack speaker cable, but in the second I am using a single pair of a CAT5 cable here. Another example of live and learn. Speaker wire works if you only have a couple sensors or a couple meters of cable, but CAT5 works with dozens of sensors and dozens of meters of cable. Here is an experiment that used a string of 12 sensors connected to about 20 meters of CAT5 wire and it worked great….


Here is what the waterproof packaging looks like…


At first I was worried that the extra metal on the end would raise the thermal mass and slow down the response to temperature changes, but these turned out to still be plenty fast and can easily register the heat of you touching one with a finger in 1 second.

The water proof ones are very handy because they already attached wire. On the ones I get from Amazon, the data pin is the white wire. On the AdaFruit version, the data wire is yellow.

I typically solder a bunch of them to a board fragment in a star configuration like this…


I recently saw this terminal block on Amazon that could make hookup solder-free.

I’ve also learned that you really should connect the power pin to ground if are going to use only two wires (parasite power mode). They will work if you leave the power pin dangling, but not as reliably probably because the unconnected power pin picks up noise.  For me, this manifested itself in occasional readings of 185C, which indicates that the sensor reset between the time you told it to take a sample and when you tried to read that sample over the bus. If you are getting 185C readings, maybe check that your power pins are solidly grounded.

Note that the wire used on the waterproof sensors is not twisted pair and so you will be limited in how much wire and how many sensors you can hook up before it starts getting flaky. I’ve found that I can reliably use about 2 waterproof sensors without a pull-up resistor, and about 6 with one- especially if the wires from the sensor are connected right near the board so there are not extra runs.


Problems along the Way

It should not be hard to hook up a Yun as a Cloud data logger – that’s what it is built for, right?

But it was hard. Too hard.

Problem #1: Yun looses internet connection after working for 12 hours.

Looking at the logs, curl is failing with “Failed to connect: network unreachable” message. Netstat -r shows the default gateway is just gone.

This turns out to be an outright bug in the scripts on the Yun that make it delete the default gateway anytime it does a DHCP refresh, which happens for most networks every 12 or 24 hours.

You can read about the bug here and how to fix it here (thanks Mitu!)…


Moral: Fix that script on any Yun you plan to leave connected to the internet.

Problem #2:  Logger works perfectly on my desk while I am watching it, but fails after I leave.

I can sit there and watch it all day and it keeps on chugging perfectly.

Alas, anytime I leave it alone for more than about 30 minutes, it starts dumping garbage data into the spreadsheet. In an attempt to catch the glitch in the act, I went for a walk but kept watching the spreadsheet for garbage data. As soon as I saw the first bad row, I rushed back to my desk and watched for the next line of garbage data to go out- but the next line was fine. No matter what I did the data was good if I was sitting there watching it and would turn bad about 30 minutes after I left. It felt like a prank, but there was a simple explanation…

I often sprinkle Serial.println()  debug messages throughout my programs to see what is going on inside the Arduino.

It turns out that if you have an Arduino Yun plugged into your computer and your computer falls asleep, Serial.println() will block for several seconds- presumably because of some flow control going on.

Every time I got up from my desk for more than 30 minutes, the PC went to sleep and the Serial.println() statements started blocking, which cause the reads from the temperature sensors to time out and give garbage data.

Moral: If you use the Yun, make sure you take out all your Serial.print() lines if you plan on having the device run autonomously.

Problem #3: Trying to run CURL over the Yun Bridge just doesn’t work and randomly crashes the Arduino side.

It turns out that the Bridge.Process code uses C++ strings which are dynamically allocated. Since I was sending long command lines that were built up from multiple arguments, these strings would quickly use up all available memory (not much on an Arduino) and then silently crash.

To get around this, I had to throw out the Bridge entirely and send my commands directly tot the Linino side using the serial console. This works greats and is fast and efficiently and reliability uses a static buffer for memory.  The only tricky part is making sure you don’t send any data into the console while the Linino side is booting because there is a “push any key to enter uboot” in there and if you do press any key then you will be stuck in uboot. You avoid this by always waiting for at least 1 second of dead air coming from the console before ever sending data in.

Moral: Don’t use the Yun Bridge package for long commands.


Anyway, the real take away here is that the Yun is definitely not worth the hassle for me. These kinds of problems are needless time-sinks and kill any time saved by using the Arduino in the first place. Too bad.

Leave a Reply