IoT with NodeMCU

Published in

Oceanize Lab Geeks

8 min readJan 16, 2020

NOTE: In this article only for those guy who knows about NodeMCU /ESP3286 or Arduino.

Today I will discuss how to get sensor value from temperature sensor (“DHT11 Temperature and Humidity Sensor”) and store sensing value in google sheet

Google sheet is most commonly used in place of Microsoft Excel to develop spreadsheet documents. It provides a good way to store or process data in spreadsheet form and it can be integrated with dozens of other services provided by Google like Maps, to create truly innovative solutions. Through APIs and the use of Google script (Gscript), Google made it easy for developers to programmatically fill in data into a google sheet thus making it easy to build solutions using their services and this is what we will use for this tutorial..

As an example to show the use of Google Sheets as the device cloud, we will build a simple temperature and humidity based IoT weather monitor. The device will obtain temperature and humidity from the environment using DHT11 and upload the data over WiFi to a Google Sheet file.

Ready? let’s jump in.

Required Components

ESP8266 NodeMCU
DHT11 Temperature and Humidity Sensor
LED
Registor (1K)

5. Jumper wires

6. Bread Board

Schematics Circuit Diagram

The pin to pin map of the connection between the NodeMCU and the DHT

NodeMCU - DHT
3.3V - VCC
GND -  GND
A0 - DO

Now We Preparing the Google Sheet

Follow the steps below -

browser to docs.google.com. using your google account or create a new one before you can access the link.
When the page opens, click on the menu button and select the Google sheet from the drop-down of applications.
3. The above will launch a new page where you can choose the option of creating a new sheet.
When the new Spreadsheet opens Give it a name (Document title)In my case, the document title is IoTSenceingTemp while the sheet is name temphum.

5. It will launch a new tab which will open the Google Script Editor. Rename the Google script file to a name you can relate with. For this tutorial, I will be calling the file “temphum_log“.

6. Copy the script content and paste in the editor.

