Hackster is hosting Hackster Holidays, Ep. 7: Livestream & Giveaway Drawing. Watch previous episodes or stream live on Friday!Stream Hackster Holidays, Ep. 7 on Friday!
Stephen Borsay
Published © GPL3+

Transmit ESP8266 Data to Google Sheets

This project allows you to transmit DHT environmental data from your ESP8266 to your Google Sheet for data logging.

IntermediateFull instructions provided2.5 hours67,986
Transmit ESP8266 Data to Google Sheets

Things used in this project

Hardware components

NodeMCU ESP8266 Breakout Board
NodeMCU ESP8266 Breakout Board
tested with ESP8266 12-E with NodeMCU v1.0
×1
DHT11 Temperature & Humidity Sensor (4 pins)
DHT11 Temperature & Humidity Sensor (4 pins)
×1
Breadboard (generic)
Breadboard (generic)
×1
Jumper wires (generic)
Jumper wires (generic)
×1
USB-A to Micro-USB Cable
USB-A to Micro-USB Cable
×1

Software apps and online services

Arduino IDE
Arduino IDE
Google Sheets
Google Sheets
Pushingbox

Story

Read more

Schematics

wiring

This shows where I wired relative to the Arduino sketch

Code

ESP8266 to Google Sheets

Arduino
Connect the ESP8266 to the DHT11 and upload code
//-----------------------------------------------
//This sketch is combined from Adafruit DHT sensor and tdicola
//for dht.h library
//Along with some  esp8266 NodeMCU specifics from teos009    
// https://learn.adafruit.com/dht/overview
// https://gist.github.com/teos0009/acad7d1e54b97f4b2a88
//other Authors Arduino and associated Google Script:
//Aditya Riska Putra
//Ahmed Reza Rafsanzani
//Ryan Eko Saputro
//See Also
//http://jarkomdityaz.appspot.com/
//
//ELINS UGM
//
//Modified for Hackster.io project to the ESP8266 from
//a Ethernet Sheild project
//by Stephen Borsay
//Since Arduino can't handle https, we need to use Pushingbox API
//(uses http)to run 
//the Google Script (uses https).


#include "ESP8266WiFi.h"
#include "DHT.h"
#define DHTPIN 2    // what digital pin we're connected to
                    //pin2 to D4 on esp board

// Uncomment whatever DHT sensor type you're using!
#define DHTTYPE DHT11  // DHT 11
//#define DHTTYPE DHT21  // DHT 21
//#define DHTTYPE DHT22  // DHT 22

DHT dht(DHTPIN,DHTTYPE);


const char WEBSITE[] = "api.pushingbox.com"; //pushingbox API server
const String devid = "<YOUR_Devid_HERE>"; //device ID from Pushingbox 

const char* MY_SSID = "<YOUR_WIFI_NAME_HERE";
const char* MY_PWD =  "<YOUR_WIFI_PASSWORD_HERE>";


void setup()
{
  Serial.begin(115200);
  dht.begin();
  Serial.print("Connecting to "+*MY_SSID);
  WiFi.begin(MY_SSID, MY_PWD);
  Serial.println("going into wl connect");

  while (WiFi.status() != WL_CONNECTED) //not connected,..waiting to connect
    {
      delay(1000);
      Serial.print(".");
    }
  Serial.println("wl connected");
  Serial.println("");
  Serial.println("Credentials accepted! Connected to wifi\n ");
  Serial.println("");
}


