0

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;
}
  • Why the 4s delay at the end of loop? – chrisl Sep 25 at 5:41
  • Actually that code is copied from a website, I don't know the specific function of that delay of 4 second – Ansari Aquib Sep 25 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.