How to Post Data To Google Spread Sheet to make analysis

In this tutorial, I will show you how to make ESP8266 data logging to IOT cloud. This data logging system is vast and can be applied in Research Centres, Scientific Labs, Operation Theatres, Patient monitoring system and many more. Here I used LCD display to display the data in a monitor also you can visualize and can analize the data through your IOT Cloud.

Components Required

When you are going to buy those components using those link. I could receive small amount of payment.

Cicuit for LCD display

You can make Bussiness queries by using the business queries section. You can find that at the bottom of this page

You can make queries related to place ads, product placement, and advertising
make project queries related to DIY project section (college and school students for their projects)

DH11_______ Node MCU

Vcc______ VV

IN1______D4

GND______GND

Here I have made small box to fit the LCD display also power the NodeMCU board through a 9V battery.

I have used Google spread sheet to log the data into cloud.So before going to the program, We need to make sure some credentials that will be used in the code.

You can check my previous tutorial about Home Automation with python.

The First step will be getting the Google Script ID from Google Sheet. For this, you need to follow the below steps. Make sure If you have been using more one Email ID in one browser please make sure to use your frequently using ID. If you want to use other ID log in a New private window.

First you need to Login to the Gmail with your Email ID and Password
Next, Go to the App Icon In Top Right Corner.
Now choose Sheets as well as you can see there.
Create a New Blank Sheet.
Just rename this created Spreadsheet Project to any name you want.
In this tutorial I have one sheet , also you can add more sheet ,then change the sheet name as per your wish.
Now got to ‘Tools’ and click on “<> Script Editor”.
The You can rename this Google Script File to any name you want.
Now Copy and Paste the Google script code that I have given in bottom of the article.
Then edit the Sheet name and Sheet ID in the code. You can get the Sheet ID from the Sheet URL just like shown below. https://docs.google.com/spreadsheets/d/xxxxxxxxyyyyyyzzzzzzzzzz/edit#gid=0 , where “xxxxxxxxyyyyyyzzzzzzzzzz” is your Sheet ID. you have to got this link fom your spread sheet tab.

Now it’s time to get the major credential i.e. Google Script ID which will be written in the Arduino Program. If you make mistake in the copying Google Script ID then the data won’t reach to Google Sheet.

Getting the Google Script ID

Go to ‘Publish’ then click on ‘Deploy as Web App…’
The “Project version” will be “New”. Select “your email id” in the “Execute the app as” field. Choose “Anyone, even anonymous” in the “Who has access to the app” field. And then Click on “Deploy”. Note that When republishing please select the latest version and then Deploy again.

3. You will have to give the Google permission to deploy it as web app. Just click on “Review Permissions”.

4. Then choose your Email ID

5. Click on “Advanced”.

6. And then click on “Go to ‘your_script_name’(unsafe)”.

7. Click on “Allow” and it will give the permission to deploy it as web app.

8. Now you can see new window with the name of “Current web app URL”. Under this name, you can find a URL that URL contains Google script ID copy that and save it privately. it will be used in your code.

the format is like <https://script.google.com/macros/s/____Your_Google _ScriptID___/exec>.

If didn't comfortable with steps follow my tutorial from YouTube

Programming NodeMCU to Send Temperature Data to Google Sheets

Before get into the programming, You need to download some libraries click here.

The library ESP8266WiFi.h is used for accessing the functions of ESP8266, the HTTPSRedirect.h library is used for connecting to Google Spreadsheet Server, DebugMacros.h is used to debug the data receiving and DHT.h is a used to the read the DHT11 sensor.

#include <ESP8266WiFi.h>
#include "HTTPSRedirect.h"
#include "DebugMacros.h"
#include <DHT.h>

you need to add this library for use LCD display

#include <LiquidCrystal.h>

Here I have declared the pin for LCD display

const int RS = D2, EN = D3, d4 = D5, d5 = D6, d6 = D7, d7 = D8;   
LiquidCrystal lcd(RS, EN, d4, d5, d6, d7);

Define the DHT sensor pin and its type

#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);

Define the variable to store the values

float h;
float t;
String sheetHumid = "";
String sheetTemp = "";

put your network credentials

const char* ssid = "******";                //replace with our wifi ssid
const char* password = "*******"; 

Now you want to put some credentials of Google server

The host and port number will be same as attached code but you need to change the Google Scripts ID that we got from the above steps.

const char* host = "script.google.com";
const char *GScriptId = "AKfycbyE_jloJgKB4McUYy_gLPHsCI6hTh5mqdCIaLYIS3lJXRmAqwc"; // Replace with your own google script id
const int httpsPort = 443;

Define the URL of Google Sheet where the data will be written. This is basically a path where the data will be written.

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"; 

Define the google sheet address

String payload_base =  "{\"command\": \"appendRow\", \
                    \"sheet_name\": \"TempSheet\", \
                       \"values\": ";

connect to WIFI and wait for its establishment

WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

Start new HTTPS connection .If you want to establish HTTPS connection, you need to add setInscure() other wise you will lose your connection.

client = new HTTPSRedirect(httpsPort);
  client->setInsecure();
  client->setPrintResponseBody(true);
  client->setContentTypeHeader("application/json");
  Serial.print("Connecting to ");
  Serial.println(host); 

We will communicate with server with GET and POST function. GET will use to read the cells and POST method is used to write in a cell

client->GET(url2, host);

Read the temperature and Humidity values from DHT sensor and print it on Serial monitor and LCD display.

 h = dht.readHumidity();                                              // Reading temperature or humidity takes about 250 milliseconds!
  t = dht.readTemperature();
  lcd.setCursor(0,0); // row 1
  lcd.print(" Humidity = "); // print on the lcd Humidity =  
  lcd.print(h);  // to print the humidity 
  Serial.println("Humidity:");
  Serial.print(h);         

  lcd.setCursor(0,1);
  lcd.print(" Temp = ");  // print on the lcd temp =     
  lcd.print(t);  // print on the lcd temperture       
  lcd.println(" C ");
  Serial.println("Temperature:");
  Serial.print(t);  

Upload code

Now is the time to Upload the code. Select the correct COM port and board type for upload the code. Then you can the result is printing on LCD display and Google spread sheet. you can get the data to make analyze and decisions even spread sheet provides some graph and chart feature to make analyze.

Download the library and code here