Up and running with the ESP8266

Successfully put ESP8266 into prototype after finalizing the temperature controlled deck lights.  The deck lights use an Arduino UNO with MOSFETs, a dht11 sensor and a ds1307 real time clock (rtc). They turn on and off the lights at predefined times.  I’m hoping to use the Timelord library to turn them off shortly after sunrise and on at sunset.  That’s for another post.

The only thing they are really missing is connectivity.


The ESP8266 solves that with on-board Wi-Fi, and pretty much anything else you could want for ten bucks.  Other people have said it, but this is amazing technology for the money.  With a little persistence and a lot of mistakes, it is infinitely configurable.  I ran out of DHT sensors and had to rely on the sole analog pin of the ESP to start collecting data.

On day 1, it’s already streaming data to thingspeak’s platform every 20 seconds.  I know that’s overkill, but I copied the timing code and didn’t want to recompile and upload before settting it up for the night on the porch.


Here it is after running all night on the FTDI USB TTL adapter (I do not have a high quality 3.3V power supply).


Clipboard01I have a LiPo Battery that I will run it off for the day after adjusting the WiFi Send interval to a more reasonable 1 Hz.  After reading about the Narcoleptic.h library, it seems plausible to get 1 year off a LiPo Cell.

While I like the data manipulation of Thingspeak, I prefer the customizability of Google Sheets for data display especially since I’ve already worked out the bugs of the sheets daily rollover.  While both services are free and both could charge at any moment, I suspect that google will prefer to keep it free while consuming our data.

With google sheets as a preferred method, the next step is to figure out how to POST data to google sheets directly from ESP8266 packets.  From what I can tell, this will involve a google form or an intermediary processor such as python and gspread which I already have running.

Google Sheets Trickery for Logger

There are a number of needs to get the datalogger to serve as a repository.  This is a collection of Google Scripts for reference to help in the development of the Google Sheet side of the project.

Delete redundant spreadsheets

This was linked to graphic inserted on the spreadsheet for ease of access.  This deletes all the sheets in the file except index [0].

function deleteRedundantSheets() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
for (i = 1; i < sheets.length; i++) {

Midnight Data operations

This script copies the spreadsheet to the next index over and renames it to the previous days date. I could have pulled the date from the data on the sheet, but just had javascript do it for me. I know my code is clunky but at least it’s functional.

function midnightRestart() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var first = SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
  var formatToday = Utilities.formatDate(new Date(), "EST", "YYYYMMdd");
// Now that the new sheet has been created, clear the first columns for new incoming data
  var range = first.getRange("A2:D2000");
// rename sheet at index 1 to yesterdays date rather than copy of index 0
  var second = SpreadsheetApp.setActiveSheet(ss.getSheets()[1]);

//Redundant error handling for fault tolerance  
  try {
    var yesterDate = yesterdaysDate();
  catch(e) {
    try {
      second.setName(yesterDate + " Backup");
      appendDate = Utilities.formatDate(new Date(), "EST", "HHmmSS");
      second.setName(yesterDate + " Backup " + appendDate);
function yesterdaysDate() {
  date = subDaysFromDate(new Date(),1)
  var datestring = Utilities.formatDate(date, "EST", "YYYYMMdd");
  return datestring;
function subDaysFromDate(date,d){
  // d = number of day ro substract and date = start date
  var result = new Date(date.getTime()-d*(24*3600*1000));
  return result


This will give a general idea of the layout of the sheet and backup/error handling of the midnight processing.




And here it is with some phony testing data.


Arduino + DHT11 + Serial + PC + Python + Gspread = Logger

I have really wanted to get into the ESP8266 area but am still learning about how to implement so to get some mental excercise, I thought I’d use the native serial capacity of arduino with python’s serial library to upload to a google sheet.

I had originally envisioned maybe a server closet datalogger since the server is on anyway and can process and upload the data at routine intervals.

This project was interesting because it required C implementation for the arduino (which was the easiest part), python to parse the data and upload it to google sheets and the really useful gspread python library.  For me, it was the most moving parts of any project I’ve made thus far.  I’ll expand on the code below, but I wanted to show the early resulting output.  A Gsheet that can be accessed from anywhere to look at local conditions.



Click Image for Realtime Data

Arduino is not for quitters

I was originally using a for loop to collect the data on a per minute basis.  This proved problematic with the midnight data reset so I simply used the old arduino c standby “while True:”  That and tweaks in the gspread authentication have greatly improved consistency on a q10 minute upload schedule with no timeouts.  I have some error handling built into a refactor but have not implemented yet…following “if it ain’t broke” mantra.