// Created by @Roktim 2020
// All Rights Reserved.
//
// Read/Write to Google Sheets using REST API.
// Can be used with ESP8266 & other embedded IoT devices.
// 
// Use this file with the ESP8266 library HTTPSRedirect
// 
// doGet() and doPost() need the spreadsheet ID. Cannot use "active spreadsheet" here since
// the device can operate without the spreadsheet even being open.
// http://stackoverflow.com/questions/4024271/rest-api-best-practices-where-to-put-parameters
// http://trevorfox.com/2015/03/rest-api-with-google-apps-script// Similar API docs:
// https://gspread.readthedocs.org/en/latest/
// https://smartsheet-platform.github.io/api-docs/#versioning-and-changes
// http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX/lib/Excel/Writer/XLSX.pm// http://forum.espruino.com/conversations/269510/
// http://stackoverflow.com/questions/34691425/difference-between-getvalue-and-getdisplayvalue-on-google-app-script
// http://ramblings.mcpher.com/Home/excelquirks/gooscript/optimize// Things to remember with getValue() object format:
// 1. Partial dates or times-only will be replaced with a full date + time, probably in the
//    year 1989. Like this: Sat Dec 30 1899 08:09:00 GMT-0500 (EST)
// 2. Dollar ($) currency symbol will be absent if cell contains currency.
//    This may be locale-dependent.
// 3. Scientific notation will be replaced by decimal numbers like this: 0.0000055// Script examples
// https://developers.google.com/adwords/scripts/docs/examples/spreadsheetappvar SS = SpreadsheetApp.openById('18EfIQZ9JBbiY9uUx3xlJP2tlGgZTOctYdMiEhdJxcJo');    //Enter Your Sheet ID Got From Sheet URL Link
var sheet = SS.getSheetByName('temphum');      // Enter your sheet name here, In my case it is TempSheet
var str = "";function onOpen(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('ESP8266_Temp_Logger')
  .addItem('Clear', 'Clear')
  .addToUi();
}function Clear(){
  sheet.deleteRows(4, sheet.getLastRow());
  SS.toast('Chart cleared', 'ESP8266_Temp_Logger', 5);
}function doPost(e) {var parsedData;
  var result = {};
  
  try { 
    parsedData = JSON.parse(e.postData.contents);
  } 
  catch(f){
    return ContentService.createTextOutput("Error in parsing request body: " + f.message);
  }
   
  if (parsedData !== undefined){
    // Common items first
    // data format: 0 = display value(literal), 1 = object value
    var flag = parsedData.format;
    
    if (flag === undefined){
      flag = 0;
    }
    
    switch (parsedData.command) {
      case "appendRow":
         var tmp = SS.getSheetByName(parsedData.sheet_name);
         var nextFreeRow = tmp.getLastRow() + 1;
         var dataArr = parsedData.values.split(",");
         
         tmp.appendRow(dataArr);
         
         str = "Success";
         SpreadsheetApp.flush();
         break;     
       
       
    }
    
    return ContentService.createTextOutput(str);
  } // endif (parsedData !== undefined)
  
  else{
    return ContentService.createTextOutput("Error! Request body empty or in incorrect format.");
  }
  
  
}function doGet(e){
  
  var val = e.parameter.value;
  var cal = e.parameter.cal;
  var read = e.parameter.read;
  
  if (cal !== undefined){
    return ContentService.createTextOutput(GetEventsOneWeek());
  }
  
  if (read !== undefined){
    var now = Utilities.formatDate(new Date(), "EST", "yyyy-MM-dd'T'hh:mm a'Z'").slice(11,19);
    //sheet.getRange('D1').setValue(now);
    //var count = (sheet.getRange('C1').getValue()) + 1;
    sheet.getRange('C1').setValue(count);
    return ContentService.createTextOutput(sheet.getRange('A1').getValue());
  }
  
  if (e.parameter.value === undefined)
    return ContentService.createTextOutput("No value passed as argument to script Url.");
    
  var range = sheet.getRange('A1');
  var retval = range.setValue(val).getValue();
  var now = Utilities.formatDate(new Date(), "EST", "yyyy-MM-dd'T'hh:mm a'Z'").slice(11,19);
//  sheet.getRange('B1').setValue(now);
  sheet.getRange('B1').setValue("Humidity");
  //sheet.getRange('C1').setValue('0');
  
  if (retval == e.parameter.value)
    return ContentService.createTextOutput("Successfully wrote: " + e.parameter.value + "\ninto spreadsheet.");
  else
    return ContentService.createTextOutput("Unable to write into spreadsheet.\nCheck authentication and make sure the cursor is not on cell 'A1'." + retval + ' ' + e.parameter.value);
}function GetEventsOneWeek(){
  var Cal = CalendarApp.getCalendarsByName('Test REST API')[0];
  // Need to create 2 separate Date() objects. Cannot do 'OneWeekFromNow = Nowjs' to 
  // simply get it's value and use that later without modifying 'Now'
  // since in JS, an object is automatically passed by reference
  var Now = new Date();
  var OneWeekFromNow = new Date();
  OneWeekFromNow.setDate(Now.getDate() + 7);
  //Logger.log(Now);
  //Logger.log(OneWeekFromNow);
  var events = Cal.getEvents(Now, OneWeekFromNow);
  //Logger.log(events.length);
  var str = '\nEvent Title,\tDescription,\tRecurring?,\tAll-day?,\tFirst Reminder (in minutes before event)\n';
  for (var i = 0; i < events.length; i++){
    str += events[i].getTitle() + ',\t' + events[i].getDescription() + ',\t' + events[i].isRecurringEvent() +  ',\t' + events[i].isAllDayEvent() + ',\t' + events[i].getPopupReminders()[0];
    str += '\n';
  }
  //Logger.log(str);
  return str;
}//https://script.google.com/macros/s/AKfycbxIp9H4WA_GnzcZP6IEojjTxFfgNsTnPUOIYcGAnWa2NHT5xTjp/exec

Edit the sheet name and sheet ID in the code to match with your own sheet name (which is “temphum” in my case ) and your own sheet ID.

7. If Gscript editing complete, click on the publish button and select “deploy as web app”

With this done you should now see the success page. It will provide you with the web app URL. Copy this URL and keep safe as the script ID embedded in the web app URL will be used by the Arduino code to access the google sheet.

With this done, we are now ready to write the code for our ESP8266Now We Preparing the Google Sheet

Now our google sheet is preparing so we move to next step to wright NodeMCU Programming

CODE

#include <ESP8266WiFi.h>
#include "HTTPSRedirect.h"
#include "DebugMacros.h"
#include <DHT.h>#define DHTPIN D4                                                           // what digital pin we're connected to
#define DHTTYPE DHT11                                                       // select dht type as DHT 11 or DHT22
DHT dht(DHTPIN, DHTTYPE);float h;
float t;
String sheetHumid = "";
String sheetTemp = "";const char* ssid = " ";                //Put wifi ssid within the quotes
const char* password = " ";         //Put WiFi password within the quotesconst char* host = "script.google.com";
const char *GScriptId = "enter your GSCript ID here"; // Replace with your own google script id
const int httpsPort = 443; //the https port is same// echo | openssl s_client -connect script.google.com:443 |& openssl x509 -fingerprint -noout
const char* fingerprint = "";//const uint8_t fingerprint[20] = {};String url = String("/macros/s/") + GScriptId + "/exec?value=Temperature";  // Write Teperature to Google Spreadsheet at cell A1
// Fetch Google Calendar events for 1 week ahead
String url2 = String("/macros/s/") + GScriptId + "/exec?cal";  // Write to Cell A continuosly//replace with sheet name not with spreadsheet file name taken from google
String payload_base =  "{\"command\": \"appendRow\", \
                    \"sheet_name\": \"temphum\", \
                       \"values\": ";
