How to post data to Google sheets using ESP8266
|
|
In the past couple years, the ESP8266 platform has flourished dramatically and emerged as one of the most popular hardware tools among electronics hobbyists and IoT enthusiasts. Packed with a 32-bit RISC CPU running at 80 MHz, a fully integrated WiFi radio with TCP/IP protocol stack, serial peripherals (I2C, SPI, and UART), an ADC channel, and general purpose I/O pins, the ESP8266 is the most integrated and affordable WiFi solution available in the current IoT market space. An ESP8266 hardware, like NodeMCU and ESP-01, can directly interface with sensors using its peripherals and upload the sensor measurements to a local or a remote web server via internet. Right now, there are already quite a bit of cloud IoT platforms (ThingSpeak, thinger.io, TESPA.io, Xively, … the list is getting bigger everyday) that provides APIs and tools to allow the ESP8266 users to directly upload their sensor readings online for real-time visualization and global access. If you are a regular user of Google Drive, like me, you would find a Google sheet more approachable than all those IoT cloud platforms. In this tutorial, I will describe a method of connecting the ESP8266 device directly to a Google sheet for storing the sensor data without using any third party plugin. For illustration, I am using a NodeMCU board that reads the analog output from a soil moisture sensor inserted into one my flower pots and directly connects to a spreadsheet on my Google Drive for storing the data.
ESP8266 data logging to Google spreadsheets
Related Posts |
|
Check out How to send email and text messages using ESP8266. Check out our weather web server project for local web hosting of the sensor readings using ESP8266. Check out our ThingSpeak temperature and humidity logger project for remote server data logging example using ESP8266 |
Basically there are two parts to this project. The first part is setting up the ESP8266 hardware to read the soil moisture sensor output. The second part involves creating a Google sheet and configuring it to accept the sensor data sent by the ESP8266 module over the internet using a Google App Script that is provided with this tutorial.
Hardware setup
This project uses a very simple and inexpensive soil moisture sensor (one you can buy on eBay or Aliexpress for $1) kit consisting of two exposed metal pads and an instrumentation circuit board. The exposed pads act as probes for sensing the moisture level within the soil. The more water in the soil means higher conductivity between the two pads, and vice-versa. The accompanying instrumentation board provides an analog output voltage that varies with the moisture level of the soil. The instrumentation circuit is powered with a 3.3V supply and its output is fed to the analog input channel (A0) of the NodeMCU.
Soil moisture sensor consisting of exposed metal pads and an instrumentation circuit
Note: The ESP8266 analog input can handle the maximum input voltage of 1V. However, the NodeMCU implements a voltage divider circuit on board to expand the range of input voltage to 3.3V.
Software
Let’s first try to understand what really happens when we try to send data to Google’s end. We will obviously be sending data using a GET request over an URL which has a pattern of https://script.google.com/….. When you enter this URL in a web browser, the Google’s server responds back asking the browser to redirect to another URL with domain script.googleusercontent.com with a new GET request. For a web browser, the URL redirection is a very common thing and it works without any problem. However, for ESP8266, this is not straight forward. The ESP8266 needs to correctly decode the header information received from the first server to extract the redirect URL and make a second GET request to the new server. To make this redirection simpler, Sujay Phadke, aka electronicsguy, has shared on GitHub a beautiful piece of code in the form of Arduino Library named HTTPSRedirect. There are other examples of Arduino posting data to Google spreadsheets using a third party API service, like pushingbox, for handling Google’s https requirement and URL redirection. The use of HTTPSRedirect library makes the task much simpler by avoiding the need of any third party service. So, the first thing you need to do is to copy the HTTPSRedirect library files from GitHub and install into your Arduino libraries folder. I have also posted the zipped library files at the following link for convenience.
Please go to GitHub Sujay Phadke and download the updated library. Thanks Sujay.
In order to install it on your machine, simply download the above zipped file, unzip it, and move the folder named HTTPSRedirect into your Arduino’s libraries location. On Windows PC, it typically goes to C:\Users\\Documents\Arduino\libraries\ .
Arduino IDE Library Path on a Windows PC
Make sure both HTTPSRedirect.cpp and HTTPSRedirect.h files exist inside the copied library folder as shown above.
Program ESP8266 to send data to Google Sheets
Posted below is the ESP8266 code written using Arduino IDE for reading the soil moisture sensor output and posting it to a Google Sheet on your Google Drive. In order to make it work for your case, you will need to update the ssid and password matching to your WiFi network. Also, you will also need to get the *GScriptId, which can only be obtained after publishing the required Google App Scripts. The process of obtaining the *GScriptId is explained towards the bottom of this tutorial. The sensor data are posted to the Google sheet every 15 minutes.
// BY: Akshaya Niraula
// ON: Nov 11, 2016
// AT: http://www.embedded-lab.com/
#include <ESP8266WiFi.h>
#include “HTTPSRedirect.h”
const char* ssid = “–Your-SSID–“;
const char* password = “–Your-Password–“;
// The ID below comes from Google Sheets.
// Towards the bottom of this page, it will explain how this can be obtained
const char *GScriptId = “—Your-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);
// Prepare the url (without the varying data)
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”;
// We will take analog input from A0 pin
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…”);
}
// Connection Status, 1 = Connected, 0 is not.
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;
}
// Data will still be pushed even certification don’t match.
if (client.verify(fingerprint, host)) {
Serial.println(“Certificate match.”);
} else {
Serial.println(“Certificate mis-match”);
}
}
// This is the main method where data gets pushed to the Google sheet
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);
}
// Continue pushing data at a given interval
void loop() {
// Read analog value, in this case a soil moisture
int data = 1023 – analogRead(AnalogIn);
// Post these information
postData(“SoilMoisture”, data);
delay (dataPostDelay);
}
You can also download the NodeMCU code from the following link.
Download NodeMCU code
Create Google Sheets
Create a Google spreadsheet in your Google Drive and name it something like DataCollector. Rename the current/active sheet to Summary and add a second sheet, call it DataLogger. From the URL address bar of the sheet, copy the string between d/ and /edit and save it somewhere. This is your spreadsheet’s unique sharing key, which will be needed in the Google Apps scripts later.
Unique sharing key for your Google spreadsheet can be found in the URL
Note: The name of the Google spreadsheet does not matter much because we will be using the sharing key of the sheet, which is unique, in the Google Apps Scripts to access it. However, the sheets name (Summary and DataLogger) have to match with what you put in your Google Apps Scripts (described later).
In the Summary sheet, type Last Modified On on cell A1, DataLogger Count on A2, and Next Read Time on A3. In cell B2, type a formula that will give us the data count ‘=counta(DataLogger!D:D) -1‘ In B3, type this equation, =B1 + TimeValue(“00:15”), which will simply add 15 minute to last modified date time. To support a gauge chart in Google Sheets, some information from A6 to B7 has been added, as showing in the image below
Goolge Sheets – Summary tag
In the DataLogger sheet, type ID, DateTime, Tag, and Value in A1, B1, C1 and D1, respectively.
Google Sheets Data Logger Tab
Google Apps Scripts
To create a Google App Scripts, go to Tools > Script Editor from the Google Sheets. In the code section, paste the code below. The code or script can be saved under any name.
// BY: Akshaya Niraula // ON: 2016 November 12th. // AT: http://www.embedded-lab.com/..... // Steps are valid as of 2016 November 12th. // 0) From Google spreadsheet, Tools > Scriipt Editor... // 1) Write your code // 2) Save and give a meaningful name // 3) Run and make sure "doGet" is selected // You can set a method from Run menu // 4) When you run for the first time, it will ask // for the permission. You must allow it. // Make sure everything is working as it should. // 5) From Publish menu > Deploy as Web App... // Select a new version everytime it's published // Type comments next to the version // Execute as: "Me (your email address)" // MUST: Select "Anyone, even anonymous" on "Who has access to this script" // For the first time it will give you some prompt(s), accept it. // You will need the given information (url) later. This doesn't change, ever! // Saving the published URL helps for later. // https://script.google.com/macros/s/---Your-Script-ID--Goes-Here---/exec // https://script.google.com/macros/s/---Your-Script-ID--Goes-Here---/exec?tag=test&value=-1 // This method will be called first or hits first function doGet(e){ Logger.log("--- doGet ---"); var tag = "", value = ""; try { // this helps during debuggin if (e == null){e={}; e.parameters = {tag:"test",value:"-1"};} tag = e.parameters.tag; value = e.parameters.value; // save the data to spreadsheet 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); } } // Method to save given data to a sheet function save_data(tag, value){ Logger.log("--- save_data ---"); try { var dateTime = new Date(); // Paste the URL of the Google Sheets starting from https thru /edit // For e.g.: https://docs.google.com/..../edit var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/---Your-Google-Sheet-ID--Goes-Here---/edit"); var summarySheet = ss.getSheetByName("Summary"); var dataLoggerSheet = ss.getSheetByName("DataLogger"); // Get last edited row from DataLogger sheet var row = dataLoggerSheet.getLastRow() + 1; // Start Populating the data 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 // Update summary sheet 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---"); } |
Important Note: In the above script, the sheet names should match to those we want to be populated.
var summarySheet = ss.getSheetByName("Summary"); var dataLoggerSheet = ss.getSheetByName("DataLogger"); |
Similarly, you would also need to edit the spreadsheet sharing key in the script to match with yours (one you copied from the spreadsheet URL earlier).
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/---Your-Google-Sheet-ID--Goes-Here---/edit"); |
Download Goolge App Scripts
Deploy the contents as Web app
Next step is to publish the script so that it is accessible through an URL. In order to do that, Publish > Deploy as Web App.
Note: Whenever you modify your code, you have to create a “New” Project version and publish it otherwise you will still be hitting the same old code.
Copy the Current web app URL and save it somewhere as we will need it for extracting the GScriptID. I usually save it in my code as a comment. The web app URL would look like this:
https://script.google.com/macros/s/–Your Google Script ID–/exec?tag=test&value=-1
The string between s/ and /exec? is your GScriptID.
Deploy As Web App
During publishing, Google will ask to grant some of the permissions and you need to allow those.
Test the Progress
If things been followed properly, the following test should go successful. Copy the web app URL that you received during publishing, format it like shown above and paste this string in your web browser URL field. You should see the following information populated in the DataLogger sheet as shown below.
Test data posted to Google sheet
So far so good? If not, double check the steps. If the URL entered in your browser populated the values in your sheet then update your GScriptID in the ESP8266 code posted earlier.
Output
I added a line chart to visualize the time series of sensor data posted on the DataLogger sheet. The source range for the chart is big enough to cover any new data sent by ESP8266. You can also add a gauge to display the most recent soil moisture data.
Charts based on data posted on DataLogger sheet by NodeMCU
Here’s a quick view of the DataLogger sheet. The moisture data are recorded with time stamps.
Sensor data posted by ESP8266 with a minute interval
|
|
Hi ANir, thanks for your tutorial, very useful. I’d like to do the same (send data to a spreadsheet) with the micro:bit and its ESP8266 extension. I’ve created the script for the spreadsheet and it works properly.
From the micro:bit, I send data through an http GET, directly to script.googleusercontent.com, since there isn’t a way for redirect, here is the code (Python):
def on_button_pressed_a():
global Temperature
Temperature = input.temperature()
WiFiBit.execute_http_method(HttpMethod.GET,
“https://script.googleusercontent.com”,
443,
“/macros/s/_my key_/exec?tag=” + “Temperature” + “&value=” + str(Temperature))
input.on_button_pressed(Button.A, on_button_pressed_a)
Temperatura = 0
WiFiBit.connect_to_wi_fi_bit()
WiFiBit.connect_to_wi_fi_network(“_My WiFi_”, “”)
but it doesn’t work… Do you have any idea? Thanks!
I am using MQ135 to measure the amount CO2 in my room and I have written same code except for this part-
void loop(){
sensorData=analogRead(sensor);
Serial.print(“co2ppm level=”);
Serial.print(sensorData);
delay(2000);
if(isnan(sensorData)){
Serial.println(“Failed to read from co2ppm senmsor!”);
return;
}
Serial.print(“co2ppm: “);
Serial.print(sensorData);
postData(“SensorValue” ,sensorData);
delay(dataPostDelay);
But I my server unable to connect with the host.This is what I get on Serial Monitor.
co2ppm level=228.00co2ppm: 228.00Connecting to client again….
Error! Not connected to host.
And can you tell what whose fingerprint is being used in the code.
Please solve my problem. I’d be obliged
Thanks for the article.
Is there any way to protect the sheet from unauthorized access? Looks like anyone can push data to the sheet if he has an URL for the app.
By the way, have you tried to use Sheets API + API key or OAuth2?
can i get the script code and esp8266 code for logging dht 11 sensor value
and also can you share how to edit the codes when there instead another sensor placed instead of dht 11
I updated the HTTPSRedirect and now get
/home/abasel/Dropbox/Transfer/WorkingOn/WeatherStation – Dad/WeatherStationBMP280-GSheets/WeatherStationBMP280-GSheets.ino: In function ‘void postData(String, float)’:
WeatherStationBMP280-GSheets:90:52: error: no matching function for call to ‘HTTPSRedirect::printRedir(String&, const char*&, const char*&)’
client.printRedir(urlFinal, host, googleRedirHost);
^
/home/abasel/Dropbox/Transfer/WorkingOn/WeatherStation – Dad/WeatherStationBMP280-GSheets/WeatherStationBMP280-GSheets.ino:90:52: note: candidate is:
In file included from /home/abasel/Dropbox/Transfer/WorkingOn/WeatherStation – Dad/WeatherStationBMP280-GSheets/WeatherStationBMP280-GSheets.ino:9:0:
/home/abasel/Arduino/libraries/HTTPSRedirect/HTTPSRedirect.h:48:10: note: bool HTTPSRedirect::printRedir()
bool printRedir(void);
^
/home/abasel/Arduino/libraries/HTTPSRedirect/HTTPSRedirect.h:48:10: note: candidate expects 0 arguments, 3 provided
exit status 1
no matching function for call to ‘HTTPSRedirect::printRedir(String&, const char*&, const char*&)’
Hi, I dont know if it further helps you, but I can solve this problem… I’ve spent a lot of time trying to figure out whats happen and I tried many libaries HTTPSRedirect and many versions.
After get a large variety of errors, include that above. I start looking for to another soluction or find way to fix this library… For me, it was easy find alternative way…
I use the library #include and the library #include . Obviouly, I’m referring just to send data, to get data from sensor and treat each one you need include in your code.
The GScript use in this tutorial works for me, so you can use tha same.
I’ll post here exactly what I do and further I’ll post the whole code in my Github.
#include
#include
#include
#include
#include
#define DHTPIN D3 // Digital pin connected to the DHT sensor
// Uncomment the type of sensor in use:
#define DHTTYPE DHT11 // DHT 11
const char* ssid = “Skynet Outside”; //YOUR SSID
const char* password = “vgnns21s”; //PASSWORD OF YOUR SSID
const char* host = “script.google.com”;
const int httpsPort = 443;
unsigned long tempoDeEnvioDHT = millis(); //tempo decorrido
unsigned long tempoDeEnvioLumus = millis(); //tempo decorrido
//calculo do tempo para comparar (1000 x 60 x minutos desejadio) = tempo em minutos
int tempoDHT = 1000 * 60 * 2;
int tempoLumus = 1000 * 60 * 1;
// Use WiFiClientSecure class to create TLS connection
BearSSL::WiFiClientSecure client;
const char* fingerprint = “46 B2 C3 44 9C 59 09 8B 01 B6 F8 BD 4C FB 00 74 91 2F EF F6”;
//Current web app URL: https://script.google.com/macros/s/–SgScript_ID–/exec
String gScript_ID = “AKfycbwoPaO42Y64uhWYbjLaW-NroTYpD3tVcsgEDNUv”; //copia link entre /s/… e …/exec **** CHANGE IT, PUT YOUR GSCRIPT ID
char *tags[] = {“temperatura”, “umidade”, “luminosidade”};
DHT dht(DHTPIN, DHTTYPE);
float umidade, temperatura;
void setup()
{
Serial.begin(115200);
client.setInsecure();
//connecting to internet
Serial.print(“connecting to “);
Serial.println(ssid);
WiFi.mode(WIFI_STA);
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());
dht.begin();
}
void loop()
{
//This void loop works even if there is no DHT sensor
temperatura = random(15, 35);
sendData(temperatura, tags[0]);
delay(5000);
}
//enviar dados para o Google Spreadsheet
void sendData(float value, String tag){
if (!client.connect(host, httpsPort)) {
Serial.println(“connection failed”);
return;
}
String string_tag= String(tag);
String string_value = String(value, 1);
Serial.print(string_tag);
Serial.print(“: “);
Serial.println(string_value);
String url = “/macros/s/” + gScript_ID + “/exec?tag=” + string_tag + “&value=” + string_value;
Serial.println(url);
Serial.print(“requesting URL temp: “);
client.print(String(“GET “) + url + ” HTTP/1.1\r\n” +
“Host: ” + host + “\r\n” +
“User-Agent: BuildFailureDetectorESP8266\r\n” +
“Connection: close\r\n\r\n”);
delay(500);
Serial.println(“request sent”);
}
The includes at beggining of code
#include
#include
#include
#include
#include
i dont know, but the includes does not appear in my comments
1. # include
2. # include
3. # include
4. # include
5. # include
1. ESP8266WiFi
2. WiFiClientSecure
3. Hash
4. Adafruit_Sensor
5. DHT
I have set up my Script which I have tested to confirm that it works. However when I upload the code I get the following error:
. IP address:
192.168.1.117
Connecting to script.google.com
Connection failed. Retrying…
Connection failed. Retrying…
Connection failed. Retrying…
Connection failed. Retrying…
Connection failed. Retrying…
Connection Status: 0
Could not connect to server: script.google.com
Exiting…
Connecting to client again…
Error! Not connected to host.
I have confirmed that I am using the correct GScriptId.
I got the same erro. I change the way to work with Arduino and Gscript. I thing the problem is on RTTPSRedirect library. So I use this conde bellow
I dont know, but the includes does not appear in my comments
1. # include
2. # include
3. # include
4. # include
5. # include
1. ESP8266WiFi
2. WiFiClientSecure
3. Hash
4. Adafruit_Sensor
5. DHT
# define DHTPIN D3 // Digital pin connected to the DHT sensor
// Uncomment the type of sensor in use:
#define DHTTYPE DHT11 // DHT 11
const char* ssid = “Skynet Outside”; //YOUR SSID
const char* password = “vgnns21s”; //PASSWORD OF YOUR SSID
const char* host = “script.google.com”;
const int httpsPort = 443;
unsigned long tempoDeEnvioDHT = millis(); //tempo decorrido
unsigned long tempoDeEnvioLumus = millis(); //tempo decorrido
//calculo do tempo para comparar (1000 x 60 x minutos desejadio) = tempo em minutos
int tempoDHT = 1000 * 60 * 2;
int tempoLumus = 1000 * 60 * 1;
// Use WiFiClientSecure class to create TLS connection
BearSSL::WiFiClientSecure client;
const char* fingerprint = “46 B2 C3 44 9C 59 09 8B 01 B6 F8 BD 4C FB 00 74 91 2F EF F6”;
//Current web app URL: https://script.google.com/macros/s/–SgScript_ID–/exec
String gScript_ID = “AKfycbwoPaO42Y64uhWYbjLaW-NroTYpD3tVcsgEDNUv”; //copia link entre /s/… e …/exec **** CHANGE IT, PUT YOUR GSCRIPT ID
char *tags[] = {“temperatura”, “umidade”, “luminosidade”};
DHT dht(DHTPIN, DHTTYPE);
float umidade, temperatura;
void setup()
{
Serial.begin(115200);
client.setInsecure();
//connecting to internet
Serial.print(“connecting to “);
Serial.println(ssid);
WiFi.mode(WIFI_STA);
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());
dht.begin();
}
void loop()
{
//This void loop works even if there is no DHT sensor
temperatura = random(15, 35);
sendData(temperatura, tags[0]);
delay(5000);
}
//enviar dados para o Google Spreadsheet
void sendData(float value, String tag){
if (!client.connect(host, httpsPort)) {
Serial.println(“connection failed”);
return;
}
String string_tag= String(tag);
String string_value = String(value, 1);
Serial.print(string_tag);
Serial.print(“: “);
Serial.println(string_value);
String url = “/macros/s/” + gScript_ID + “/exec?tag=” + string_tag + “&value=” + string_value;
Serial.println(url);
Serial.print(“requesting URL temp: “);
client.print(String(“GET “) + url + ” HTTP/1.1\r\n” +
“Host: ” + host + “\r\n” +
“User-Agent: BuildFailureDetectorESP8266\r\n” +
“Connection: close\r\n\r\n”);
delay(500);
Serial.println(“request sent”);
}
I have problem with conection to host.
serial port:
z-14
. IP address:
192.168.10.101
Connecting to script.google.com
Connection failed. Retrying…
Connection failed. Retrying…
Connection failed. Retrying…
Connection failed. Retrying…
Connection failed. Retrying…
Connection Status: 0
Could not connect to server: script.google.com
Exiting…
Connecting to client again…
/macros/s/AKfycbwIYvdwYO8LFJdAa2fQxbdm4nh0PYmVundzN3VqUMPPaz-EPWwb/dev?tag=SoilMoisture&value=1019.00
Error! Not connected to host.
my scetch:
// BY: Akshaya Niraula
// ON: Nov 11, 2016
// AT: http://www.embedded-lab.com/
#include
#include “HTTPSRedirect.h”
const char* ssid = “z-14”;
const char* password = “111111112”;
// The ID below comes from Google Sheets.
// Towards the bottom of this page, it will explain how this can be obtained
const char *GScriptId = “AKfycbwIYvdwYO8LFJdAa2fQxbdm4nh0PYmVundzN3VqUMPPaz-EPWwb”;
// Push data on this interval
const int dataPostDelay = 9000; // 15 minutes = 15 * 60 * 1000
const char* host = “script.google.com”;
const char* googleRedirHost = “script.googleusercontent.com”;
const int httpsPort = 80;
HTTPSRedirect client(httpsPort);
// Prepare the url (without the varying data)
String url = String(“/macros/s/”) + GScriptId + “/dev?”;
const char* fingerprint = “F0 5C 74 77 3F 6B 25 D7 3B 66 4D 43 2F 7E BC 5B E9 28 86 AD”;
// We will take analog input from A0 pin
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…");
}
// Connection Status, 1 = Connected, 0 is not.
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;
}
// Data will still be pushed even certification don't match.
if (client.verify(fingerprint, host)) {
Serial.println("Certificate match.");
} else {
Serial.println("Certificate mis-match");
}
}
// This is the main method where data gets pushed to the Google sheet
void postData(String tag, float value){
if (!client.connected()){
Serial.println("Connecting to client again…");
client.connect(host, httpsPort);
}
// String urlFinal = url + "tag=" + "R2" "&value=" + "1234.00";
String urlFinal = url + "tag=" + tag + "&value=" + String(value);
//client.printRedir(urlFinal, host, googleRedirHost);
Serial.println(urlFinal);
client.printRedir(urlFinal, host, googleRedirHost);
}
// Continue pushing data at a given interval
void loop() {
// Read analog value, in this case a soil moisture
int data = 1023 – analogRead(AnalogIn);
// Post these information
postData("SoilMoisture", data);
delay (dataPostDelay);
}
I got this problem. So I decided work with anothers library. I thing the problem is on RTTPSRedirect library. So I use this code bellow
I dont know, but the includes does not appear in my comments
1. # include
2. # include
3. # include
4. # include
5. # include
1. ESP8266WiFi
2. WiFiClientSecure
3. Hash
4. Adafruit_Sensor
5. DHT
# define DHTPIN D3 // Digital pin connected to the DHT sensor
// Uncomment the type of sensor in use:
#define DHTTYPE DHT11 // DHT 11
const char* ssid = “Skynet Outside”; //YOUR SSID
const char* password = “vgnns21s”; //PASSWORD OF YOUR SSID
const char* host = “script.google.com”;
const int httpsPort = 443;
unsigned long tempoDeEnvioDHT = millis(); //tempo decorrido
unsigned long tempoDeEnvioLumus = millis(); //tempo decorrido
//calculo do tempo para comparar (1000 x 60 x minutos desejadio) = tempo em minutos
int tempoDHT = 1000 * 60 * 2;
int tempoLumus = 1000 * 60 * 1;
// Use WiFiClientSecure class to create TLS connection
BearSSL::WiFiClientSecure client;
const char* fingerprint = “46 B2 C3 44 9C 59 09 8B 01 B6 F8 BD 4C FB 00 74 91 2F EF F6”;
//Current web app URL: https://script.google.com/macros/s/–SgScript_ID–/exec
String gScript_ID = “AKfycbwoPaO42Y64uhWYbjLaW-NroTYpD3tVcsgEDNUv”; //copia link entre /s/… e …/exec **** CHANGE IT, PUT YOUR GSCRIPT ID
char *tags[] = {“temperatura”, “umidade”, “luminosidade”};
DHT dht(DHTPIN, DHTTYPE);
float umidade, temperatura;
void setup()
{
Serial.begin(115200);
client.setInsecure();
//connecting to internet
Serial.print(“connecting to “);
Serial.println(ssid);
WiFi.mode(WIFI_STA);
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());
dht.begin();
}
void loop()
{
//This void loop works even if there is no DHT sensor
temperatura = random(15, 35);
sendData(temperatura, tags[0]);
delay(5000);
}
//enviar dados para o Google Spreadsheet
void sendData(float value, String tag){
if (!client.connect(host, httpsPort)) {
Serial.println(“connection failed”);
return;
}
String string_tag= String(tag);
String string_value = String(value, 1);
Serial.print(string_tag);
Serial.print(“: “);
Serial.println(string_value);
String url = “/macros/s/” + gScript_ID + “/exec?tag=” + string_tag + “&value=” + string_value;
Serial.println(url);
Serial.print(“requesting URL temp: “);
client.print(String(“GET “) + url + ” HTTP/1.1\r\n” +
“Host: ” + host + “\r\n” +
“User-Agent: BuildFailureDetectorESP8266\r\n” +
“Connection: close\r\n\r\n”);
delay(500);
Serial.println(“request sent”);
}
Hello Dear All!
I needed more variables than tag and value, so i changed everything a bit.
Everything looked like good, the esp got back the spreadsheet app reponse with sent variables,
BUT! The DataLogger sheet was silent.
After a few half hours I figured out the problem, it was with the timestamp in the googlescript.
I changed:
var dateTime = new Date();
to:
var dateTime = Utilities.formatDate(new Date(), “GMT+2”, “yyyy-MM-dd’ ‘HH:mm E”);
(I use GMT+2 because I’m in Europe,may You need change it based on Your location.)
I had time problems with spreadsheets before, so for solution I used these codeline for date from Java reference.It looks like:
2018-05-01 22:45 Tue
p.s.:Is there somebody who knows why caused that?
Robert
I believe I followed the steps correctly
#1 ) DOWNLOAD the arduino code, NOT copy from the web (gets errors when verifying if you copy)
#2) create the sheet with the two tabs and heading columns
#3) create the scrips file, RUN doGet = posts ‘undefined’ in your sheet
#4) publish and test, posts test and -1 in the sheet
#5 ) put in my SSID / password and the script address between s/ and /edit
#6) upload to my ESP8266
when using the serila monitor, I get my WiFi connection followed by a fast data post, then one more post.
then it hangs.
and nothing gets to the sheet.
Connecting toconst char* script.google.com
Connection Status: 1
Certificate mis-match
29
Wrote:
tag: SoilMoisture
value: 81.00
0
Connecting to client again…
29
Wrote:
tag: SoilMoisture
value: 63.00
0
Can anyone point out where I made my mistake ?
thanks for doing the research and presenting this page.
the only huge mistake is in linking the updated library.
since it is incomparable with this tutorial, the only link that should be listed is version 1
better known as the only one that works with this tutorial.
hello,when I compiler the ardiono code,I get the following error:’class HTTPSRedirect’ has no member named ‘verify’,I have already move the folder in the correct place,can you help me,thanks
hello
can i get data from Google spreadsheet into my esp8266?
can u teach me? i already got json,csv, and html file from my Gspreadsheet but i dont know the code for my esp
thank you
hello can i use this tutorial to arduino uno?
cause i got this
Fatal error: ESP8266WiFi.h: No such file or directory
i already download that but still got that error
please help me
thanks
I am sorry, I personally won’t be able to help you on this. Might someone else help…
Hello!
I tried this brilliant project. First I half an hour can’t get it work(arduino IDE exits with error(maybe “/316” or “/314”), after i realize the code for the ESP… that i simple copied from this site has another type of apostrofes (“). I change all of them and now it works, except the certificate, its mistmatched for me too.
I just write it down, maybe it help someone who went like me.
Thank You Akshaya Niraula for this great project tutorial and code and Sujay Phadke for the idea and the library!
Thank You!
Robert
Gald to hear that.
Good one. Thank you.
i am getting certificate miss match error
That shouldn’t really stop you from rest of the process. I was getting the same but still things were going through to google sheet.
Hi Akshaya! First off, thank you for the excellent tutorial…easily the best written and most clear that I’ve ever seen.
I am really excited for this project, but I can’t get the code to compile…even when I just open the sketch from your .zip file! I am getting the following error:
exit status 1
no matching function for call to ‘HTTPSRedirect::printRedir(String&, const char*&, const char*&)’
I looked in the HTTPSRedirect.h library, and didn’t see a corresponding function. I’ve tried several workarounds without any luck so far. Any idea where I’ve gone wrong here?
Sounds like you haven’t setup the library properly. Note: this code relies on another code which you have to place at the right location. Look around “Download HTTPSRedirect Library” in the page…
I also contacted Sujay, the author of the library. He had this to say:
“Hi, as I mentioned in the comments on that page, the code there is outdated. (it’s always a problem when people paste code rather than linking to the original). The author hasn’t updated it since I release V2 of this library.
Please follow the instructions in the Readme and see the example to understand how it works. You no longer call printRedir() directly. You’ll have to modify the sensor recording code accordingly for your needs.”
I’m now tinkering, trying to find a workaround…will let you know if I succeed!
Thanks for taking the extra step. I added a note in the section above asking to visit the GitHub directly.
Hi Rock, have you had any success on testing a workaround for the printRedir() call replacement? I have been trying the same without success, the Github README document really only focuses on the GET function instead of the POST
Thanks
As gowtham said,
re the
‘HTTPSRedirect::printRedir error
Using the old httpredirect link on this website works, so Don’t use the latest github version
Here is the link the works copied from above.
http://embedded-lab.com/blog/wp-content/uploads/2016/11/HTTPSRedirect1.zip
This problem occurs for me also.Instead pf using new httpredirect updated library i used the old httpredirect library .to download the old httpredirect the link is in the website itself.Now will not show any errors.its just work fine for me.try this.
Dear ANir, thank you posting such great tutorial! I followed the tutorial but I got the following message:
Connection Status: 1
Connecting to client again…
Connecting to client again…
Connecting to client again…
Connecting to client again…
Connecting to client again…
So something not right in the function postData(). Notice that the status returned 1, meaning ESP8266 connected with google then it dropped. I doubted something wrong with printRedir(), so I commented the last two lines of the function postData() then tested the connection as:
void postData(String tag, float value){
if (!client.connected()){
Serial.println(“Connecting to client again…”);
client.connect(host, httpsPort);
}
else
{
Serial.println(“Connected to client”);
}
// String urlFinal = url + “tag=” + tag + “&value=” + String(value);
// client.printRedir(urlFinal, host, googleRedirHost);
}
Now I get
Connection Status: 1
Connected to client
Connected to client
Connected to client
Connected to client
Connected to client
It looks to me that as long as the line
client.printRedir(urlFinal, host, googleRedirHost);
is called, the connection to the client is dropped. I can be sure my input to the function postData() is correct. I even tried to fix variable urlFinal as
float fixedValue = 60.f;
String urlFinal = url + “tag=” + tag + “&value=” + String(fixedValue);
The issue still persisted.
Could you please suggest if I did anything wrong?
Kyle, it’s hard to say what’s causing the issue from the code you pasted. It seems like connection is there but if the data is not getting pushed, I would say print the url, copy it and paste to a browser then see if that’s valid meaning the url is doing the expected changes on Google side. You want to avoid and space or special characters in the web url, just FYI.
Hello, I had the same isssue. Problem was really in URL final. When I tried to copy URL from ino file to browser, URL did not work even it looked exactly the same as working one. Just try to copy working url to your ino file again. Hope it helps. Ludek
warning: espcomm_sync failed
error: espcomm_open failed
error: espcomm_upload_mem failed
error: espcomm_upload_mem failed
I received the above errors. I am using ESP8266 along with Arduino Mega.
Hi
Your project works very well.
I read reviews and tried many times, but i still struggle to add more sensors. Could you please help me more specific to add more values and tags in ESP and GAS?
Hello to all
Its really a very nice work. I really able to see data of my multiple DS18b20 in gsheet. I am using ESP 8266. Every thing work perfectly but I got one issue that I cannot find the way to sent email if my temp data go upper or lower limit.
I want to use B7 cell to get latest data and then it should be checked by if condition for high or low temp if its higher then 30 or lower then 20 i should get an email to alart .
Looking forward for your help
Thank you
Regards
Khan
Hi there,
I generally don’t like to point just to a web link but in this let me ask, have you tried http://embedded-lab.com/blog/send-email-text-messages-using-esp8266/ ?
Hello ANir,
thank you for publishing great tutorial. I am trying to modify the code for more parameters. From ESP8266 I am sending this modified string with one more parameter:
?tag=Hmotnost1&value=10&tag1=Teplota&value1=10
But there is any mistake on the Google script side, it does not work further:
// This method will be called first or hits first
function doGet(e){
Logger.log(“— doGet —“);
// One more parameter tag1, value1
var tag = “”,
value = “”,
tag1 = “”,
value1 = “”;
try {
// this helps during debuggin
if (e == null){e={}; e.parameters = {tag:”test”,value:”-1″};}
tag = e.parameters.tag;
value = e.parameters.value;
tag1 = e.parameters.tag;
value1 = e.parameters.value;
// save the data to spreadsheet
save_data(tag, value, tag1, value1);
// what are the correct delimiters here?
return ContentService.createTextOutput(“Wrote:\n tag: ” + tag + “\n value: ” + value + “\n tag1: ” + tag1 + “\n value1: ” + value1);
} catch(error) {
Logger.log(error);
return ContentService.createTextOutput(“oops….” + error.message
+ “\n” + new Date()
+ “\ntag: ” + tag +
+ “\nvalue: ” + value +
+ “\ntag1: ” + tag1 +
+ “\nvalue1: ” + value1);
}
}
// Method to save given data to a sheet
function save_data(tag, value, tag1, value1){
Logger.log(“— save_data —“);
try {
var dateTime = new Date();
// Paste the URL of the Google Sheets starting from https thru /edit
// For e.g.: https://docs.google.com/…./edit
var ss = SpreadsheetApp.openByUrl(“https://docs.google.com/spreadsheets/d/—- ID——/edit”);
var summarySheet = ss.getSheetByName(“Souhrn”);
var dataLoggerSheet = ss.getSheetByName(“Data”);
// Get last edited row from DataLogger sheet
var row = dataLoggerSheet.getLastRow() + 1;
// Start Populating the data
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
dataLoggerSheet.getRange(“E” + row).setValue(tag1); // tag1
dataLoggerSheet.getRange(“F” + row).setValue(value1); // value1
// Update summary sheet
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—“);
}
How to Post multiple data to Google Sheet:
(Here are bits and pieces of the codes, please stitch them together…)
//
// Format data as JSON string
String formatDataToJson(String tag, String value){
return String(“\”” + tag + “\”:\”” + value + “\””);
}
//
// This is the main method where data gets pushed to the Google sheet
void postData(String dataToPost){
Serial.println(“Data to post: ” + dataToPost);
if (!client.connected()){
Serial.println(“Connecting to client again…”);
client.connect(host, httpsPort);
}
String urlFinal = url + “data=” + dataToPost;
client.printRedir(urlFinal, host, googleRedirHost);
}
//
// Continues to loop forever
void loop(){
String dataBuilder = “{“;
// Read analog value, in this case a soil moisture
int moisture = analogRead(AnalogIn);
dataBuilder.concat(formatDataToJson(tag + “Moist”, String(1023 – moisture)));
dataBuilder.concat(“,”);
// Read Tempearture from BME280
float temp = bme.readTemperature();
if(temperatureInF){temp = temp * 9 / 5 + 32;}
dataBuilder.concat(formatDataToJson(tag + “Temp”, String(temp)));
dataBuilder.concat(“,”);
// Read Humidity from BME280
float humid = bme.readHumidity();
dataBuilder.concat(formatDataToJson(tag + “Humid”, String(humid)));
dataBuilder.concat(“,”);
// Read Pressure from BME280
float pressure = bme.readPressure()/100.0F;
dataBuilder.concat(formatDataToJson(tag + “Pressure”, String(pressure)));
dataBuilder.concat(“,”);
// Read Light Intensity
float lux = getTSLData();
if (lux != -1){
dataBuilder.concat(formatDataToJson(tag + “Light”, String(lux)));
}
dataBuilder.concat(“}”);
// Post all the data at once
postData(dataBuilder);
// Wait for given time
delay (dataPostDelay);
}
NOW on the GAS side..
// this helps during debuggin
if (e == null){e={}; e.parameters = {“data”:{“tag1″:”value1″,”tag2″:”value2″,”tag3″:”value3”}}}
dataReceived = e.parameters.data;
// pass this to save_data(dataReceived)
// Method to save given data to a sheet
function save_data(dataSet){
Logger.log(“— save_data —“);
try {
var dateTime = new Date();
// Paste the URL of the spreadsheeet starting from https thru /edit
// For e.g.: https://docs.google.com/…./edit
var ss = SpreadsheetApp.openByUrl(“https://docs.google.com/spreadsheets/d/—Your-Google-Sheet-Info-From-Address-Bar—/edit”);
var summarySheet = ss.getSheetByName(“Summary”);
var dataLoggerSheet = ss.getSheetByName(“DataLogger”);
// Get last edited row from DataLogger sheet
var row = dataLoggerSheet.getLastRow() + 1;
// Start Populating the data
for(var data in dataSet){
dataLoggerSheet.getRange(“A” + row).setValue(row -1); // ID
dataLoggerSheet.getRange(“B” + row).setValue(dateTime); // dateTime
dataLoggerSheet.getRange(“C” + row).setValue(dataSet[data]); // tag
dataLoggerSheet.getRange(“D” + row).setValue(dataSet[data]); // value
row += 1;
};
// Update summary sheet
summarySheet.getRange(“B1”).setValue(dateTime); // Last modified date
}
catch(error) {
Logger.log(JSON.stringify(error));
}
Logger.log(“— save_data end—“);
}
Hi Anir,
Can i ask something? I follow every of your step but i couldn’t display the time in google drive like yours. I only manage to display date. I can only view the time when i press that particular column and row. How can i make the time display without pressing the particular cell. What should i do??
In the toolbar of GoogleSheet you will find an icon “123” click on this and towards the bottom, there is “Date time”. Click on this.
I managed to get your method to work for a small weather station back in March. However, I am trying to reuse this method for another project and I’ve hit a roadblock.
I’ve narrowed the issue down to when I publish the Gscript as a web app. When I use the Gscript url (https://script.google.com/macros/s/–My Google Script ID–/exec?valueT=75), the script processes the date and time, but leaves the value blank.
While I was poking around, I discover that when I publish the Gscript, there is a button to test the script. But, instead of having “/exec?” at the end of the url, it has “/dev?” at the end. Also, instead of the Google Script ID, it has some other random assortment of letters and numbers.
Using this new format (https://script.google.com/macros/s/–some other random assortment–/dev?valueT=75), works perfectly. Unfortunately, I can’t get this new format to work with the Arduino code, so I’m spinning my wheels now.
Thanks in advance, great tutorial, immensely helpful.
-Anthony
Well, I managed to solve my own problem. Apparently, the difference between “/exec” and “/dev” is that one is that the “/dev” gives you write access and lets you work with the most recent version of the script whereas “/exec” is the most recent *published* version of the script. Learning that didn’t solve my initial problem, but “unpublishing” and then “re-publishing” my script did the trick. Somehow…
Thanks again for a great tutorial,
-Anthony
Hi, I got the code working from an ESP8266 breakout board (adafruit #2471). Now I wonder how to add more value posting to the same row of Gsheet. I think I need to modify the postData() in 8266 code and also the gscript function doGet and save_data. However, I’ve try a few method and did not get it work. Actually, after the modification, it seems not able to post data any more. If possible, will you please help give some hint how to do so? I am trying to add the temp and humidity (and pressure later) from BME280 sensor.
thanks,
Leonard
Hi Leonard,
I will give some tips and see if you can figure out. I feel like someone else have asked this question too… The main key thing is you have to send data in JSON format so that you can easily parse in the GScript side.
So prepare your data in JSON format that you need to send GScript and send. On the GScript sites, it’s a lot easier to parse the JSON data. All you have to do is json.parse(yourDataString).
done – it’s working and I changed the tag to sensor ID, add column so now it logs multiple data in one shot. Thanks for your help.
Leonard
Nice to hear that and thanks for the update.
Feel free to share your work if you like
Excellent! Thank you for your sharing.
I’m trying to implement the base code you provide, but I keep getting a missing library error:
C:\Arduino\libraries\HTTPSRedirect/HTTPSRedirect.h:7:30: fatal error: WiFiClientSecure.h: No such file or directory
#include
GitHub search shows that library should be part of the ESP8266Wifi_master Lib, but I can’t include it…I’m obviously a noob, so a little help would be appreciated!
Not really sure…
Compare this image with your folder…
can’t post images, but I have the same .cpp and .h files, and additionally:
Folder named “Extra”
An example sketch called GoogleDocs.ino
and a README.md
The Extra folder contains a shot of the creator’s google calendar in .jpg, and a file called code.gs…that is all!
Oh, and thank you for the super fast response! Sorry to have missed that!
I opened the HTTPSredirect.h, and here’s where it’s calling for that WiFiClientSecure.h library:
/* HTTPS with follow-redirect
* Created by Sujay S. Phadke, 2016
* All rights reserved.
*
*/
#include
class HTTPSRedirect : public WiFiClientSecure {
private:
const int httpsPort;
const char* redirFingerprint;
bool fpCheck = false;
bool keepAlive = true;
bool verboseInfo = false;
public:
HTTPSRedirect(const int, const char*, bool);
HTTPSRedirect(const int);
~HTTPSRedirect();
bool printRedir(const char*, const char*, const char*);
bool printRedir(String&, const char*, const char*);
String createRequest(const char*, const char*);
void fetchData(bool, bool);
};
I can’t find a library of that name to include in my sketch, and don’t quite get why I’m the only one who is apparently encountering this…
Thanks again!
Moderation is taking a long time, so sorry for these comments piling up. I’ve fixed the issue I was having previously with the missing library by upgrading the IDE, and re-assuring that the ESP8266 core was installed correctly. Since doing that, I can get your code to work just fine. However, if I try to modify it at all, I get the following on the serial window:
Connecting to script.google.com
Connection Status: 1
Certificate mis-match
Connecting to client again…
Connecting to client again…
Connecting to client again…
Connecting to client again…
Connecting to client again…
Connecting to client again…
Connecting to client again…
Connecting to client again…
Connecting to client again…
The modifications I’m attempting are to have it issue a string (“Pump 1 is ON”) when a digital pin is high, and (“Pump 1 is OFF”) when that pin goes low. I don’t need the ‘data’ varriable, so I just set the int data = 0, since I’m not doing any analog reading. I tested this in your code, and it worked, but on mine (a little more complex), it compiles, uploads, but cannot connect to the script. I can’t see anything that would matter, but this is completely new to me!
Ok mod- feel free to delete all my posts that are waiting, but do please pass through the one that said “THANKS” because I got everything working… here’s the scoop if you want to post:
I discovered the “Connecting to client again…” messages were tied to the digital pins I’m using going high or low. If they switch, I got the message. That told me that something was causing the postData function to crash. The Strings I was trying to pass were typed out with spaces. I thought that’d be okay, but once I ditched the spaces (after noticing your tag was SoilMoisture), it is working perfectly now.
I have the same problem (I upgraded IDE)
C:\Arduino\libraries\HTTPSRedirect/HTTPSRedirect.h:7:30: fatal error: WiFiClientSecure.h: No such file or directory
Please give me details
I have the same problem (I upgraded IDE)
C:\Arduino\libraries\HTTPSRedirect/HTTPSRedirect.h: fatal error: WiFiClientSecure.h: No such file or directory
Please give me details
I followed all your instructions, I am not getting the test results written to the DataLogger spreadsheet. I double checked the ss URL.
Found my mistake; everything now cool.
Thanks for letting us know. If you like share where you stumble so that others will likely avoid it.
Hello all,
I am the author of the HTTPSRedirect library. In case you have any questions, please check out the github page and open a new “issue” there. I have also launced version 2 of the library. Please see the readme for the details and use this only in future projects. Thanks.
github: http://bit.ly/1SopEug
Hi Sujay, Do you know how esp8266 read the data from google sheet? Thank you
The reading from Google sheets is part of the example code posted on Github. Please refer to it and if you have any questions, you can open an issue there.
Hi Sujay, I am not skilled enough to understand all the documentation. Could you please take a look at the following: https://forum.arduino.cc/index.php?topic=613541.0?
Thanks
I met a problem in arduino IDE
This is the problem
D:\????辣憭閱獢\source_code\data_to_google\data_to_google.ino:6:26: fatal error: ESP8266WiFi.h: No such file or directory
compilation terminated.
exit status 1
Error compiling for board Arduino Nano.
I try to download ESP8266WiFi.h , but the file still wrong
I would like to know which side of the problem or where to download it?
Thank you
I use arduino nano with ESP8266.
Hi
After modifying my esp code( increasing the delays ) and repositioning my router and esp to a clearer line of sight I am getting a much more reliable data transfer. For information it looks as if a bigger signal is emitted from the open end of the pcb antenna. If you hold the esp nodemcu with the usb connector towards you then the top right hand side of the board gives the strongest signal. You suggested sending from sheets the number of data items received. I do not know how to do that. If it is easier can you point me to a tutorial which explains the sheets script? Thanks for all your time and help.
How does the value ‘fingerprint’ get generated?
Hi Jeffrey, I searched quite a bit and bumped on to some of the sites however none of the generated fingerprints passed the test from ESP. Since it didn’t really stop me to post data to Google Sheets, I didn’t research on this topic in depth. If you find a solution, I will appreciate your time on sharing the finding. Thanks. Sorry, couldn’t help.
Hi
After following ALL your instructions I got a single stream of data regularly posted to my sheet. Thanks. I am now trying to send more than one piece of data in a stream. I assumed I could send a tag and the data where the tag is the “title” at the head of the relevant column of the sheet. So far this does not work. Can you help or point me in the direction of a suitable tutorial. Any help would be appreciated.
Sorry about the delay hengis. If you post your data in JSON format from ESP then it will be a lot easier to parse in GAS. So construct your ESP data in JSON format like this: {“tag1″:”10”, “tag2″:”9”, “tag3″:”11”} then in GAS side you could something like this:
var espData = JSON.parse(‘{“tag1″:”10”, “tag2″:”9”, “tag3″:”11”}’)
then to get the tag1 value, espData.tag1.
Hope this helps.
Thanks for your reply. I found a solution by manipulating the cells of google sheets. However I have another, interesting issue (I hope), when I try to use “pinmode output” and “digital write” within your exp8266 program I get no response. Can you fix of explain? I can control pins digitally in other programmes.
Can you post your code for digital write operation? It should be straightforward.
I am not sure what I was doing wrong but I now have the system up and running.. However an additional issue. It seems if my data stream is interrupted by say my dense head getting in the way of the transmission the whole data stream is stopped. I only seem to restart by resetting the esp source. Is there any wa to automatically resend the data if it is not received?
hengis – it’s always hard to troubleshoot when things work for the most past and at the same it doesn’t work. If I have to guess, there could be two things, 1) we might be sending too much of data (less likely as I have seen quite a heavy data been transmitted/received. 2) May be it is timing out.
If there are data missing then send known number of data points and return a count from Google App Script. If the data count doesn’t match do the corrective action…
ANir, Using the HTTPSredirect is a pleasure. But I don’t understand the multiple data solution. I have to make 2 changes : One in the ino file and one in the script, right.
In the ino : XXXXX {“sens1″:String(sens1value, “sens2″:String(sens2value, “sens3″:String(sens3value)}
but what should be XXXXX ?
I missed some brackets 🙁
XXXXX {“sens1″:String(sens1value), “sens2″:String(sens2value), “sens3″:String(sens3value)}
I have resent the serial monitor output without what I think is xtraneous information.
“Connecting to script.google.com
Connection Status: 1
Certificate mis-match
The requested URL
/1.1was not found on this server. That’s all we know.Does this help to show what I am doing wrong?
Guys, I was able to do the test post to my spreadsheet and it worked. But running the firmware side of things I get the following error, any thoughts ?
Connection Status: 1
Certificate mis-match
Error 404 (Not Found)!!1
this is followed by a lot more html code ending with:
The requested URL
/1.1was not found on this server. That’s all we know.Hi Joesgarage,
Try to print the final URL that actually posts the data to GAS in serial and copy that to a browser where you are not signed in. My first suspicion is the final URLis not in the correct format such as it has some spaces. My second guess is towards how you published things on Google side, make sure it’s “Everyone even anonymous” or something similar.
code (postData method):
String urlFinal = url + “tag=” + tag + “&value=” + String(value);Serial.println(urlFinal); // < << Add this line and then check manually client.printRedir(urlFinal, host, googleRedirHost);
As if by magic i have got it working.
ANir,
thank you for taking the time to respond. It was my fault I didn’t select “Everyone even anonymous”.
Doh !
It works – life is good. Nice job on the post BTW.
-Joe
Google wants me to login to get access to the documents.
The script works through a normal page, but not being incognito (and not logged in into my google account)
I assume my device encounters the same problems.
Even when the script is visible to everyone.
Has someone an explaination and solution for this?
Hi Jo, when you say “visible to everyone” what do you mean? Are you saying you published you GAS script as web app like the screen below?
If you haven’t done like above then you need to do that. If you did publish as above then things don’t make sense to me as it doesn’t require you to be signed into your google account.
Found it, rather simple.
I changed access already to “everyone”, but it has to be to “everyone even anonymous”.
This option however only appears when I specify my account to execute the script
Now it’s working as explained.
Thanks for this nice example.
If I leave off the
?tag=test&value=-1
at the end of my url, I get “undefined” for the Tag and Value cells.
I would have expected the
if (e == null){e={}; e.parameters = {tag:”test”,value:”-1″};}
statement to set up the parameters for me.
So then, if I set my data to
?tag=&value=
I would expect e is forced to null, but the (e == null) test above still fails.
Any thoughts on this?
if you took out ?tag=test&value=-1 and got ‘undefined’ then everything is working well. So, IMO you must be seeing right values when you put the full URL (with ?tag=test&value=-1). To force e==null, from GAS (Google App Script), Run > doGet. If you like you can also have a breakpoint after the e==null line and you can see the value while debugging within GAS. HTH
Thanks for this.
Pingback: ESP8266 sending data to Google spreadsheets - Electronics-Lab