How to upload data from ESP8266 to Google Spreadsheet: a step by step guide

How to upload data from ESP8266 to Google Spreadsheet: a step by step guide

Daniil Zhuk

Over the past couple of years, the ESP8266 platform has come a long way in its development and has become one of the most popular hardware tools among electronics and IoT enthusiasts. Equipped with a 32-bit CPU with 80 MHz RISC architecture, fully integrated WiFi module with TCP / IP protocol stack, serial interfaces (I2C, SPI, and UART), ADC channel and general-purpose I / O pins. ESP8266 is The most integrated and affordable WiFi solution on the Internet of Things today.

Hardware modules, such as NodeMCU, with their peripherals, can directly work with sensors and upload data from them to a local or remote web server via the Internet. Already, there are many cloud platforms for the Internet of Things (ThingSpeak, thinger.io, TESPA.io, Xively, etc. - the list continues to grow every day), which provide an application programming interface and tools for downloading sensor readings directly, for their further visualization and access to them from anywhere in the world in real time. Regular Google Drive users will probably notice that accessing Google Spreadsheets and using them to store and process data is much easier than with the cloud platforms of the Internet of Things.

In this guide, we will describe how to directly connect an ESP8266 based device to Google Table to store sensor data without using any third party add-ons. For example, we will use the NodeMCU board, by means of which we consider the analog data of the soil moisture sensor installed in a flower pot, and directly enter them into an electronic Google spreadsheet.

The project is divided into two parts. The first is the assembly of equipment based on the NodeMCU ESP8266 for reading data from a soil moisture sensor. The second is the creation of a Google Table and its configuration for receiving sensor data from the ESP8266 module via the Internet using a Google Apps Script language script, which is attached to this manual.

To implement the project from this article, we need the following components:

  • Nodemcu V3 Lua Wi-Fi
  • Soil moisture sensor
  • Breadboard
  • Wires

CONNECTION OF SOIL MOUNT SENSOR TO NODEMCU
Soil moisture sensor
The project uses a very simple and cheap kit with a soil moisture sensor, consisting of two open metal plates and a measuring PCB with a comparator. Open plates are a device that reacts to soil moisture. The more water in the soil, the higher the conductivity between the plates, and vice versa.

The included board with a comparator provides an analog output with a voltage that varies with the soil moisture level. The board is powered by 3.3 V, and its output is connected to the analog input channel (A0) of the NodeMCU module.

Attention! The analog input of the ESP8266 chip can only work with a maximum voltage of 1 V. But the voltage divider circuit used in the NodeMCU module allows you to work with a voltage on the analog input of the module up to 3.3 V.

The pinout diagram for connecting NodeMCU to a soil moisture sensor is shown in the figure below:

SKETCH FOR GETTING DATA WITH NODEMCU
Let's first take a look at what actually happens when we try to send data to Google servers. To send data, we will, of course, use the GET request method and a URL containing https://script.google.com/…. When you enter such an address in a web browser, the Google server in response requests the browser to redirect its GET request to another address located in the domain script.googleusercontent.com. Redirection is common and common, and the browser will easily handle it. However, for the ESP8266 chip, it is not so simple. She needs to correctly decode the information in the header received with the response message from the server in order to find out the new address and make a second request to another server.

To simplify redirection processing, a GitHub user is known as an electronics guy posted on GitHub an excellent code snippet in the Arduino Library library format called HTTPSRedirect.

There are other ways to send data to Google spreadsheets from Arduino devices through third-party services (such as pushing boxes) and their application programming interfaces, which are used to fulfill all Google server’s https messages and handling redirects. Using the HTTPS Redirect library greatly simplifies this process by eliminating the mediation of third-party code. So the first thing that needs to be done is to download the HTTPSRedirect library from the GitHub website and install it in your Arduino library folder. For convenience, we have posted the library archive at the link below.

Download HTTPSRedirect Library