void loop()
{
  
  //Wait between measurements longer then normal to slow donwn
  //google sheet populate, as We dont want to exceed free service quota
  delay(10000); //10 seconds, (sampling rate vs. service call quota)

  float humidityData = dht.readHumidity();
  // Read temperature as Celsius (the default)
  float celData = dht.readTemperature();
  // Read temperature as Fahrenheit (isFahrenheit = true)
  float fehrData = dht.readTemperature(true);

  // Check if any reads failed and exit early (to try again).
  if (isnan(humidityData) || isnan(celData) || isnan(fehrData))
  {
    Serial.println("Failed to read from DHT sensor!");
    return;
  }

  // Compute heat index in Celsius (isFahreheit = false)
  float hicData = dht.computeHeatIndex(celData, humidityData, false);
  // Compute heat index in Fahrenheit (the default)
  float hifData = dht.computeHeatIndex(fehrData, humidityData);

  //Print to Serial monitor or Terminal of your chocice at 115200 Baud
  Serial.print("Humidity: ");
  Serial.print(humidityData);
  Serial.print(" %\t");
  Serial.print("Temperature in Cel: ");
  Serial.print(celData);
  Serial.print(" *C ");
  Serial.print("Temperature in Fehr: ");
  Serial.print(fehrData);
  Serial.print(" *F\t");
  Serial.print("Heat index in Cel: ");
  Serial.print(hicData);
  Serial.print(" *C ");
  Serial.print("Heat index in Fehr: ");
  Serial.print(hifData);
  Serial.print(" *F\n");
    
  WiFiClient client;  //Instantiate WiFi object

    //Start or API service using our WiFi Client through PushingBox
    if (client.connect(WEBSITE, 80))
      { 
         client.print("GET /pushingbox?devid=" + devid
       + "&humidityData=" + (String) humidityData
       + "&celData="      + (String) celData
       + "&fehrData="     + (String) fehrData
       + "&hicData="      + (String) hicData
       + "&hifData="      + (String) hifData
         );

      client.println(" HTTP/1.1"); 
      client.print("Host: ");
      client.println(WEBSITE);
      client.println("User-Agent: ESP8266/1.0");
      client.println("Connection: close");
      client.println();
      }
}

Google App Script

JavaScript
Use in Google sheet Script Editor and Paste your spreadsheet URL into the correct field
//-----------------------------------------------
//Originally published by Mogsdad@Stackoverflow
//Modified for jarkomdityaz.appspot.com
//Modified for Hackster.io by Stephen Borsay
//-----------------------------------------------
/*

GET request query:

https://script.google.com/macros/s/<gscript id>/exec?celData=data_here
----------------------------------------------------------------------

GScript, PushingBox and Arduino/ESP8266 Variables in order:

humidityDat
celData
fehrData
hicData
hifData
----------------------------------------------------
*/


/* Using spreadsheet API */

function doGet(e) { 
  Logger.log( JSON.stringify(e) );  // view parameters

  var result = 'Ok'; // assume success

  if (e.parameter == undefined) {
    result = 'No Parameters';
  }
  else {
    var id = '<YOUR_SPREADSHEET_URL_HERE';//docs.google.com/spreadsheetURL/d
    var sheet = SpreadsheetApp.openById(id).getActiveSheet();
    var newRow = sheet.getLastRow() + 1;
    var rowData = [];
    //var waktu = new Date();
    rowData[0] = new Date(); // Timestamp in column A
    
    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 'humidityData': //Parameter
          rowData[1] = value; //Value in column B
          break;
        case 'celData':
          rowData[2] = value;
          break;
        case 'fehrData':
          rowData[3] = value;
          break;
        case 'hicData':
          rowData[4] = value;
          break;
        case 'hifData':
          rowData[5] = value;
          break;
        default:
          result = "unsupported parameter";
      }
    }
    Logger.log(JSON.stringify(rowData));

    // Write new row below
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    newRange.setValues([rowData]);
  }

  // Return result of operation
  return ContentService.createTextOutput(result);
}

/**
* Remove leading and trailing single or double quotes
*/
function stripQuotes( value ) {
  return value.replace(/^["']|['"]$/g, "");
}

Wireless Projects

Credits

Stephen Borsay
11 projects • 73 followers
Computer Engineer: Embedded Systems and IoT. AWS IoT Hero www.udemy.com/user/stv/ device2cloud.net

Comments

Related channels and tags
  • Espressif
  • Google
  • NodeMCU