Good Morning,
The backstory on my project is that I'm trying to build a hydroponic garden and would like to have my sensor data posted to a spreadsheet at regular intervals.
The first challenge I ran into was that the TDS sensor I had picked up didn't want to work when installed on the NodeMCU 1.0 (ESP8266) I wanted to use. As a workaround I connected the TDS sensor to an Arduino Uno (that can read the sensor without issue). The Uno is Serial connected to the NodeMCU, in order to upload the data to the spreadsheet.
I've noticed a trend where the data will upload to the spreadsheet normally for about 20 rows and then it is like a buffer gives out or something, and instead of getting results like ###.## they start skewing, and the decimal starts roaming.
181.43 ppm
183.15 ppm
183.15 ppm
181.43 ppm
81.431 ppm
.43181 ppm
3181.4 ppm
81.431 ppm
.15181 ppm
3181.4 ppm
etc
Looking at the Serial Monitors:
On the NodeMCU, the tdsTransfer value is showing the same skewed values (above) that are being posted to the spreadsheet, however when I look at the Arduino Uno's tdsTransfer data, it is still posting correctly formatted data. (###.##)
(This is why I think the problem is with the NodeMCU's code, and not with the Uno)
Below is the sketch that I'm using for the NodeMCU. ( I'm repurposing a tutorial that I found for posting Temperature and Humidity data to a google sheet)
#include <ESP8266WiFi.h>
#include "HTTPSRedirect.h"
#include "DebugMacros.h"
int h ; //int will ned to change to float for mustard? dstTransfer
int t ; //int will ned to change to float for mustard?
char tdsTransfer[7]; // 6+1 characters reserved for tdsTransfer
String sheetHumid = "";
String sheetTemp = "";
const char* ssid = "NETWORK"; //replace with our wifi ssid
const char* password = "PASSWORD"; //replace with your wifi password
const char* host = "script.google.com";
const char *GScriptId = "SCRIPT"; // Replace with your own google script id
const int httpsPort = 443; //the https port is same
// echo | openssl s_client -connect script.google.com:443 |& openssl x509 -fingerprint -noout
const char* fingerprint = "";
//const uint8_t fingerprint[20] = {};
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"; // Write to Cell A continuosly
//replace with sheet name not with spreadsheet file name taken from google
String payload_base = "{\"command\": \"appendRow\", \
\"sheet_name\": \"Data\", \
\"values\": ";
String payload = "";
HTTPSRedirect* client = nullptr;
// used to store the values of free stack and heap before the HTTPSRedirect object is instantiated
// so that they can be written to Google sheets upon instantiation
void setup() {
delay(1000);
Serial.begin(9600);
Serial.println();
Serial.print("Connecting to wifi: ");
Serial.println(ssid);
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());
// Use HTTPSRedirect class to create a new TLS connection
client = new HTTPSRedirect(httpsPort);
client->setInsecure();
client->setPrintResponseBody(true);
client->setContentTypeHeader("application/json");
Serial.print("Connecting to ");
Serial.println(host); //try to connect with "script.google.com"
// Try to connect for a maximum of 5 times then exit
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...");
}
if (!flag) {
Serial.print("Could not connect to server: ");
Serial.println(host);
Serial.println("Exiting...");
return;
}
// Finish setup() function in 1s since it will fire watchdog timer and will reset the chip.
//So avoid too many requests in setup()
Serial.println("\nWrite into cell 'A1'");
Serial.println("------>");
// fetch spreadsheet data
client->GET(url, host);
Serial.println("\nGET: Fetch Google Calendar Data:");
Serial.println("------>");
// fetch spreadsheet data
client->GET(url2, host);
Serial.println("\nStart Sending Sensor Data to Google Spreadsheet");
// delete HTTPSRedirect object
delete client;
client = nullptr;
}
void loop() {
Serial.readBytes (tdsTransfer,6); // read first 6 bytes
h = 123456 ; // Reading temperature or humidity takes about 250 milliseconds!
t = 999999 ; // Read temperature as Celsius (the default)
if (isnan(h) || isnan(t)) { // Check if any reads failed and exit early (to try again).
Serial.println(F("Failed to read from DHT sensor!"));
return;
}
//*-* This is where the data is put together
// *-* for upload to spreadsheet
Serial.print("Humidity: "); Serial.print(h);
sheetHumid = String(h) + String("%"); //convert integer humidity to string humidity
Serial.print("% TDS: "); Serial.print(tdsTransfer); Serial.println(" uploaded ");
sheetTemp = String(tdsTransfer) + String(" ppm");
static int error_count = 0;
static int connect_count = 0;
const unsigned int MAX_CONNECT = 20;
static bool flag = false;
payload = payload_base + "\"" + sheetTemp + "," + sheetHumid + "\"}";
if (!flag) {
client = new HTTPSRedirect(httpsPort);
client->setInsecure();
flag = true;
client->setPrintResponseBody(true);
client->setContentTypeHeader("application/json");
}
if (client != nullptr) {
if (!client->connected()) {
client->connect(host, httpsPort);
client->POST(url2, host, payload, false);
Serial.print("Sent : "); Serial.println("Temp and Humid");
}
}
else {
DPRINTLN("Error creating client object!");
error_count = 5;
}
if (connect_count > MAX_CONNECT) {
connect_count = 0;
flag = false;
delete client;
return;
}
// Serial.println("GET Data from cell 'A1':");
// if (client->GET(url3, host)) {
// ++connect_count;
// }
// else {
// ++error_count;
// DPRINT("Error-count while connecting: ");
// DPRINTLN(error_count);
// }
Serial.println("POST or SEND Sensor data to Google Spreadsheet:");
if (client->POST(url2, host, payload)) {
;
}
else {
++error_count;
DPRINT("Error-count while connecting: ");
DPRINTLN(error_count);
}
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);
}
delay(3000); // keep delay of minimum 2 seconds for google sheet
}Any and All assistance will be greatly appreciated!
Many Thanks in Advance!
DtB
