electronicsguy / ESP8266

ESP8266 Projects
242 stars 183 forks source link

Date and Time in Spreadsheet #74

Open Starscream83 opened 5 years ago

Starscream83 commented 5 years ago

Hi, Could you please add Date and Time into the gscript whenever a new row of data is appemded? I know it is possible to get time on the esp8266 over NTP request and send it to google sheets but for low energy applications it is better to not make this request every time the esp8266 wakes up.

Alpol2019 commented 5 years ago

I used working scetch and followed the instructions of the author. It may well be a mistake on my side. I am attaching the source code and debug. I will be especially grateful to you if you find error

include

include "HTTPSRedirect.h"

include "DebugMacros.h"

// Fill ssid and password with your network credentials const char ssid = "--------------"; const char password = "--------"; //------------------------------------- HTTPSRedirect client = nullptr; //-------------------------------------- const char host = "script.google.com"; const char googleRedirHost = "script.googleusercontent.com"; // Replace with your own script id to make server side changes // New spreadsheetID july 2019 //const char GScriptId = "AKfycbzmxHIksfDsmQOXVxHq9Fd67KsNMWtMJF_JuNutvoQv14T46co"; // scriptID //AKfycbwrFY95-vkOstegQKpdIdv1mjLgjyNQvGH-lZ0JcxzuS-QB1yY //const char *GScriptId = "--------------------------------------------"; int cn = 0; const int httpsPort = 443;

// echo | openssl s_client -connect script.google.com:443 |& openssl x509 -fingerprint -noout //const char fingerprint = ""; //const uint8_t fingerprint[20] = {}; //------------------------------------------------------------------ //const char fingerprint ="55 5F 55 54 67 1B 98 31 11 A8 45 55 95 D4 CE 3F 80 90 5D A7"; ///// replace const char* fingerprint ="F0 5C 74 77 3F 6B 25 D7 3B 66 4D 43 2F 7E BC 5B E9 28 86 AD"; ///// replace void setup() { Serial.begin(115200); Serial.println(); Serial.print("Connecting to wifi: "); Serial.println(ssid); // flush() is needed to print the above (connecting...) message reliably, // in case the wireless connection doesn't go through Serial.flush();

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 TLS connection

// HTTPSRedirect client(httpsPort); //----------------------------------------------- // 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);

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

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

Serial.flush(); // if (client.verify(fingerprint, host)) { if (client->verify(fingerprint, host)) { Serial.println("Certificate match."); } else { Serial.println("Certificate mis-match"); }

}

//funtion URL String urlpacket( String GScriptId,String Address, String Message , String subject) {

//replace - remplazo " " for "+" para envio atravez de url, for send via url
Message.replace(" ", "+"); subject.replace(" ", "+");

String url = String("/macros/s/") + GScriptId + "/exec?"+ "Address=" + Address + "&Message=" + Message + "&subject="+ subject + "&sendMail=ok";
return url; } //------------------------------------------------------------ void loop() { //HTTPSRedirect client(httpsPort); //----------------------------------------------- // Use HTTPSRedirect class to create a new TLS connection client = new HTTPSRedirect(httpsPort); client->setInsecure(); client->setPrintResponseBody(true); client->setContentTypeHeader("application/json"); //------------------------------------------------- // if (!client.connected()) if (!client->connected()) { // connect to the gmail server on port 443 Serial.println("Connection with Gmail failed"); // client->stop(); // return -1; // break; } // client.connect(host, httpsPort); client->connect(host, httpsPort); Serial.println("run..."); String Address ="----------@gmail.com"; ///Email address String subject ="Send Mail from esp8266-01 (Alex Pol) "; String Message ="Alarm - Main door is open"; //--------------------------------------------------------
if (cn==10) {
//Counter = 10 send Mail //// Contador = 10 envia correo // Write to Google Spreadsheet
String urlout= urlpacket(GScriptId,Address,Message,subject); // client.printRedir(urlout, host, googleRedirHost); client->GET(urlout,host); Serial.println("Mail sending to "+ Address); Serial.println("==============================================================="); } cn++; Serial.println(cn);
delay(5000); //delay 5 sec. // delay(20000); //delay }


WiFi connected IP address: 192.168.0.20 Connecting to script.google.com Certificate match. Connection with Gmail failed

Abort called

stack>>>

ctx: cont sp: 3ffffcf0 end: 3fffffc0 offset: 01b0 3ffffea0: 00000000 00000000 3fff7554 4020f1c8
3ffffeb0: 3ffee8b0 3ffe892d 3fff7554 402051eb
3ffffec0: 00000000 3fffff20 40206e2c 3fffefa0
3ffffed0: 00000000 00000000 00000000 402014df
3ffffee0: 00000d50 3fff777c 3fff7554 40204059
3ffffef0: 000001bb 3fff7554 3ffeeef4 3ffeef6c
3fffff00: 000001bb 3fff7554 3ffe892d 3ffeef6c
3fffff10: 000001bb 3fff7554 3ffe892d 402053fa
3fffff20: 402077a0 0e01d9ac 3ffeeef4 402063c0
3fffff30: 3ffeeebc 3ffe84e0 3ffeeef4 40202fc1
3fffff40: 3ffe85e2 3fff0514 3ffe892d 40206349
3fffff50: 3ffe876a 00000000 3ffeeef4 40206374
3fffff60: 402077a0 3ffeeebc 3ffeeef4 402063c0
3fffff70: 3ffe84e0 3ffeeebc 3ffeeef4 40202e2e
3fffff80: 402077a0 1400a8c0 feefeffe feefeffe
3fffff90: feefeffe feefeffe feefeffe 3ffeef6c
3fffffa0: 3fffdad0 00000000 3ffeef3c 40206ed8
3fffffb0: feefeffe feefeffe 3ffe8518 401009a5
<<<stack<<<

last failed alloc call: 4020F1F8(16709)

ets Jan 8 2013,rst cause:4, boot mode:(1,6)

wdt reset

ets Jan 8 2013,rst cause:4, boot mode:(1,6)

wdt reset