EXP 9. PUSHING THE ESP 8266 DATA INTO GOOGLE SPREAD SHEET

 

Program

#include <ESP8266WiFi.h>

#include <WiFiClientSecure.h>

//----------------------------------------

//----------------------------------------Include the DHT Library

#include "DHT.h"

//----------------------------------------

#define DHTTYPE DHT11 //--> Defines the type of DHT sensor used (DHT11, DHT21, and DHT22), in this project the sensor used is DHT11.

const int DHTPin = 5; //--> The pin used for the DHT11 sensor is Pin D1 = GPIO5

DHT dht(DHTPin, DHTTYPE); //--> Initialize DHT sensor, DHT dht(Pin_used, Type_of_DHT_Sensor);

#define ON_Board_LED 2  //--> Defining an On Board LED, used for indicators when the process of connecting to a wifi router

//----------------------------------------SSID and Password of your WiFi router.

const char* ssid = "rkk"; //--> Your wifi name or SSID.

const char* password = "rkk@1234"; //--> Your wifi password.

//----------------------------------------

//----------------------------------------Host & httpsPort

const char* host = "script.google.com";

const int httpsPort = 443;

//----------------------------------------

WiFiClientSecure client; //--> Create a WiFiClientSecure object.

String GAS_ID = "AKfycbwHyCamO34TCYGdx8SNIU7x4qf3wAODbrxcXCp75PnHA4kivHoFxvcbGgr1rxFZyPiX"; //--> spreadsheet script ID

//============================================================================== void setup

void setup() {

  // put your setup code here, to run once:

  Serial.begin(115200);

  delay(500);

  dht.begin();  //--> Start reading DHT11 sensors

  delay(500);

  

  WiFi.begin(ssid, password); //--> Connect to your WiFi router

  Serial.println("");

    

  pinMode(ON_Board_LED,OUTPUT); //--> On Board LED port Direction output

  digitalWrite(ON_Board_LED, HIGH); //--> Turn off Led On Board

  //----------------------------------------Wait for connection

  Serial.print("Connecting");

  while (WiFi.status() != WL_CONNECTED) {

    Serial.print(".");

    //----------------------------------------Make the On Board Flashing LED on the process of connecting to the wifi router.

    digitalWrite(ON_Board_LED, LOW);

    delay(250);

    digitalWrite(ON_Board_LED, HIGH);

    delay(250);

    //----------------------------------------

  }

  //----------------------------------------

  digitalWrite(ON_Board_LED, HIGH); //--> Turn off the On Board LED when it is connected to the wifi router.

  //----------------------------------------If successfully connected to the wifi router, the IP Address that will be visited is displayed in the serial monitor

  Serial.println("");

  Serial.print("Successfully connected to : ");

  Serial.println(ssid);

  Serial.print("IP address: ");

  Serial.println(WiFi.localIP());

  Serial.println();

  //----------------------------------------

  client.setInsecure();

}

//==============================================================================

//============================================================================== void loop

void loop() {

  // Reading temperature or humidity takes about 250 milliseconds!

  // Sensor readings may also be up to 2 seconds 'old' (its a very slow sensor)

  int h = dht.readHumidity();

  // Read temperature as Celsius (the default)

  float t = dht.readTemperature();

  

  // Check if any reads failed and exit early (to try again).

  if (isnan(h) || isnan(t)) {

    Serial.println("Failed to read from DHT sensor !");

    delay(500);

    return;

  }

  String Temp = "Temperature : " + String(t) + " °C";

  String Humi = "Humidity : " + String(h) + " %";

  Serial.println(Temp);

  Serial.println(Humi);

  

  sendData(t, h); //--> Calls the sendData Subroutine

}

//==============================================================================

//============================================================================== void sendData

// Subroutine for sending data to Google Sheets

void sendData(float tem, int hum) {

  Serial.println("==========");

  Serial.print("connecting to ");

  Serial.println(host);

  

  //----------------------------------------Connect to Google host

  if (!client.connect(host, httpsPort)) {

    Serial.println("connection failed");

    return;

  }

  //----------------------------------------

  //----------------------------------------Processing data and sending data

  String string_temperature =  String(tem);

  // String string_temperature =  String(tem, DEC); 

  String string_humidity =  String(hum, DEC); 

  String url = "/macros/s/" + GAS_ID + "/exec?temperature=" + string_temperature + "&humidity=" + string_humidity;

  Serial.print("requesting URL: ");

  Serial.println(url);

  client.print(String("GET ") + url + " HTTP/1.1\r\n" +

         "Host: " + host + "\r\n" +

         "User-Agent: BuildFailureDetectorESP8266\r\n" +

         "Connection: close\r\n\r\n");

  Serial.println("request sent");

  //----------------------------------------

  //----------------------------------------Checking whether the data was sent successfully or not

  while (client.connected()) {

    String line = client.readStringUntil('\n');

    if (line == "\r") {

      Serial.println("headers received");

      break;

    }

  }

  String line = client.readStringUntil('\n');

  if (line.startsWith("{\"state\":\"success\"")) {

    Serial.println("esp8266/Arduino CI successfull!");

  } else {

    Serial.println("esp8266/Arduino CI has failed");

  }

  Serial.print("reply was : ");

  Serial.println(line);

  Serial.println("closing connection");

  Serial.println("==========");

  Serial.println();

  //----------------------------------------

} 

//==============================================================================

Script

function doGet(e) { 

  Logger.log( JSON.stringify(e) );

  var result = 'Ok';

  if (e.parameter == 'undefined') {

    result = 'No Parameters';

  }

  else {

    var sheet_id = '1fN1cT0CYQhljYGHLL7UmM4uHhBW6stWhn2WxKR8BUp8';  // Spreadsheet ID

    var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet();

    var newRow = sheet.getLastRow() + 1;            

    var rowData = [];

    var Curr_Date = new Date();

    rowData[0] = Curr_Date; // Date in column A

    var Curr_Time = Utilities.formatDate(Curr_Date, "Asia/Jakarta", 'HH:mm:ss');

    rowData[1] = Curr_Time; // Time in column B

    for (var param in e.parameter) {

      Logger.log('In for loop, param=' + param);

      var value = stripQuotes(e.parameter[param]);

      Logger.log(param + ':' + e.parameter[param]);

      switch (param) {

        case 'temperature':

          rowData[2] = value; // Temperature in column C

          result = 'Temperature Written on column C'; 

          break;

        case 'humidity':

          rowData[3] = value; // Humidity in column D

          result += ' ,Humidity Written on column D'; 

          break;  

        default:

          result = "unsupported parameter";

      }

    }

    Logger.log(JSON.stringify(rowData));

    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);

    newRange.setValues([rowData]);

  }

  return ContentService.createTextOutput(result);

}

function stripQuotes( value ) {

  return value.replace(/^["']|['"]$/g, "");

}

OUTPUT AT MONITOR

Temperature : 25.30 °C Humidity : 48 % ========== connecting to script.google.com requesting URL: /macros/s/1fN1cT0CYQhljYGHLL7UmM4uHhBW6stWhn2WxKR8BUp8/exec?temperature=25.30&humidity=48 request sent headers received esp8266/Arduino CI has failed reply was : bee closing connection