Google-Sheets-Logging icon indicating copy to clipboard operation
Google-Sheets-Logging copied to clipboard

Published 20 hours ago • verified publisher icon StorageB

"Error! Not connected to host." when using ISR

Open franz134 opened this issue 1 year ago • 4 comments

Hey, I'm trying to create a kind of stopwatch and for the best responsibility, I use an interrupt, from which the subroutine to send the data is called. (When I was trying the raw example it worked and I used as much as I could from the example) According to the serial monitor, the values of my variables seem to be taken along correctly, but when the payload should get posted, it didn't work, and I got this response:

Publishing data...
{"command": "insert_row", "sheet_name": "Tabellenblatt1", "values": "4,406571,408355"}
Error! Not connected to host.
Error while connecting

This is my code:

#include <Arduino.h>
#include <ESP8266WiFi.h>
#include "HTTPSRedirect.h"

#define ON_Board_LED 2
#define BUTTON_PIN 0

const char* ssid     = "myWiFi";
const char* password = "myPW";
const char *GScriptId = "my Script-ID";

// Enter command (insert_row or append_row) and your Google Sheets sheet name (default is Sheet1):
String payload_base =  "{\"command\": \"insert_row\", \"sheet_name\": \"Tabellenblatt1\", \"values\": ";
String payload = "";

// Google Sheets setup (do not edit)
const char* host = "script.google.com";
const int httpsPort = 443;
const char* fingerprint = "";
String url = String("/macros/s/") + GScriptId + "/exec";
HTTPSRedirect* client = nullptr;

// Declare variables that will be published to Google Sheets
volatile int run = 0;
volatile uint64_t time_start = 0;
volatile uint64_t time_finish = 0;

volatile bool running = false;
volatile uint64_t last_isr;

void ICACHE_RAM_ATTR ISR() {
  if (millis() - last_isr > 500) { //bounce-filter
    if (!running) {
      time_start = millis();
      running = true;
      last_isr = millis();
    } else {
      time_finish = millis();
      running = false;
      run++;
      last_isr = millis();
      sendData(run, time_start, time_finish);
    }
  }
}

void setup() {
  Serial.begin(9600);        
  delay(10);
  Serial.println('\n');
  
  // Connect to WiFi
  WiFi.begin(ssid, password);             
  Serial.print("Connecting to ");
  Serial.print(ssid); Serial.println(" ...");
  while (WiFi.status() != WL_CONNECTED) {
    delay(1000);
    Serial.print(".");
  }
  Serial.println('\n');
  Serial.println("Connection established!");  
  Serial.print("IP address:\t");
  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; i<5; i++){ 
    int retval = client->connect(host, httpsPort);
    if (retval == 1){
       flag = true;
       Serial.println("Connected");
       break;
    }
    else
      Serial.println("Connection failed. Retrying...");
  }
  if (!flag){
    Serial.print("Could not connect to server: ");
    Serial.println(host);
    return;
  }
  delete client;    // delete HTTPSRedirect object
  client = nullptr; // delete HTTPSRedirect object

  pinMode(ON_Board_LED,OUTPUT); //--> On Board LED port Direction output
  digitalWrite(ON_Board_LED, HIGH); //--> Turn off Led On Board
  attachInterrupt(digitalPinToInterrupt(BUTTON_PIN), ISR, FALLING);
}

void loop() {
  if (running) {
    digitalWrite(ON_Board_LED, LOW);
  } else {
    digitalWrite(ON_Board_LED, HIGH);
  }
}

void sendData(int run, uint64_t time_star, uint64_t time_finish) {
  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);
    }
  }
  else{
    Serial.println("Error creating client object!");
  }
  
  // Create json object string to send to Google Sheets
  payload = payload_base + "\"" + run + "," + time_start + "," + time_finish + "\"}";
  
  // Publish data to Google Sheets
  Serial.println("Publishing data...");
  Serial.println(payload);
  if(client->POST(url, host, payload)){ 
    // do stuff here if publish was successful
  }
  else{
    // do stuff here if publish was not successful
    Serial.println("Error while connecting");
  }
}

I'm grateful for any advice on a better solution, thanks

Hardware: Wemos D1 mini

franz134 avatar Nov 22 '22 15:11 franz134

You created the variables "time_start" and "time_finish" to be published:

Screenshot from 2022-11-23 15-22-04

You have a typo in your sendData function where you use "time_star" instead of "time_start":

Screenshot from 2022-11-23 15-24-21

StorageB avatar Nov 23 '22 21:11 StorageB

