Log Temperature Sensor Data to Google Sheet using NodeMCU ESP8266

There are many IoT clouds available to store and monitor the data from various sensors. With increasing popularity of IoT, more number of sensors are used every day and these sensors produce anonymous data which are useful to control other devices on the IoT network. These data can be studied further and used to modify design or improve the system performance. The Applications of Data Logging is vast and can be applied in Research Centres, Scientific Labs, Operation Theatres, Patient Monitoring System and many more.

 

Today we will do a similar project and use the Google sheet as IoT cloud to log the data generated by a Temperature Sensor. Here we will use ESP8266 NodeMCU to send the temperature and humidity data from DHT11 sensor to the Google Sheet over the internet. In previous projects we learned to log the data on SD card and other IoT clouds like ThingSpeak, Amazon Web services (AWS), MQTT server, Adafruit IO, Firebase etc.

• Arduino Data Logger (Log Temperature, Humidity, Time on SD Card and Computer)
• Publishing Temperature and Humidity Sensor Data to AWS - IoT using Raspberry Pi
• Live Temperature and Humidity Monitoring over Internet using Arduino and ThingSpeak
•  IoT Based Electricity Energy Meter using ESP12 and Arduino
• Sending Temperature and Humidity sensor data to Firebase

 

This Google Sheet Logger system is quite easy to learn and doesn’t contain much peripherals. The Components are also frequently used. Note that, the active internet connection is required to send the data to Google Sheet.

 

Components Required

• NodeMCU ESP8266 Module
• DHT11 Sensor
• Jumpers

 

Circuit Diagram

 

There will be a few steps before starting to program ESP8266 for Logging Temperature Data on Google Sheet. We will be needing few credentials that will be used to communicate and send the data from ESP8266 to Google Server to reflect on Google Sheet. The ESP8266 NodeMCU code is attached at the end of this tutorial.

I would recommend to follow the tutorial till the last as the tutorial is divided into sections and each section will play an important role. Also the tutorial contains Screenshots to help you better. The First step will be getting the Google Script ID from Google Sheet. For this, you need to follow the below steps.

 

Creating Google Script in Google Sheet for Data Logging

1. Login to the Gmail with your Email ID and Password.

 

2. Go to the App Icon In Top Right Corner Highlighted in Green Circle and Click on Docs.

 

3. The Google Docs screen will appear. Now choose Sheets in the right sidebar.

 

4. Create a New Blank Sheet.

 

5. The Blank Sheet will be created with an “Untitled Spreadsheet”. Just rename this created Spreadsheet Project to any name you want. In my case I have renamed “Untitled Spreadsheet” to “ESP8266_Temp_Logger” since I am logging temperature using ESP8266. To rename the created Spreadsheet Project, go to “File” > “Rename”.

 

6. You can also add another multiple sheets in Google spread sheet. In this tutorial only one sheet is used. So I have renamed “Sheet1” > “TempSheet” since I am logging Temperature data to sheet.

 

7. After renaming the created Spreadsheet Project and Sheet name, now its time to create a Google script.  

8. Now got to ‘Tools’ marked in green circle and click on “<> Script Editor” option marked on red circle.

 

9. The new Google Script is created with “Untitled project”. You can rename this Google Script File to any name you want. In my Case I have renamed to “Untitled project” > “TempLog_Script”.

 

10. Now Copy and Paste the Google Script code from file attached in this ZIP file here (GoogleScript.gs). 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.

 

11. When you copy and paste the Google Script then it will look like following.

 

12. Save the file. If you want to make your own sheet then change your credentials such as Sheet ID, Sheet Name and Sheet Project Name.

13. Now we have finished the Setting up Google Script in Spreadsheet. 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

1. Go to ‘Publish’ > ‘Deploy as Web App…’

 

2. 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 here using which you have created spreadsheet.

 

5. Click on “Advanced”.

 

6. And then click on “Go to ‘your_script_name’(unsafe)”. Here in my case it is “TempLog_Script”.

 

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

 

8. Now you can see the new screen with a given link and named as “Current web app URL”. This URL contains Google Script ID. Just copy the URL and save it somewhere.

 

9. Now when you copy the code, the format is like <https://script.google.com/macros/s/____Your_Google _ScriptID___/exec>. 

 