String payload = "";HTTPSRedirect* client = nullptr;// used to store the values of free stack and heap before the HTTPSRedirect object is instantiated
// so that they can be written to Google sheets upon instantiationvoid setup() {
  delay(1000);
  Serial.begin(115200);
  dht.begin();     //initialise DHT11Serial.println();
  Serial.print("Connecting to wifi: ");
  Serial.println(ssid);
  
  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());// Use HTTPSRedirect class to create a new TLS connection
  client = new HTTPSRedirect(httpsPort);
  client->setInsecure();
  client->setPrintResponseBody(true);
  client->setContentTypeHeader("application/json");
  Serial.print("Connecting to ");
  Serial.println(host);          //try to connect with "script.google.com"// Try to connect for a maximum of 5 times then exit
  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...");
  }if (!flag) {
    Serial.print("Could not connect to server: ");
    Serial.println(host);
    Serial.println("Exiting...");
    return;
  }
// Finish setup() function in 1s since it will fire watchdog timer and will reset the chip.
//So avoid too many requests in setup()Serial.println("\nWrite into cell 'A1'");
  Serial.println("------>");
  // fetch spreadsheet data
  client->GET(url, host);
  
  Serial.println("\nGET: Fetch Google Calendar Data:");
  Serial.println("------>");
  // fetch spreadsheet data
  client->GET(url2, host);Serial.println("\nStart Sending Sensor Data to Google Spreadsheet");// delete HTTPSRedirect object
  delete client;
  client = nullptr;
}void loop() {h = dht.readHumidity();                                              // Reading temperature or humidity takes about 250 milliseconds!
  t = dht.readTemperature();                                           // Read temperature as Celsius (the default)
  if (isnan(h) || isnan(t)) {                                                // Check if any reads failed and exit early (to try again).
    Serial.println(F("Failed to read from DHT sensor!"));
    return;
  }
  Serial.print("Humidity: ");  Serial.print(h);
  sheetHumid = String(h) + String("%");                                         //convert integer humidity to string humidity
  Serial.print("%  Temperature: ");  Serial.print(t);  Serial.println("Â°C ");
  sheetTemp = String(t) + String("Â°C");static int error_count = 0;
  static int connect_count = 0;
  const unsigned int MAX_CONNECT = 20;
  static bool flag = false;payload = payload_base + "\"" + sheetTemp + "," + sheetHumid + "\"}";if (!flag) {
    client = new HTTPSRedirect(httpsPort);
    client->setInsecure();
    flag = true;
    client->setPrintResponseBody(true);
    client->setContentTypeHeader("application/json");
  }if (client != nullptr) {
    if (!client->connected()) {
      client->connect(host, httpsPort);
      client->POST(url2, host, payload, false);
      Serial.print("Sent : ");  Serial.println("Temp and Humid");
    }
  }
  else {
    DPRINTLN("Error creating client object!");
    error_count = 5;
  }if (connect_count > MAX_CONNECT) {
    connect_count = 0;
    flag = false;
    delete client;
    return;
  }Serial.println("POST or SEND Sensor data to Google Spreadsheet:");
  if (client->POST(url2, host, payload)) {
    ;
  }
  else {
    ++error_count;
    DPRINT("Error-count while connecting: ");
    DPRINTLN(error_count);
  }if (error_count > 3) {
    Serial.println("Halting processor...");
    delete client;
    client = nullptr;
    Serial.printf("Final free heap: %u\n", ESP.getFreeHeap());
    Serial.printf("Final stack: %u\n", ESP.getFreeContStack());
    Serial.flush();
    ESP.deepSleep(0);
  }
  
  delay(2000);    // keep delay of minimum 2 seconds as dht allow reading after 2 seconds interval and also for google sheet
}

DEMO

Connect the setup to your computer, launch the Arduino IDE, paste the code, verify and upload to your board. With the upload done, open the serial monitor to ensure the WiFi is connected and there are no issues. Head to the Google sheet, you should now see temperature and humidity data as they stream to the cloud from your device.