Hello,
I'm trying to make a Temp and humidity controller to send results to a small webserver but also to an online excel spreadsheet for history.
i scrambled together some code from different guides and get this error:
POST or SEND Sensor data to Google Spreadsheet:
<!DOCTYPE html><html><head><link rel="shortcut icon" href="//ssl.gstatic.com/docs/script/images/favicon.ico"><title>Fejl</title><style type="text/css" nonce="nBcZvYHA/O/Hx209u03Isg">body {background-color: #fff; margin: 0; padding: 0;}.errorMessage {font-family: Arial,sans-serif; font-size: 12pt; font-weight: bold; line-height: 150%; padding-top: 25px;}</style></head><body style="margin:20px"><div><img alt="Google Apps Script" src="//ssl.gstatic.com/docs/script/images/logo.png"></div><div style="text-align:center;font-family:monospace;margin:50px auto 0;max-width:600px">TypeError: Cannot read property 'getLastRow' of null (linje 74, fil "Kode")</div></body></html>
Does anyone know how i should continue from here? When u take the URL (ssl.gstatic.com/docs/script/images/favicon.ico) i see that its a picture of an arrow, so i guess the communication between the Wemos and google is wrong, but i don't know where or how to fix it.
The full code:
/*********
* PROJECT
Rui Santos
Complete project details at https://randomnerdtutorials.com/esp8266-dht11dht22-temperature-and-humidity-web-server-with-arduino-ide/
*********/
// Import required libraries
#include <Arduino.h>
#include <ESP8266WiFi.h>
#include <Hash.h>
#include <ESPAsyncTCP.h>
#include <ESPAsyncWebServer.h>
#include <Adafruit_Sensor.h>
#include <DHT.h>
#include <HTTPSRedirect.h>
// Replace with your network credentials
const char* ssid = "PrettyFlyForAWIFI";
const char* password = "Nogetmed69";
#define DHTPIN 5 // Digital pin connected to the DHT sensor
// Uncomment the type of sensor in use:
//#define DHTTYPE DHT11 // DHT 11
#define DHTTYPE DHT22 // DHT 22 (AM2302)
//#define DHTTYPE DHT21 // DHT 21 (AM2301)
//Sheets
float h;
float t;
String sheetHumid = "";
String sheetTemp = "";
const char* host = "script.google.com";
const char *GScriptId = "AKfycbzpUQ3Gu0xaUD2qag9HKV7V2RRi3gKl8zAK-B0MTg"; // 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 = "";
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\", \
\"ESP8266_Temp_Logger\": \"TempSheet\", \
\"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
//SHEETS
DHT dht(DHTPIN, DHTTYPE);
//WEBSERVER
// Create AsyncWebServer object on port 80
AsyncWebServer server(80);
// Generally, you should use "unsigned long" for variables that hold time
// The value will quickly become too large for an int to store
unsigned long previousMillis = 0; // will store last time DHT was updated
// Updates DHT readings every 10 seconds
const long interval = 10000;
const char index_html[] PROGMEM = R"rawliteral(
<!DOCTYPE HTML><html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.2/css/all.css" integrity="sha384-fnmOCqbTlWIlj8LyTjo7mOUStjsKC4pOpQbqyi7RrhN7udi9RwhKkMHpvLbHG9Sr" crossorigin="anonymous">
<style>
html {
font-family: Arial;
display: inline-block;
margin: 0px auto;
text-align: center;
}
h2 { font-size: 3.0rem; }
p { font-size: 3.0rem; }
.units { font-size: 1.2rem; }
.dht-labels{
font-size: 1.5rem;
vertical-align:middle;
padding-bottom: 15px;
}
</style>
</head>
<body>
<h2>ESP8266 DHT Server</h2>
<p>
<i class="fas fa-thermometer-half" style="color:#059e8a;"></i>
<span class="dht-labels">Temperature</span>
<span id="temperature">%TEMPERATURE%</span>
<sup class="units">°C</sup>
</p>
<p>
<i class="fas fa-tint" style="color:#00add6;"></i>
<span class="dht-labels">Humidity</span>
<span id="humidity">%HUMIDITY%</span>
<sup class="units">%</sup>
</p>
</body>
<script>
setInterval(function ( ) {
var xhttp = new XMLHttpRequest();
xhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
document.getElementById("temperature").innerHTML = this.responseText;
}
};
xhttp.open("GET", "/temperature", true);
xhttp.send();
}, 10000 ) ;
setInterval(function ( ) {
var xhttp = new XMLHttpRequest();
xhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
document.getElementById("humidity").innerHTML = this.responseText;
}
};
xhttp.open("GET", "/humidity", true);
xhttp.send();
}, 10000 ) ;
</script>
</html>)rawliteral";
// Replaces placeholder with DHT values
String processor(const String& var) {
//Serial.println(var);
if (var == "TEMPERATURE") {
return String(t);
}
else if (var == "HUMIDITY") {
return String(h);
}
return String();
}
//WEBSERVER STOP
void setup() {
// Serial port for debugging purposes
Serial.begin(115200);
dht.begin();
// Connect to Wi-Fi
WiFi.begin(ssid, password);
Serial.println("Connecting to WiFi");
while (WiFi.status() != WL_CONNECTED) {
delay(1000);
Serial.println(".");
}
//SHEETS
// 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"
//CONNECTION
// 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;
}
// Print ESP8266 Local IP Address
Serial.println(WiFi.localIP());
//SERVER
// Route for root / web page
server.on("/", HTTP_GET, [](AsyncWebServerRequest * request) {
request->send_P(200, "text/html", index_html, processor);
});
server.on("/temperature", HTTP_GET, [](AsyncWebServerRequest * request) {
request->send_P(200, "text/plain", String(t).c_str());
});
server.on("/humidity", HTTP_GET, [](AsyncWebServerRequest * request) {
request->send_P(200, "text/plain", String(h).c_str());
});
// Start server
server.begin();
//SHEETS
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() {
unsigned long currentMillis = millis();
if (currentMillis - previousMillis >= interval) {
// save the last time you updated the DHT values
previousMillis = currentMillis;
// Read temperature as Celsius (the default)
float newT = dht.readTemperature();
// Read temperature as Fahrenheit (isFahrenheit = true)
//float newT = dht.readTemperature(true);
// if temperature read failed, don't change t value
if (isnan(newT)) {
Serial.println("Failed to read from DHT sensor!");
}
else {
t = newT;
Serial.print("Temperature: "); Serial.print(t); Serial.println("°C ");
}
// Read Humidity
float newH = dht.readHumidity();
// if humidity read failed, don't change h value
if (isnan(newH)) {
Serial.println("Failed to read from DHT sensor!");
}
else {
h = newH;
Serial.print("Humidity: "); Serial.print(h); Serial.println("%");
}
}
sheetHumid = String(h) + String("%"); //convert integer humidity to string humidity
sheetTemp = String(t) + String("°C");
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 {
Serial.print("Error creating client object!");
error_count = 5;
}
if (connect_count > MAX_CONNECT) {
connect_count = 0;
flag = false;
delete client;
return;
}
Serial.println("POST or SEND Sensor data to Google Spreadsheet:");
if (client->POST(url2, host, payload)) {
;
}
else {
++error_count;
Serial.print("Error-count while connecting: "); Serial.println(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 as dht allow reading after 2 seconds interval and also for google sheet
}