Introduction: Weather Data Using Google Sheets and Google Script

About: We are a group of makers. We work in IoT, IOS app, android app, embedded design, sensor design, raspberry pi, arduino, beaglebone, particle electron, particle photon, Bluetooth.

In this Blogtut, we are going to send the readings of the SHT25 sensor to google sheets using Adafruit huzzah ESP8266 which helps to send the data to the internet.

  • Sending data to google sheet cell is much useful and basic way which saves the data in tabular form online.
  • Instead of using any kind of special bridge like pushing a box or MQTT NODE RED or any other kind of REST API, we are going to send the data by using google script which easily receives the data from the sensor by using script Link provided by the google script application after publishing
  • We can use the scripting to send the data from google sheet, docs or even to google drive.
  • Just not end here further you may connect the google sheets with any web-based application to which share the data with users the way they wanted
  • No extra charges to be paid unlike the other online servers here and you can store the data up to 10 GB Maximum in the user account and can use
  • One of the best efforts is you can learn about connecting the data to the internet with this kind of real-time application easily.
  • We are going to use the I2C connection which performs the master-slave protocol to share the data with google sheets in a flexible manner.
  • The I2C protocol platform connects the sensor hardware and works with around 256 sensors at one time by using only 2 wired pull up to transfer the sensor data in 8 bits hardware

Step 1: Hardware

Step 2: How Stuff Works

Fetching the readings of real-time data of sensors through ESP8266 and send the data to different Cloud platforms is much simple.

We are going to use Wire.h library in Arduino IDE for creating the two wired communication between Adafruit Huzzah Board and SHT25 sensor I2C module and I2C cable.

Note: To avoid the complex wiring structure, I am going to use the I2C adapter for Adafruit Huzzah designed to connect the I2C sensor.

For the newbie to set up the Esp8266 yo must go through the ESP8266 setup

First Initialize the libraries:

  • Wire library
  • ESP8266WiFi
  • WiFiClientSecure

Step 3: I2C Module Execution Process in Arduino IDE

After Initializing libraries, we will be defining the I2C process to be used to fetch the sensor readings convert them and shift the 8 bits data as per the requirements:

  • Initialize the registers in two wire I2C protocol for the I2C sensor module
#define Addr 0x40
  • Start I2C transmission and initialize the registers and request for 2 bytes data from where we will read the sensor data.
  • If 2 bytes data will be available then read the sensor data and using mentioned below formulas we are converting the desired values
float humidity = (((data[0] * 256.0 + data[1]) * 125.0) / 65536.0) - 6; 
 float cTemp = (((data[0] * 256.0 + data[1]) * 175.72) / 65536.0) - 46.85; 
float fTemp = (cTemp * 1.8) + 32;
  • Print the values in a serial monitor screen

Step 4: ESP8266 Connection With WiFi and Google Spreadsheet

After the execution I2C modules we are going to learn about how to fetch the data and by using WiFi libraries and host id as well as API keys to send the data to google sheets.

  • Define WiFi credentials globally in ESP8266 which will help us to connect the board with internet
  • As we are going to use the HTTP client and will be defining HTTPS = 443 protocol for securing the HTTP path as the script will work in the secure path only.
  • Initialize the host details in code

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

const int httpsPort = 443; 
String SCRIPT_ID = "mention the script ID as mentioned in snaps";

Note: Script ID is mentioned in “web apps URL” while Gscript code will be published, just copy and paste the id mentioned below and initialize in above commands

  • Also using volatile with variable, we are going to initialize variable globally which fetch the data from the I2C module and send it to the URL script which will further send the data to the destination.
  • Using ESP8266 WiFi Library we will be able to connect the board with internet
  • The sensor data will be hosted to a local server after every 5 seconds.

  • With the help of URL script, the data will be hosted to google script published link Active page after every 15 seconds.

Step 5: Automate the Google Sheet Using GScript Editor

As we all have a google account to sign in to google sheet with your account

  • Mention the values which you need to get from the sensor connected with ESP8266
  • Go to Tools>Script Editor
  • Use “Doget” function to receive the events
  • In “Doget” function to initialize the spreadsheet API key as well as connect the Active Sheet where you want to send the sensor values

  • With the help of automation function to mentioned in the code to represent the data in rows and columns easily.

  • Finally, save the data and click on “Publish”>> Click “Deploy as web apps”

  • Make sure whenever there will be any change to select the “project version” >> “New”>> press “update”

Current web app URL will appear as below :

<p>https://script.google.com/macros/s/”GScript ID”/exec:</p>

further used in ESP8266 code for fetching the data from sensors

Hosting connections
We are going to use the HTTPS Get request to connect the data to the host id mentioned in the gscript editor where we coded our data further to connect with google sheet.

<p>function doGet(e) { <br>    Logger.log( JSON.stringify(e) );  // view parameters
    var result = 'Ok'; // assume success
    if (e.parameter == 'undefined') 
    {
      result = 'No Parameters';
      }
    else {
    var sheet_id = ''; // Spreadsheet ID
    var sheet = SpreadsheetApp.openById(sheet_id).getActiveSheet();
    var newRow = sheet.getLastRow() + 1;                        
    var rowData = [];
     } Logger.log(JSON.stringify(rowData));
    // Write new row below
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    newRange.setValues([rowData]);
    }</p>

Step 6: Limitations:

  • This project is only limited to store the data of I2C sensor in google sheets
  • We are using HTTPS GET request to receive the values through I2C functions
  • We have to change the value in string format and then send the data to gscript URL link.

Step 7: Code, Credits, Reference