Day 3 – Logging the nodeMCU Arduino sensor data to google Drive and having insights analyze it

Its been a great day to day! lots of problems solved…

Target is to log the sensor data form the nodemcu esp8622 / arduino into google drive on a sheet for visualization.

it consisted of 3 steps:

1. creating the google form
2. creating the Arduino Sketch to get the sensor data and send it using wifi to the internet
3. create a php middleware to handle the https connection to google

Create a form in google drive to log the data ur sensors provide, in my case i created humidty and temperature, what is delivered by the DHT11 sensor. heres a link to my form https://docs.google.com/forms/d/1rv-vuoiAbHI8I9nkGXjb3nMB8LFn7-jh20fYDdm9Kcg/,  the nice thing here is that google will create a sheet to store the data, give them a timestamp and provides “insights” to analyze the data. AND you can publish them on the internet, share them…

1. in the form, make sure the answers are text
2. note ur form id 1rv-vuoiAbHI8I9nkGXjb3nMB8LFn7-jh20fYDdm9Kcg
3. open the form page source and look for the field names : in my case its “entry.2067116456” every field has unique number after “entry.”, you’ll need these to fill the form
4. test a GET URL like this to fill the form (replace the form id and the field id with urs), mine looks like this https://docs.google.com/forms/d/1rv-vuoiAbHI8I9nkGXjb3nMB8LFn7-jh20fYDdm9Kcg/formResponse?ifq&entry.997762564=33&entry.2067156456=66 -> here you see that i have changed the field numbers to avoid abuse.

Now you see, its easy to get the nodemcu to call this URL and transmit the variables humidity and temperature to googleforms, google will do the rest. The bigger problem is that google changes the forms every now and then and doesnt support http… and i want to keep node as small as possible…. so i opted for a PHP middleware that gets the http request from the node and forwards it in https to google.

---

 

Heres the php code:

<?php
// create a new cURL resource
$ch = curl_init();

$url = “https://docs.google.com/forms/d/1rv-vuoiAbHI8I9nkGXjb3nMB8LFn7-jh20fYDdm9Kcg/formResponse?ifq&entry.XXX=”;
$url .= $_GET [‘h’];
$url .= “&entry.YYY=”;
$url .= $_GET [‘t’];

// set URL and other appropriate options
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HEADER, 0);

// grab URL and pass it to the browser
curl_exec($ch);

// close cURL resource, and free up system resources
curl_close($ch);
?>

---

 

and the Sketch for the arduino / nodemcu 8266

/*
Programm tp send the data from the sensor DHT11 to a php page and from there to google forms
*/

#include <DHT.h>
#include <ESP8266WiFi.h>

#define CYCLE 600000 //10 minutes, change as desired
#define DHTPIN 2
#define DHTTYPE DHT11 // DHT 11

const char* ssid = “XXX”; // “ur SSID”;
const char* password = “XXX”; //”ur wifi pswd”;
const char* host = “beedata.yazbek.com”;
unsigned long value = 0;

unsigned int lastcall = CYCLE;
int conn_time;

DHT dht(DHTPIN, DHTTYPE);

void setup() {
Serial.begin(9600);
delay(10);

dht.begin();

// We start by connecting to a WiFi network

Serial.println();
Serial.println();
Serial.print(“Connecting to “);
Serial.println(ssid);

WiFi.begin(ssid, password);

while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(“.”);
conn_time++;
if (conn_time > 20) {
break;
}
}
if (WiFi.status() == WL_CONNECTED) {
Serial.println(“”);
Serial.println(“WiFi connected”);
Serial.println(“IP address: “);
Serial.println(WiFi.localIP());
}
else {
Serial.println(“”);
Serial.println(“no WiFi connection”);
ESP.deepSleep(100000);
Serial.println(“gone to sleep”);
}
}

void loop() {
while (millis() – lastcall < CYCLE) {
delay(1);
}
lastcall = millis();
++value;

Serial.print(“connecting to “);
Serial.println(host);

// Use WiFiClient class to create TCP connections
WiFiClient client;
const int httpPort = 80;
if (!client.connect(host, httpPort)) {
Serial.println(“connection failed”);
return;
}

float h = dht.readHumidity();
// Read temperature as Celsius (the default)
float t = dht.readTemperature();

Serial.println(h);
Serial.println(t);

// We now create a URI for the request
String url = “http://beedata.yazbek.com/data/XXX.php?”;
url += “h=”;
url += h;
url += “&t=22”;
url += t;
//url += value;

Serial.print(“Requesting URL: “);
Serial.println(url);

// This will send the request to the server
client.print(String(“GET “) + url + ” HTTP/1.1rn” +
“Host: ” + host + “rn” +
“Connection: closernrn”);
delay(1000);

// Read all the lines of the reply from server and print them to Serial
while (client.available()) {
String line = client.readStringUntil(‘r’);
Serial.print(line);
}

Serial.println();
Serial.println(“closing connection”);
}

---

 

Here you can see the logged data as google prepares it, i have set the sheet to public

https://docs.google.com/spreadsheets/d/1-CJAlEKUblTtkPXD9mYxuZGMb-Wri7wWm1Z8-baxjy4/edit#gid=123450854