To install it in the Arduino IDE, simply download this archived file, unzip it and move it to a folder called HTTPSRedirect, which should be located in the location of your Arduino IDE libraries. On a Windows PC, they are usually located at C: \ Users \ your user \ Documents \ Arduino libraries
Make sure that the package with the library contains both the HTTPSRedirect.cpp and HTTPSRedirect.h files, as shown in the figure below.

PROGRAMMING NODEMCU ESP8266 TO SEND DATA TO GOOGLE TABLE
The code below is for the ESP8266 chip and is written using the Arduino IDE development environment to receive data from the soil moisture sensor and send it to the Google Table located on the Google Disk. For the code to work, you need to change the ssid identifier and password (password) of the WiFi network to your own.

You will also need the script identifier * GScriptId, which can be obtained only after the publication of the corresponding script in the Google Apps Script language. How to get * GScriptId is described closer to the end of this guide.

Sensor data is recorded in a Google table every 15 minutes.

#include <ESP8266WiFi.h>
#include <HTTPSRedirect.h>

const char* ssid = “SSID Wi-Fi“;
const char* password = “password“;

const char *GScriptId = “Google-Script-ID”;

// Push data on this interval
const int dataPostDelay = 900000; // 15 minutes = 15 * 60 * 1000

const char* host = “script.google.com”;
const char* googleRedirHost = “script.googleusercontent.com”;

const int httpsPort = 443;
HTTPSRedirect client(httpsPort);

String url = String(“/macros/s/”) + GScriptId + “/exec?”;

const char* fingerprint = “F0 5C 74 77 3F 6B 25 D7 3B 66 4D 43 2F 7E BC 5B E9 28 86 AD”;

const int AnalogIn = A0;

void setup() {
Serial.begin(115200);
Serial.println(“Connecting to wifi: “);
Serial.println(ssid);
Serial.flush();

WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(“.”);
}
Serial.println(” IP address: “);
Serial.println(WiFi.localIP());

Serial.print(String(“Connecting to “));
Serial.println(host);

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…”);
}

Serial.println(“Connection Status: “ + String(client.connected()));
Serial.flush();

if (!flag){
Serial.print(“Could not connect to server: “);
Serial.println(host);
Serial.println(“Exiting…”);
Serial.flush();
return;
}

if (client.verify(fingerprint, host)) {
Serial.println(“Certificate match.”);
} else {
Serial.println(“Certificate mis-match”);
}
}
void postData(String tag, float value){
if (!client.connected()){
Serial.println(“Connecting to client again…”);
client.connect(host, httpsPort);
}
String urlFinal = url + “tag=” + tag + “&value=” + String(value);
client.printRedir(urlFinal, host, googleRedirHost);
}

void loop() {
int data = 1023 – analogRead(AnalogIn);
postData(“SoilMoisture”, data);
delay (dataPostDelay);
}

CREATING A GOOGLE TABLE
Create a Google spreadsheet on your Google Drive and name it, for example, DataCollector (“Data Collector”). Rename the current (or active) sheet to Summary (“General”) and add a second sheet, name it DataLogger (“Data Logger”). From the address bar of the table, copy the characters between the characters "d /" and "/ edit" and save them somewhere. This is a unique key for sharing your spreadsheet, which is later needed for the Google Apps Script language script.

Attention! The name of the Google spreadsheet does not play a big role, as in the Google Apps Script we will use the table sharing the key, which is always unique. At the same time, the names of the sheets (Summary and DataLogger) should coincide with those that you use in the Google Apps Script (described below).

