electroSome

Updating Sensor Data to Google Spreadsheet using ESP8266 – IoT Project

In IoT applications, the monitored sensor data needs to be recorded. This data logging is important for data analytics. So in this tutorial, we will record the sensor captured data in a Google spreadsheet.

Components Required

Hardware

Circuit Diagram

Updating Sensor Data to Google Spreadsheet using ESP8266 – Circuit Diagram

Software

Google Spreadsheet Creation

function doGet(e) 
{
  var mo = e.parameter.func;
  if(mo == "addData")
  {
    var stat = add_data(e);
    if(stat == 1)
    {
      var result = 
      {
        status : true
      };
      return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
    }
  }
}
function add_data(e)
{
  var sheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/182EnOR4vF5eVs4wGD-zOn7pKy_6BMpED8ApjIGh0C9Q/edit#gid=0');
  var lastVal = sheet.getRange("A1:A").getValues();
  var id = lastVal.filter(String).length;

  var CurrentDate = new Date();
  var Date_ = Utilities.formatDate(CurrentDate, "IST", "dd/MM/YYYY");
  var Time_ = Utilities.formatDate(CurrentDate, "IST", "HH:mm:ss");

  sheet.appendRow([id, Date_, Time_, e.parameter.val]);
  return 1;
}
Updating Sensor Data to Google Spreadsheet using ESP8266 – Google Spreadsheet-Script editor – Deploy as web app

Programming ESP8266

Arduino Code

#include <ESP8266WiFi.h>
#include <WiFiClientSecure.h>

const char *ssid = "WiFi SSID"; 
const char *password = "WiFi PASSWORD";  
const char* host = "script.google.com"; 
String url;

void setup() 
{
  Serial.begin(115200);
  delay(100);
  Serial.println();
  Serial.println();
  Serial.print("Connecting to ");
  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());
  Serial.print("Netmask: ");
  Serial.println(WiFi.subnetMask());
  Serial.print("Gateway: ");
  Serial.println(WiFi.gatewayIP());
}

void loop() 
{
  Serial.print("connecting to ");
  Serial.println(host);
 
  WiFiClientSecure client;

  const int httpPort = 443;
  if (!client.connect(host, httpPort)) 
  {
    Serial.println("connection failed");
    return;
  }

  float t = analogRead(A0);
  
  url = "/macros/s/AKfycbyvGjcryd7c5uNeX6dkleZhmjDKyTrs1L9Lf3kWlPhTZDn9JPrH/exec?func=addData&val="+ String(t);
  Serial.print("Requesting URL: ");
  Serial.println(url);
  
  client.print(String("GET ") + url + " HTTP/1.1\r\n" +
               "Host: " + host + "\r\n" + 
               "Connection: close\r\n\r\n");
  delay(500);
  String section="header";
  while(client.available())
  {
    String line = client.readStringUntil('\r');
    Serial.print(line);
  }
  Serial.println();
  Serial.println("closing connection");
  delay(6000);
}

Working

The ESP8266 reads the sensor data from analog pin A0 and updates that sensor captured data to Google Spreadsheet. So the monitored data will be recorded in a Google Spreadsheet we can use it further for data analytics.

Practical Implementation

Hardware

Updating Sensor Data to Google Spreadsheet using ESP8266 – Practical Implementation

Google Spreadsheet

Updating Sensor Data to Google Spreadsheet using ESP8266 – Google Spreadsheet

Video