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 '19 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 '19 at 5:59
0

I think you'll need to modify your Google Script for that Sheet so that whatever function you are invoking to GET row data takes in an additional parameter for the row value.

Then just add the Javascript logic within that function for retrieving the data at that passed-in row index to be returned.

Does that make sense?

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.