In recent months I’ve been using Cayenne for many of my IoT projects, allowing me to view sensors and controllers remotely. It’s a great platform, and very easy for the user with limited programming skills.
Among many other things, Cayenne allows you to view logged data over time, displaying graphs for defined periods. It does it very well but you’re restricted to the graphs they supply and the very limited data you can download. What if you want to store all the logged data privately to create your own graphs from a detailed record of every single entry?
Logging to Google Sheets
I recently came across this instruction guide that very elegantly shows how to log data straight to a Google Sheet. It works perfectly and I’ve been logging data every 15 minutes for nearly a month. (Yes, these are real data; I live in a warm climate.)
I won’t explain here how it’s done because the original writer deserves your view on his page, not mine. That said, I’ll describe how to utilise the Google Sheets logger with an ESP8266 and DS18B20 temperature sensor.
Using the ESP’s deepsleep function, your battery could last for months, maybe even years.
Advantages of Deepsleep
As the function's name implies, activating deepsleep in the ESP effectively turns it off. Except for a trickle of energy to keep it alive, everything else is dormant. Deepsleep defines when things wake up. When it does, it turns everything on, does its job, then goes back to sleep. Waking up, reading the temperature, logging on to WiFi and sending data is done in a second or two. Hardly any energy is used and the battery can last for ages. This allows you to locate your sensor in the garden, or shed, or anywhere else you may not have electricity - as long is you're within WiFi range.
(The data on the left are genuine. When I was testing the unit, I deliberately left the sensor outside in the sun.)
Getting to Deepsleep
To get to deepsleep on an ESP, pin 16 must be connected to RST. That's why I'm using the ESP8266-12. It can be done on an ESP-01 but you need the eyes of a hawk and the arm of an Olympian marksman to solder the wire. Here's an example of someone who did it.
The DS18B20 and the Wiring
The type of people reading this blog don't need explanations of the DS18B20, or how to read it in an Arduino sketch. That said, I would point out that if you intend to use your sensor outside, get the waterproof version.
I doubt that you need the wiring diagram either, but just in case...
(Of course, the ESP requires 3.3v, but this was the nearest battery I could find in fritzing).
Powering it up
I'm using a 18650 recovered from an old laptop with a KB6206 voltage convertor that's soldered to the bottom of the ESP board. Purists will tell me I need a higher voltage than the 18650 to take account of dropout, but things are working just fine.
The Sketch
Be aware that you'll need to install the HTTPSRedirect library in your Arduino. You'll also need to follow the embedded-lab guide very closely to get all the necessary Google keys and codes.
Once you've done all that, here's the sketch to make it all work with the DS18B20 and Deepsleep.
// BY: Akshaya Niraula (Google Sheets)
// AT: http://www.embedded-lab.com/
// BY: Mike Diamond (DS18B20 and ESP Deepsleep)
// AT: www.whatimade.today
#include <ESP8266WiFi.h>
#include "HTTPSRedirect.h"
#include <OneWire.h>
#include <DallasTemperature.h>
#define ONE_WIRE_BUS 2 // Data wire is plugged into port 2 on the Arduino
OneWire oneWire(ONE_WIRE_BUS); // Setup a oneWire instance to communicate with any OneWire devices
DallasTemperature sensors(&oneWire); // Pass oneWire reference to Dallas Temperature.
const char* ssid = "your_ssid";
const char* password = "your_password";
// The ID below comes from Google Sheets. See how to get it here: http://embedded-lab.com/blog/post-data-google-sheets-using-esp8266
const char *GScriptId = "XXXXXXXXXXXXXXXXXXXXXXXX";
// Push data at this interval
const int deepSleep = 900000000; // 15 minutes in microseconds. You can change this to anything you want.
const char* host = "script.google.com";
const char* googleRedirHost = "script.googleusercontent.com";
const int httpsPort = 443;
HTTPSRedirect client(httpsPort);
// Prepare the url (without the varying data)
String url = String("/macros/s/") + GScriptId + "/exec?";
const char* fingerprint = "F0 5C 74 77 3F 6B 25 D7 3B 66 4D 43 2F 7E BC 5B E9 28 86 AD";
void setup(void)
{
sensors.begin();
}
void setup() {
Serial.begin(115200);
Serial.println("Connecting to wifi: ");
Serial.println(ssid);
Serial.flush();
WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println(" IP address: ");
Serial.println(WiFi.localIP());
Serial.print(String("Connecting to "));
Serial.println(host);
bool flag = false;
for (int i=0; i<5; i++){
int retval = client.connect(host, httpsPort);
if (retval == 1) {
flag = true;
break;
}
else
Serial.println("Connection failed. Retrying...");
}
// Connection Status, 1 = Connected, 0 is not.
Serial.println("Connection Status: " + String(client.connected()));
Serial.flush();
if (!flag){
Serial.print("Could not connect to server: ");
Serial.println(host);
Serial.println("Exiting...");
Serial.flush();
return;
}
// Data will still be pushed even certification don't match.
if (client.verify(fingerprint, host)) {
Serial.println("Certificate match.");
} else {
Serial.println("Certificate mis-match");
}
}
// This is the main method where data gets pushed to the Google sheet
void postData(String tag, float value){
if (!client.connected()){
Serial.println("Connecting to client again...");
client.connect(host, httpsPort);
}
String urlFinal = url + "tag=" + tag + "&value=" + String(value);
client.printRedir(urlFinal, host, googleRedirHost);
}
// Continue pushing data at a given interval
void loop() {
//Read the temperature from DS18B20
sensors.requestTemperatures();
float h = (sensors.getTempCByIndex(0));
float t = (DallasTemperature::toFahrenheit(h));
// Post this information to Google Sheet
postData("Temperature", h);
ESP.deepSleep(deepSleep); // Go back to sleep
//delay (deepSleep);
}
Addendum
After reading the temperature at 15-minute intervals for three months, I thought my Google Sheet must be using up quite a few megabytes of my Google allowance. I went looking to find how large the file had become and discovered something I didn't know - despite being an early adopter and avid user of Google Docs.
It turns out that Google Sheets are "free" and don't count towards your Google allowance. (I didn't check other Google Docs). This means you can log data to a Google Sheet as long as you want, never having to worry about its size.
Not sure what Google gets out of this... I can't see how the temperature of my garden affects their advertising revenues...
Addendum 2
My sensor has been running for nearly a year now. Some months ago I attached a cheap solar panel to it that manages to charge the battery sufficient to keep the ESP going through the night. I haven't touched it for about six months and it diligently updates to Google Sheets every 15 minutes.
Addendum 3 - December 2018
It's now nearly a year since this project was installed. My Wemos D1 Mini is connected to a small charging board - which itself is connected to an 18650 and a solar panel.
The panel charges the 18650 during daylight and the battery keeps the Wemos running overnight. In the year it has been running, it has taken the temperature about 17,500 times. (Every 30 minutes). In that time, it has crashed about 6-8 times altogether, requiring a reset of the Wemos. I'm not sure why this happens but I'm fairly satisfied with its performance.