So here in my case my Google script ID in this link <https://script.google.com/macros/s/AKfycbxy9wAZKoPIpP53AvqYTFFn5kkqK_-avacf2NU_w7ycoEtlkuNt/exec> is “AKfycbxy9wAZKoPIpP53AvqYTFFn5kkqK_-avacf2NU_w7ycoEtlkuNt”.

 

Just save this Google Script to some place.

 

Programming NodeMCU to Send Temperature Data to Google Sheets

Here the DHT sensor is connected to ESP8266 NodeMCU and ESP8266 NodeMCU is connected to Internet through WiFi to send the DHT11 readings to Google Sheet. So start with including the necessary libraries. As usual the complete code is given at the end of this tutorial.

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>

 

Initially define the NodeMCU Pin Number where DHT11 sensor will be read. Here the output of DHT11 is connected to D4 of NodeMCU. Also define thee DHT type, here we are using DHT11 sensor.

#define DHTPIN D4                                                           
#define DHTTYPE DHT11    

 

Define variables to store the temperature and humidity value.

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

 

Enter your WiFi credentials such as SSID name and Password.

const char* ssid = "CircuitDigest";                
const char* password = "circuitdigestfun";

 

Enter the Google server credentials such as host address, Google script ID and port number. 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 = "AKfycbxy9wAZKoPIpPq5AvqYTFxxxkkqK_avacf2NU_w7ycoEtlkuNt"; 
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";  
String url2 = String("/macros/s/") + GScriptId + "/exec?cal";

 

Define the Google sheet address where we created the Google sheet.

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

 

Define the client to use it in the program ahead.

HTTPSRedirect* client = nullptr; 

 

Start the serial debugger or monitor at 115200 baud rate. You can also select other baud rates such as 9600, 57600 etc. And then initialise DHT11 sensor.

  Serial.begin(115200);
  dht.begin();     

 

Connect to WiFi and wait for the connection to establish.

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

 

Start a new HTTPS connection. Note that if you are using HTTPS the you need to write the line setInscure() otherwise the connection will not establish with server.

  client = new HTTPSRedirect(httpsPort);
  client->setInsecure();
  Start the respose body i.e. if the server replies then we can print it on serial monitor. 
  client->setPrintResponseBody(true);
  client->setContentTypeHeader("application/json");

 

Connect to host. Here it is "script.google.com".

  Serial.print("Connecting to ");
  Serial.println(host);  

 

Try connection for five times and if doesn’t connect after trying five times then drop the connection.

  bool flag = false;
  for (int i = 0; i < 5; i++) {
    int retval = client->connect(host, httpsPort);
    if (retval == 1) {
      flag = true;
      break;
    }
    else
      Serial.println("Connection failed. Retrying...");
  }

 

We will communicate with server with GET and POST function. GET will be used to read the cells and POST will be used to write into the cells. Get the cell data of A1 from Google sheet.

client->GET(url, host);

 

Read the temperature and Humidity data from DHT11 sensor and save it in variable. If any reads fails then print a fail message and return.

h = dht.readHumidity();                                              
  t = dht.readTemperature
  if (isnan(h) || isnan(t)) {                                                
    Serial.println(F("Failed to read from DHT sensor!"));
    return;
  }

 

Write the data in the path. This data will be written in the Google Sheet. The data path contains Temperature and  Humidity data such as sheetTemp and sheetHumid.

payload = payload_base + "\"" + sheetTemp + "," + sheetHumid + "\"}";

 

If client is connected then simply send the Data to Google Sheet by using POST function. Or save it if the data fails to send and count the failure.

   if (client->POST(url2, host, payload)) {
    ;
  }
  else {
    ++error_count;
    DPRINT("Error-count while connecting: ");
    DPRINTLN(error_count);
  }

 

If data sending fails for three times then halt all processes and exit and go to deepsleep.

  if (error_count > 3) {
    Serial.println("Halting processor...");
    delete client;
    client = nullptr;
    Serial.printf("Final free heap: %u\n", ESP.getFreeHeap());
    Serial.printf("Final stack: %u\n", ESP.getFreeContStack());
    Serial.flush();
    ESP.deepSleep(0);
  }

 

Give a delay of at least 2 seconds after every reading and sending as it is recommended by the DHT library and HTTPSRedirect library.

 

This finishes the complete tutorial on sending the sensor data to a Google sheet using ESP8266. I would recommend to download all the files such a libraries and source code from the zip file given here. Because there are some mistakes in the library and multiple versions of library are available which can create problem in development. If you have any difficulties then reach us at our forum or comment below.

"No value passed as argument to script Url."