Date and Time in Spreadsheet

[Starscream83]

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]

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 <ESP8266WiFi.h>
#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 ="[email protected]"; ///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