In the Summary: sheet, write “Last Modified On” in cell A1, “DataLogger Count” (“Data logger counter”) in cell A2 and in cell A3 - “Next Read Time” (“Next Read Time "). In cell B2, write a formula that will be used to calculate the value of the data counter: "= counta (DataLogger! D: D) -1". In cell B3, write the following formula: “= B1 + TimeValue (“ 00:15 ”)”, which will simply add 15 minutes to the time of the last change. To create a further calibration chart in the Google Tables, information was added to cells A6 through B7, as shown in the figure below.

On the DataLogger sheet, write “ID” (“Identifier”), “DateTime” (“Date Time”), “Tag” (“Tag”) and “Value” (“Value”) in cells A1, B1, C1, and D1, respectively.

SCRIPT ON GOOGLE APPS SCRIPT
To create a Google Apps Script script in the Google Tables application, select the menu item "Tools> Script Editor" ("Tools> Script Editor"). In the code window, paste the code below. The code or script can be saved under any name.

function doGet(e){
Logger.log("--- doGet ---");

var tag = "",
value = "";

try {

if (e == null){e={}; e.parameters = {tag:"test",value:"-1"};}

tag = e.parameters.tag;
value = e.parameters.value;

save_data(tag, value);

return ContentService.createTextOutput("Wrote:n tag: " + tag + "n value: " + value);

} catch(error) {
Logger.log(error);
return ContentService.createTextOutput("oops...." + error.message
+ "n" + new Date()
+ "ntag: " + tag +
+ "nvalue: " + value);
}
}

function save_data(tag, value){
Logger.log("--- save_data ---");

try {
var dateTime = new Date();

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/---Ваш-Google-Sheet-ID--Goes-Here---/edit");
var summarySheet = ss.getSheetByName("Summary");
var dataLoggerSheet = ss.getSheetByName("DataLogger");

var row = dataLoggerSheet.getLastRow() + 1;

dataLoggerSheet.getRange("A" + row).setValue(row -1); // ID
dataLoggerSheet.getRange("B" + row).setValue(dateTime); // dateTime
dataLoggerSheet.getRange("C" + row).setValue(tag); // tag
dataLoggerSheet.getRange("D" + row).setValue(value); // value

summarySheet.getRange("B1").setValue(dateTime); // Last modified date
// summarySheet.getRange("B2").setValue(row - 1); // Count
}

catch(error) {
Logger.log(JSON.stringify(error));
}

Logger.log("--- save_data end---");
}

Attention! The names of the sheets in the above script must match the names of the sheets in the spreadsheet to which we are going to write data.

var summarySheet = ss.getSheetByName("Summary");
var dataLoggerSheet = ss.getSheetByName("DataLogger");

Similarly, you need to change the spreadsheet sharing key in the script (change the line “—Your-Google-Sheet-ID – Goes-Here-”) to yours (which you copied earlier from the spreadsheet address).

var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/---Your-Google-Sheet-ID--Goes-Here---/edit");

DEPLOYING SCRIPT AS A WEB APPLICATION


Create a table for data collection with NodeMCU
The next step is to publish the script, so that it can be accessed via the URL. To do this, select the menu item “Publish> Deploy as Web App”.

Note. Every time you change the code, you must create a new version of the project and publish it (Project version: New; Project version: New), otherwise the old code will be available to you at the address.

Copy the address from the Current web app URL field (“Current URL of the web application”) and save it somewhere, since we will need it to retrieve the script ID of the GScriptID. It is usually stored in code, as a comment.

The address of the web application is as follows (instead of “–Your Google Script ID–” there will be an identifier of your script):

https://script.google.com/macros/s/–Your Google Script ID– / exec? tag = test & value = -1

The characters between the characters “s /” and “/ exec?” Are the identifier of your GScriptID script.

During the publication process, Google will ask for permission from you, you need to grant this permission.

CHECK RESULTS


Table for data collection with NodeMCU
If done correctly, the next test will be successful. Copy the address of the web application obtained during the publication process, bring it into the form specified above, and paste the resulting line into the address field of the web browser. Information should appear in the DataLogger sheet as shown below.

Did everything work out? If not, double-check all steps. If entering the address into the browser led to the addition of data to the list, then fill in the identifier of your GScriptID script in the above code for the ESP8266 chip.

VISUALIZATION OF DATA FROM THE TABLE
We added a graph to visualize the change in time of the sensor readings recorded in the DataLogger sheet. The range of the source for the graph is sufficient for any values sent with the NodeMCU ESP8266. You can also add a calibration chart to display the last added value of soil moisture.

Below is a DataLogger sheet. Humidity readings are recorded along with time stamps.

Add a comment

* Comments must be approved before being displayed.