Thanks for the correction, I have adjusted it and had hoped that was it but I still get the same error. Since I had gotten the value in the payload even before, it seems that this typo didn't affect it at all

{"command": "insert_row", "sheet_name": "Tabellenblatt1", "values": "4,406571,408355"}

franz134 avatar Nov 23 '22 23:11 franz134

In the sendData function can you print the url and host variables to make sure they are correct before attempting to publish as that is what is used here:

if(client->POST(url, host, payload))

StorageB avatar Nov 24 '22 16:11 StorageB

Hey, I'm trying to create a kind of stopwatch and for the best responsibility, I use an interrupt, from which the subroutine to send the data is called. (When I was trying the raw example it worked and I used as much as I could from the example) According to the serial monitor, the values of my variables seem to be taken along correctly, but when the payload should get posted, it didn't work, and I got this response:

Publishing data...
{"command": "insert_row", "sheet_name": "Tabellenblatt1", "values": "4,406571,408355"}
Error! Not connected to host.
Error while connecting

This is my code:

#include <Arduino.h>
#include <ESP8266WiFi.h>
#include "HTTPSRedirect.h"

#define ON_Board_LED 2
#define BUTTON_PIN 0

const char* ssid     = "myWiFi";
const char* password = "myPW";
const char *GScriptId = "my Script-ID";

// Enter command (insert_row or append_row) and your Google Sheets sheet name (default is Sheet1):
String payload_base =  "{\"command\": \"insert_row\", \"sheet_name\": \"Tabellenblatt1\", \"values\": ";
String payload = "";

// Google Sheets setup (do not edit)
const char* host = "script.google.com";
const int httpsPort = 443;
const char* fingerprint = "";
String url = String("/macros/s/") + GScriptId + "/exec";
HTTPSRedirect* client = nullptr;

// Declare variables that will be published to Google Sheets
volatile int run = 0;
volatile uint64_t time_start = 0;
volatile uint64_t time_finish = 0;

volatile bool running = false;
volatile uint64_t last_isr;

void ICACHE_RAM_ATTR ISR() {
  if (millis() - last_isr > 500) { //bounce-filter
    if (!running) {
      time_start = millis();
      running = true;
      last_isr = millis();
    } else {
      time_finish = millis();
      running = false;
      run++;
      last_isr = millis();
      sendData(run, time_start, time_finish);
    }
  }
}

void setup() {
  Serial.begin(9600);        
  delay(10);
  Serial.println('\n');
  
  // Connect to WiFi
  WiFi.begin(ssid, password);             
  Serial.print("Connecting to ");
  Serial.print(ssid); Serial.println(" ...");
  while (WiFi.status() != WL_CONNECTED) {
    delay(1000);
    Serial.print(".");
  }
  Serial.println('\n');
  Serial.println("Connection established!");  
  Serial.print("IP address:\t");
  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; i<5; i++){ 
    int retval = client->connect(host, httpsPort);
    if (retval == 1){
       flag = true;
       Serial.println("Connected");
       break;
    }
    else
      Serial.println("Connection failed. Retrying...");
  }
  if (!flag){
    Serial.print("Could not connect to server: ");
    Serial.println(host);
    return;
  }
  delete client;    // delete HTTPSRedirect object
  client = nullptr; // delete HTTPSRedirect object

  pinMode(ON_Board_LED,OUTPUT); //--> On Board LED port Direction output
  digitalWrite(ON_Board_LED, HIGH); //--> Turn off Led On Board
  attachInterrupt(digitalPinToInterrupt(BUTTON_PIN), ISR, FALLING);
}

void loop() {
  if (running) {
    digitalWrite(ON_Board_LED, LOW);
  } else {
    digitalWrite(ON_Board_LED, HIGH);
  }
}

void sendData(int run, uint64_t time_star, uint64_t time_finish) {
  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);
    }
  }
  else{
    Serial.println("Error creating client object!");
  }
  
  // Create json object string to send to Google Sheets
  payload = payload_base + "\"" + run + "," + time_start + "," + time_finish + "\"}";
  
  // Publish data to Google Sheets
  Serial.println("Publishing data...");
  Serial.println(payload);
  if(client->POST(url, host, payload)){ 
    // do stuff here if publish was successful
  }
  else{
    // do stuff here if publish was not successful
    Serial.println("Error while connecting");
  }
}

I'm grateful for any advice on a better solution, thanks

Hardware: Wemos D1 mini

hi, do you solve your problem?

lucasromeiro avatar Feb 07 '23 21:02 lucasromeiro