Summary:

Use an ESP8266 to send data to Google Sheets, creating a cheap and extensible open platform for data collection. Based on an example by embedded labs.

Materials:

materials-4

  1. ESP8266 Development Board
  2. Analog or Digital Sensor
  3. USB-Micro Cable
  4. Arduino IDE
  5. Google account
  6. IFTTT Account

Process:

First, we need to create a place to save our data, and write a script to handle Google API calls and save that data. Then we need to network the ESP8266 chip to WiFi. At a specified interval the ESP8266 needs to poll for sensor data, and convert it to a useable form. Finally the ESP8266 needs to send the data to an appropriate Google API.

Connections:

Connect the USB-Micro cable to the ESP8266 and a computer. Attach your sensor of choice as direct. In my case I need to provide 5V, GND, and an analog pin. On the ESP8266 the only analog pin is A0.

connections

Libraries:

Follow your guide of choice for installing the Arduino ESP8266 board.

Creating a Place to Store Data:

Log into sheets.google.com and create a new Blank Sheet. Give the document and the sheet name, and save it.

newsheet

To reference the sheet we'll need the document URL and document ID. The document ID can be found in the URL, which has the format:

https://docs.google.com/spreadsheets/d/{document_id}/edit

Handling Web Requests:

From Google Sheets we'll need to make a sheet script (Tools > Script editor).

First we need to create a request handler that gets called by URL. We need to strip some parameters off the url for logging, such as a tag for the data and a value to store.

function doGet(e){
    try {
        // Safety values
        if (e == null){
            e={};
            e.parameters = {tag:"tag",value:"-1"};
        }
        
        var tag = e.parameters.tag;
        var value = e.parameters.value;
        
        // save the data to spreadsheet
        save_data(tag, value);
        
    } catch(error) { 
        Logger.log(error);    
    }  
}

Make sure to set this function the default called by our script.

func

We'll also create a function to save a data tag and value to our spreadsheet. Let's save the time and a data ID for logging and analytics. Make sure to replace the ss URL and sheet name.

// Method to save given data to a sheet
function save_data(tag, value){
    try {
        var dateTime = new Date();
        
        var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/{document_id}/edit");
        var dataLoggerSheet = ss.getSheetByName("MySheetName");
        
        
        // Get last edited row from DataLogger sheet
        var row = dataLoggerSheet.getLastRow() + 1;
        
        // ID, DateTime, Tag, Value
        dataLoggerSheet.getRange("A" + row).setValue(row -1); // ID
        dataLoggerSheet.getRange("B" + row).setValue(dateTime); // dateTime
        dataLoggerSheet.getRange("C" + row).setValue(tag); // tag
        dataLoggerSheet.getRange("D" + row).setValue(value); // value
    }
 
    catch(error) {
        Logger.log(JSON.stringify(error));
    }
}

To make this accessible we'll need to publish this script as a web app (Publish > Deploy as web app...). Make sure to set the app permissions to "anyone, even anonymous". Then save the app url.

publish_script

We can make requests to this endpoint by issuing a GET request to https://script.google.com/macros/s/{script_id}/exec?tag={tag_name}&value={value}

ESP8266 WiFi Setup:

First we define a function and some constants to connect to WiFi for us.

#include <ESP8266WiFi.h>

// WiFi Credentials
const char * WIFI_SSID = "MySSID";
const char *  WIFI_PASS = "myPASS";

int wifiConnect() {
    Serial.print("Connecting to ");
    Serial.println(WIFI_SSID);
    
    WiFi.begin(WIFI_SSID, WIFI_PASS);
    
    // WiFi fix: https://github.com/esp8266/Arduino/issues/2186
    WiFi.persistent(false);
    WiFi.mode(WIFI_OFF);
    WiFi.mode(WIFI_STA);
    WiFi.begin(WIFI_SSID, WIFI_PASS);
    
    unsigned long wifiConnectStart = millis();
    
    while (WiFi.status() != WL_CONNECTED) {
        // Check to see if
        if (WiFi.status() == WL_CONNECT_FAILED) {
            Serial.println("Failed to connect to WiFi. Please verify credentials: ");
            delay(10000);
        }
        
        delay(500);
        Serial.println("...");
        // Only try for 5 seconds.
        if (millis() - wifiConnectStart > 15000) {
            Serial.println("Failed to connect to WiFi");
            return 0;
        }
    }
    
    Serial.println("");
    Serial.println("WiFi connected");
    Serial.println("IP address: ");
    Serial.println(WiFi.localIP());
    return 1;
}

Then, later we can simply run all Wi-Fi dependent code inside
if(wifiConnect()){ } blocks.

Reading a Sensor:

This part is really case-dependent. Follow the documentation for your sensor to read data and calibrate the results.

For my moisture sensor the API defines the correct value to be 1/10 the value of the analog pin connected to the sensor.

int getSoilMoistureLevel(int pin) {
    int value = analogRead(pin)/10;
    return min(100, max(value,0));
}

HTTPS Request (& Hack):

Now that all the data is ready to go we have to make an HTTPS GET to our endpoint. Since HTTPS on the ESP8266 is a royal pain, we can create an IFTTT convenience script that pipes two webhooks together. We just need to POST json containing {"value1":"tag_name","value2":"sensor_value"} to an IFTTT webhooks endpoint, and have that endpoint call our Google script at https://script.google.com/macros/s/{script_id}/exec?tag= {{Value1}}&value= {{Value2}}

#include <ESP8266HTTPClient.h>

String IFTTT_URL = "http://maker.ifttt.com/trigger/googleSheets/with/key/{ifttt_api_key}";

int httpJSONPOST(String url, String json) {
    if (WiFi.status() == WL_CONNECTED) {
        HTTPClient http;

        Serial.print("Connecting to ");
        Serial.println(url);
        http.begin(url);
        
        http.addHeader("Content-Type", "application/json");

        Serial.print("Posting ");
        Serial.println(json);
        int httpCode = http.POST(json);
        String payload = http.getString();  
        http.end();

        Serial.println(payload); 
   
        return (httpCode == HTTP_CODE_OK) ? 0 : httpCode;
    } else {
        return -1;
    }
}

Putting it together:

Finally, our code just needs to call our setup functions, create a nice JSON object, send it, and deep sleep as long as we want. In our case 1 hour.

 
void setup() {
    Serial.begin(115200);
    Serial.setTimeout(2000);

    Serial.println("[-] Connecting to WiFi");
    if (wifiConnect()) {
        
        Serial.println("[-] Reading soil moisture");
        int soilMoistureLevel = getSoilMoistureLevel(SOIL_PIN);
        Serial.print("Moisture level: ");
        Serial.print(soilMoistureLevel);
        Serial.println("%");

        String json = "{\"value1\":\""+String(TAG_NAME)+"\",\"value2\":\""+String(soilMoistureLevel)+"\"}";

        Serial.print("[-] Reporting: ");
        Serial.println(json);

        int retCode = httpJSONPOST(IFTTT_URL, json);
        if (retCode) {
            Serial.print("Reporting failed: Code ");
            Serial.println(retCode);
        }
    }
    
    Serial.println("[-] Sleeping");
    ESP.deepSleep(36e8); // 3600 sec / 1 hr
}

void loop() {
    // Stub - sleep voids this
}

Code:

Complete code examples can be found here.

Future Work:

Hopefully this will augment my burgeoning attempts at automated gardening.