I want to read and writ data on Google sheets using ESP8266 WIfi Nodemcu and I'm able to do it but the time interval between two writing row is 3 seconds which is too much. I can read data but I am able to read only the first row not any other rows i am not able to read even changing the URL parameters.
Here is My Code -
#include "ESP8266WiFi.h"
#include "HTTPSRedirect.h"
#include "DebugMacros.h"
// Fill ssid and password with your network credentials
const char* ssid = "Leobotics";
const char* password = "leobotics123";
int sheetHumid = 1;
int sheetTemp = 2;
int sheetaqu = 3;
String readaqu = "";
const char* host = "script.google.com";
// Replace with your own script id to make server side changes
const char *GScriptId = "AKfycbyu_JnX7F29Ea5cZUy7HdsFbv9W5UmedmNbhTwt5tznwRUiQhYS";
const int httpsPort = 443;
// echo | openssl s_client -connect script.google.com:443 |& openssl x509 -fingerprint -noout
const char* fingerprint = "ED:83:75:6B:83:FE:01:DE:BA:72:84:05:71:FA:CE:54:FB:6E:23:CA";
//const uint8_t fingerprint[20] = {};
// Write to Google Spreadsheet
String url = String("/macros/s/") + GScriptId + "/exec?value=Hello";
// Fetch Google Calendar events for 1 week ahead
String url2 = String("/macros/s/") + GScriptId + "/exec?cal";
// Read from Google Spreadsheet
String url3 = String("/macros/s/") + GScriptId + "/exec?read";
String payload_base = "{\"command\": \"appendRow\", \
\"sheet_name\": \"Sheet1\", \
\"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
unsigned int free_heap_before = 10;
unsigned int free_stack_before = 20;
void setup() {
Serial.begin(115200);
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 for a maximum of 5 times
bool flag = false;
for (int i=0; iconnect(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;
}
}
void loop() {
datasend();
static int error_count = 0;
static int connect_count = 0;
const unsigned int MAX_CONNECT = 20;
static bool flag = false;
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);
payload = payload_base + "\"" + sheetaqu + "," + sheetTemp + "," + sheetHumid + "\"}";
client->POST(url2, host, payload, false);
}
}
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;
payload = payload_base + "\"" + readaqu + "," + sheetaqu + "," + sheetTemp + "," + sheetHumid + "\"}";
Serial.println("RECIEVED DATA IS GIVEN BELOW ");
Serial.println(" ");
Serial.println("1");
Serial.println(readaqu);
Serial.println("2");
Serial.println(sheetaqu);
Serial.println("3");
Serial.println(sheetTemp);
Serial.println("4");
Serial.println(sheetHumid);
Serial.println(" ");
Serial.println("RECEIVE DATA PRINT IS OVER ");
sheetaqu ++;
sheetTemp ++;
sheetHumid ++;
}
else{
++error_count;
DPRINT("Error-count while connecting: ");
DPRINTLN(error_count);
}
Serial.println("POST append memory data to spreadsheet:");
payload = payload_base + "\"" + sheetaqu + "," + sheetTemp + "," + sheetHumid + "\"}";
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;
}
delay(4000);
}
void datasend()
{
// Send memory data to Google Sheets
payload = payload_base + "\"" + sheetaqu + "," + sheetTemp + "," + sheetHumid + "\"}";
client->POST(url2, host, payload, false);
payload = payload_base + "\"" + sheetaqu + "," + sheetTemp + "," + sheetHumid + "\"}";
client->POST(url2, host, payload, false);
// Note: setup() must finish within approx. 1s, or the the watchdog timer
// will reset the chip. Hence don't put too many requests in setup()
// ref: https://github.com/esp8266/Arduino/issues/34
Serial.println("\nGET: Write into cell 'A1'");
Serial.println("=========================");
// fetch spreadsheet data
client->GET(url, host);
// Send memory data to Google Sheets
payload = payload_base + "\"" + sheetaqu + "," + sheetTemp + "," + sheetHumid + "\"}";
client->POST(url2, host, payload, false);
Serial.println("\nGET: Fetch Google Calendar Data:");
Serial.println("================================");
// fetch spreadsheet data
client->GET(url2, host);
// Send memory data to Google Sheets
payload = payload_base + "\"" + sheetaqu + "," + sheetTemp + "," + sheetHumid + "\"}";
client->POST(url2, host, payload, false);
Serial.println("\nSeries of GET and POST requests");
Serial.println("===============================");
// delete HTTPSRedirect object
delete client;
client = nullptr;
}
