Arduino Webhooks Integration

[frankr1890]

Hello,

I am trying to send data from my arduino to google sheets using a webhooks script. The following POST works when using postman:

POST /macros/s/AKfycbzOeD77Fj_qYMP9yIXcF--6VANqSGwuaoX/exec HTTP/1.1
Host: script.google.com
Content-Type: application/json
Content-Length: 36

{"value1": 10.569, "value2": 34.257}

However when I try and use the following code it does not! Does anyone knbow why this is not working. TIA

#include "ESP8266WiFi.h"

#include "arduino_secrets.h"

///////please enter your sensitive data in the Secret tab/arduino_secrets.h
char ssid[] = SECRET_SSID;  // your network SSID (name)
char pass[] = SECRET_PASS;  // your network password (use for WPA, or use as key for WEP)
int keyIndex = 0;           // your network key index number (needed only for WEP)

int status = WL_IDLE_STATUS;
// if you don't want to use DNS (and reduce your sketch size)
// use the numeric IP instead of the name for the server:
//IPAddress server(74,125,232,128);  // numeric IP for Google (no DNS)


// Initialize the Ethernet client library
// with the IP address and port of the server
// that you want to connect to (port 80 is default for HTTP):
WiFiClient client;

/* -------------------------------------------------------------------------- */
void setup() {
  /* -------------------------------------------------------------------------- */
  //Initialize serial and wait for port to open:
  Serial.begin(115200);
  while (!Serial) {
    ;  // wait for serial port to connect. Needed for native USB port only
  }

  // attempt to connect to WiFi network:
  Serial.printf("\nConnecting to: %s \n", ssid);
  WiFi.mode(WIFI_STA);
  status = WiFi.begin(ssid, pass);

  while (WiFi.status() != WL_CONNECTED) {
    Serial.print('.');
    delay(500);
  }
  Serial.printf("\n");
  // you're connected now, so print out the data:
  Serial.println("You're connected to the network");

  printWifiStatus();
}



/* -------------------------------------------------------------------------- */
void loop() {
  /* -------------------------------------------------------------------------- */
  char server[] = "script.google.com";  // name address for Google (using DNS)
  char buffer[700];
  
  //Serial.println("\nStarting connection to server...");
  // if you get a connection, report back via serial:
  if (client.connect(server, 80)) {
    Serial.println("connected to server");

    sprintf(buffer, "");
    sprintf(buffer, "%sPOST /macros/s/AKfycbzOeD77Fj_qYMP9yIXcF-hLWihcbQoMi6lU5ASjesiKIFurpjD860C-6VANqSGwuaoX/exec HTTP/1.1\n", buffer);
    sprintf(buffer, "%sHost: script.google.com\n", buffer);
    sprintf(buffer, "%sContent-Type: application/json\n", buffer);
    sprintf(buffer, "%sConnection: close\n", buffer);
    sprintf(buffer, "%sContent-Length: 36\n", buffer);
    sprintf(buffer, "%s{\"value1\": 10.569, \"value2\": 34.257}\n", buffer);
    Serial.printf("Sending:\n%s", buffer);
    client.println(buffer);


  }

  // if the server's disconnected, stop the client:
  if (!client.connected()) {
    Serial.println();
    Serial.println("disconnecting from server.");
    client.stop();
  }
  delay(2000);
}

/* -------------------------------------------------------------------------- */
void printWifiStatus() {
  /* -------------------------------------------------------------------------- */
  // print the SSID of the network you're attached to:
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // print your board's IP address:
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");
  Serial.println(ip);

  // print the received signal strength:
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.print(rssi);
  Serial.println(" dBm");
}

[J-M-L]

@frankr1890

  if (client.connect(server, 80)) {

Google Scripts require HTTPS, not plain HTTP on port 80. (Postman works because it uses HTTPS automatically)

➜ you need to use WiFiClientSecure and port 443

---

also HTTP requires each header line to end with CRLF , Using only \n

@frankr1890

    sprintf(buffer, "%sConnection: close\n", buffer);
                                        ^^
                                        |
                                       here

can cause the server to ignore or reject the request.

➜ You should use \r\n

---

Also the headers must be followed by a blank line before the body so you should have a double \r\n after the content length and before the values

---

You should not use sprintf() the way you do, why don't you just either print directly to the client without creating a huge buffer on your side or use strlcpy() if you really want to have the buffer?

➜ something like this

WiFiClientSecure client;

...

if (client.connect("script.google.com", 443)) {
  client.print("POST /macros/s/XXXXXX/exec HTTP/1.1\r\n");
  client.print("Host: script.google.com\r\n");
  client.print("Content-Type: application/json\r\n");
  client.print("Connection: close\r\n");
  client.print("Content-Length: 36\r\n");
  client.print("\r\n");
  client.print("{\"value1\": 10.569, \"value2\": 34.257}\r\n");
}

[Rintin]

@J-M-L

HTTP/1.1

Please advertise HTTP 1.0 when stuff like Transfer-Encoding: chunked is not implemented.

[J-M-L]

fair point when it matters

[frankr1890]

Thanks all, that worked a treat, just needed to include client.setInsecure();

[kenb4]

Dunno how much is intention versus luck, but println uses "\r\n"

$ rg -A 3 '\bPrint::println\(\s*(void)?\s*\)' ~/Library/Arduino15/packages
/Users/kenb4/Library/Arduino15/packages/arduino/hardware/samd/1.8.14/cores/arduino/api/Print.cpp
149:size_t Print::println(void)
150-{
151-  return write("\r\n");
152-}

/Users/kenb4/Library/Arduino15/packages/arduino/hardware/mbed_portenta/4.2.4/cores/arduino/api/Print.cpp
150:size_t Print::println(void)
151-{
152-  return write("\r\n");
153-}

/Users/kenb4/Library/Arduino15/packages/Seeeduino/hardware/samd/1.8.5/cores/arduino/Print.cpp
111:size_t Print::println(void)
112-{
113-  return write("\r\n");
114-}

/Users/kenb4/Library/Arduino15/packages/teensy/hardware/avr/1.59.0/cores/teensy4/Print.cpp
85:size_t Print::println(void)
86-{
87-	uint8_t buf[2]={'\r', '\n'};
88-	return write(buf, 2);

/Users/kenb4/Library/Arduino15/packages/teensy/hardware/avr/1.59.0/cores/teensy3/Print.cpp
79:size_t Print::println(void)
80-{
81-	uint8_t buf[2]={'\r', '\n'};
82-	return write(buf, 2);

/Users/kenb4/Library/Arduino15/packages/teensy/hardware/avr/1.59.0/cores/teensy/Print.cpp
85:size_t Print::println(void)
86-{
87-	uint8_t buf[2]={'\r', '\n'};
88-	return write(buf, 2);

/Users/kenb4/Library/Arduino15/packages/arduino/hardware/mbed_giga/4.3.1/cores/arduino/api/Print.cpp
150:size_t Print::println(void)
151-{
152-  return write("\r\n");
153-}

/Users/kenb4/Library/Arduino15/packages/arduino/hardware/megaavr/1.8.8/cores/arduino/api/Print.cpp
149:size_t Print::println(void)
150-{
151-  return write("\r\n");
152-}

/Users/kenb4/Library/Arduino15/packages/arduino/hardware/esp32/2.0.18-arduino.5/cores/esp32/Print.cpp
169:size_t Print::println(void)
170-{
171-    return print("\r\n");
172-}

/Users/kenb4/Library/Arduino15/packages/arduino/hardware/renesas_uno/1.2.2/cores/arduino/api/Print.cpp
150:size_t Print::println(void)
151-{
152-  return write("\r\n");
153-}

/Users/kenb4/Library/Arduino15/packages/arduino/hardware/mbed_opta/4.2.1/cores/arduino/api/Print.cpp
150:size_t Print::println(void)
151-{
152-  return write("\r\n");
153-}

/Users/kenb4/Library/Arduino15/packages/m5stack/hardware/esp32/2.1.1/cores/esp32/Print.cpp
169:size_t Print::println(void)
170-{
171-    return print("\r\n");
172-}

/Users/kenb4/Library/Arduino15/packages/arduino/hardware/avr/1.8.6/cores/arduino/Print.cpp
126:size_t Print::println(void)
127-{
128-  return write("\r\n");
129-}

/Users/kenb4/Library/Arduino15/packages/esp8266/hardware/esp8266/3.1.2/cores/esp8266/Print.cpp
180:size_t Print::println(void) {
181-    return print("\r\n");
182-}
183-

/Users/kenb4/Library/Arduino15/packages/esp32/hardware/esp32/3.0.7/cores/esp32/Print.cpp
169:size_t Print::println(void) {
170-  return print("\r\n");
171-}
172-

(Check out Teensy though.) So you can just have

  client.println("POST /macros/s/XXXXXX/exec HTTP/1.1");
  client.println("Host: script.google.com");
  client.println("Content-Type: application/json");
  client.println("Content-Length: 36");
  client.println("Connection: close");
  client.println();  // don't need empty string here -- note there is no `print()` with no args
  client.print(R"({"value1": 10.569, "value2": 34.257})");  // don't need "\r\n" after content 

(The raw string makes the JSON literal a little less tedious. And if you're not going to have the compiler count the Content-Length for you, it's also a little easier to spot-check that manual length is correct.)

[J-M-L]

@kenb4

println uses "\r\n"

Yes, I hesitated putting that and thought it would be clearer having the CRLF in the string as per the point I was trying to make. But you are right, that’s the better way.

[frankr1890]

Hi,

Thanks for your help but I have a similar but different question. I have a similar project but using an arduino R4 so am having to use a different library.

It sometimes connects but then after a few trys will not connect. Any ideas what I am doing wrong?

The code is below:

// This example uses an Arduino Uno together with
// a WiFi Shield to connect to shiftr.io.
//
// You can check on your device after a successful
// connection here: https://www.shiftr.io/try.
//
// by Joël Gähwiler
// https://github.com/256dpi/arduino-mqtt

#include <WiFiS3.h>
#include <WiFiSSLClient.h>

#include "arduino_secrets.h"

//Google webhooks
char host[] = "script.google.com";
int port = 443;
char url[] = "/macros/s/####/exec";

///////please enter your sensitive data in the Secret tab/arduino_secrets.h
char ssid[] = SECRET_SSID;  // your network SSID (name)
char pass[] = SECRET_PASS;  // your network password (use for WPA, or use as key for WEP)

int status = WL_IDLE_STATUS;  // the WiFi radio's status

//Calibration Data
const float V_REF = 5.0;                         // Analog reference voltage (e.g., 5V or 3.3V)
const float R_BITS = 10.0;                       // ADC resolution (bits)
const float ADC_STEPS = (1 << int(R_BITS)) - 1;  // Number of steps (2^R_BITS - 1)
const float ZERO_OFFSET = 2.515;                 // Zero Offset of Ammeter
const float VperA = .072;                        //Volts per Amp on Ammeter

const int VoltagePin = A0;  // Voltage measurement on analogue Pin 1
const int CurrentPin = A1;  // Current measurement on analogue Pin 0

long lastMillis = 0;

WiFiSSLClient client;

float readADC(int pin)  //Returns the voltage of the analouge port defined by pin
{
  unsigned int x = 0;
  float ADCValue = 0.0, Samples = 0.0, AvgADC = 0.0, Volts = 0.0;

  for (int x = 0; x < 150; x++) {  //Get 150 samples
    ADCValue = analogRead(pin);    //Read current sensor values
    Samples = Samples + ADCValue;  //Add samples together
    delay(3);                      // let ADC settle before next sample 3ms
  }
  AvgADC = Samples / 150.0;  //Taking Average of Samples and turn into voltage
  Volts = V_REF * AvgADC / ADC_STEPS;

  return Volts;
}

void printWifiData() {
  // print your board's IP address:
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");

  Serial.println(ip);

  // print your MAC address:
  byte mac[6];
  WiFi.macAddress(mac);
  char macstr[17];
  mac2str(macstr, mac);
  Serial.print("Device MAC Address: ");
  Serial.println(macstr);
}

void printCurrentNet() {
  // print the SSID of the network you're attached to:
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // print the MAC address of the router you're attached to:
  byte bssid[6];
  WiFi.BSSID(bssid);
  Serial.print("BSSID: ");
  char macstr[17];
  mac2str(macstr, bssid);
  Serial.println(macstr);

  // print the received signal strength:
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.println(rssi);

  // print the encryption type:
  byte encryption = WiFi.encryptionType();
  Serial.print("Encryption Type:");
  Serial.println(encryption, HEX);
  Serial.println();
}

void mac2str(char* macstr, byte mac[6]) {

  sprintf(macstr, "%02X:%02X:%02X:%02X:%02X:%02X", mac[0], mac[1], mac[2], mac[3], mac[4], mac[5]);
}

void setup() {
  //Initialize serial and wait for port to open:
  Serial.begin(115200);

  while (!Serial) {
    ;  // wait for serial port to connect. Needed for native USB port only
  }
  pinMode(A0, INPUT);
  pinMode(A1, INPUT);

  // attempt to connect to WiFi network:
  while (status != WL_CONNECTED) {
    Serial.print("Attempting to connect to WPA SSID: ");
    Serial.println(ssid);
    // Connect to WPA/WPA2 network:
    status = WiFi.begin(ssid, pass);

    // wait 2 seconds for connection:
    delay(2000);
  }

  // you're connected now, so print out the data:
  Serial.println("You're connected to the network");
  printCurrentNet();
  printWifiData();
  
  //Set certificate
  client.setCACert(root_ca);
}

void loop() {
  float voltage, current;
  char buffer[36];


  if (millis() - lastMillis > 5 * 1000) {
    voltage = 25 * readADC(VoltagePin) / 5;
    current = (readADC(CurrentPin) - ZERO_OFFSET) / VperA;
    lastMillis = millis();



    //Output to google sheets
    if (client.connect(host, port)) {
      Serial.println("connected to server");
      client.print("POST ");
      client.print(url);
      client.println("HTTP/1.1");
      client.print("Host: ");
      client.print(host);
      client.println("");
      client.println("Content-Type: application/json");
      client.println("Connection: close");
      client.println("Content-Length: 36");
      client.println("");
      sprintf(buffer, "");
      sprintf(buffer, "{\"voltage\":%6.2f, \"current\":%6.2f}", voltage, current);
      client.println(buffer);
    } else {
      Serial.println("Client not connected.");
      client.stop();
    }
  }
}

[Rintin]

Please check:

• not requesting HTTP 1.0
• hardcoded content length (needs to be strlen of buffer)
• probably no space for string termination in buffer
• sprintf does no buffer size check (use snprintf)
• not reading response from server

[frankr1890]

I have tried that and still no connection. see below:

// This example uses an Arduino Uno together with
// a WiFi Shield to connect to shiftr.io.
//
// You can check on your device after a successful
// connection here: https://www.shiftr.io/try.
//
// by Joël Gähwiler
// https://github.com/256dpi/arduino-mqtt

#include <WiFiS3.h>
#include <WiFiSSLClient.h>

#include "arduino_secrets.h"

//Google webhooks
char host[] = "script.google.com";
int port = 443;
char url[] = "/macros/s/AKfycbxc_m555AsnBqiVJxi3SU4xox07Ig2ZR6xPzFuuF4IML1Qi_e3-U192oXbUrFTo7L06/exec";

///////please enter your sensitive data in the Secret tab/arduino_secrets.h
char ssid[] = SECRET_SSID;  // your network SSID (name)
char pass[] = SECRET_PASS;  // your network password (use for WPA, or use as key for WEP)

int status = WL_IDLE_STATUS;  // the WiFi radio's status

//Calibration Data
const float V_REF = 5.0;                         // Analog reference voltage (e.g., 5V or 3.3V)
const float R_BITS = 10.0;                       // ADC resolution (bits)
const float ADC_STEPS = (1 << int(R_BITS)) - 1;  // Number of steps (2^R_BITS - 1)
const float ZERO_OFFSET = 2.515;                 // Zero Offset of Ammeter
const float VperA = .072;                        //Volts per Amp on Ammeter

const int VoltagePin = A0;  // Voltage measurement on analogue Pin 1
const int CurrentPin = A1;  // Current measurement on analogue Pin 0

long lastMillis = 0;

WiFiSSLClient client;

float readADC(int pin)  //Returns the voltage of the analouge port defined by pin
{
  unsigned int x = 0;
  float ADCValue = 0.0, Samples = 0.0, AvgADC = 0.0, Volts = 0.0;

  for (int x = 0; x < 150; x++) {  //Get 150 samples
    ADCValue = analogRead(pin);    //Read current sensor values
    Samples = Samples + ADCValue;  //Add samples together
    delay(3);                      // let ADC settle before next sample 3ms
  }
  AvgADC = Samples / 150.0;  //Taking Average of Samples and turn into voltage
  Volts = V_REF * AvgADC / ADC_STEPS;

  return Volts;
}

void printWifiData() {
  // print your board's IP address:
  IPAddress ip = WiFi.localIP();
  Serial.print("IP Address: ");

  Serial.println(ip);

  // print your MAC address:
  byte mac[6];
  WiFi.macAddress(mac);
  char macstr[17];
  mac2str(macstr, mac);
  Serial.print("Device MAC Address: ");
  Serial.println(macstr);
}

void printCurrentNet() {
  // print the SSID of the network you're attached to:
  Serial.print("SSID: ");
  Serial.println(WiFi.SSID());

  // print the MAC address of the router you're attached to:
  byte bssid[6];
  WiFi.BSSID(bssid);
  Serial.print("BSSID: ");
  char macstr[17];
  mac2str(macstr, bssid);
  Serial.println(macstr);

  // print the received signal strength:
  long rssi = WiFi.RSSI();
  Serial.print("signal strength (RSSI):");
  Serial.println(rssi);

  // print the encryption type:
  byte encryption = WiFi.encryptionType();
  Serial.print("Encryption Type:");
  Serial.println(encryption, HEX);
  Serial.println();
}

void mac2str(char* macstr, byte mac[6]) {

  sprintf(macstr, "%02X:%02X:%02X:%02X:%02X:%02X", mac[0], mac[1], mac[2], mac[3], mac[4], mac[5]);
}

void setup() {
  //Initialize serial and wait for port to open:
  Serial.begin(115200);

  while (!Serial) {
    ;  // wait for serial port to connect. Needed for native USB port only
  }
  pinMode(A0, INPUT);
  pinMode(A1, INPUT);

  // attempt to connect to WiFi network:
  while (status != WL_CONNECTED) {
    Serial.print("Attempting to connect to WPA SSID: ");
    Serial.println(ssid);
    // Connect to WPA/WPA2 network:
    status = WiFi.begin(ssid, pass);

    // wait 2 seconds for connection:
    delay(2000);
  }

  // you're connected now, so print out the data:
  Serial.println("You're connected to the network");
  printCurrentNet();
  printWifiData();

  //Set certificate
  client.setCACert(root_ca);
}

/* just wrap the received data up to 80 columns in the serial print*/
/* -------------------------------------------------------------------------- */
void read_response() {
  /* -------------------------------------------------------------------------- */
  uint32_t received_data_num = 0;
  while (client.available()) {
    /* actual data reception */
    char c = client.read();
    /* print data to serial port */
    Serial.print(c);
    /* wrap data to 80 columns*/
    received_data_num++;
    if (received_data_num % 80 == 0) {
      Serial.println();
    }
  }
}

void loop() {
  float voltage, current;
  char buffer[50];


  if (millis() - lastMillis > 5 * 1000) {
    voltage = 25 * readADC(VoltagePin) / 5;
    current = (readADC(CurrentPin) - ZERO_OFFSET) / VperA;
    lastMillis = millis();



    //Output to google sheets
    if (client.connect(host, port)) {
      Serial.println("connected to server");
      client.print("POST ");
      client.print(url);
      client.println("HTTP/1.0");
      client.print("Host: ");
      client.print(host);
      client.println("");
      client.println("Content-Type: application/json");
      client.println("Connection: close");
      snprintf(buffer, 50, "");
      snprintf(buffer, 50, "{\"voltage\":%6.2f, \"current\":%6.2f}", voltage, current);
      client.print("Content-Length: ");
      client.println(strlen(buffer));
      client.println("");
      client.println(buffer);
      read_response();
    }

    if (!client.connected()) {
      Serial.println();
      Serial.println("disconnecting from server.");
      client.stop();

    }
  }
}

[Rintin]

Do you get any error response from the server?

No need for a line break after sending the buffer.
There need to be a space between url and the HTTP version.

[frankr1890]

That doesn't seem to be a response. However it never connects so the HTML request is never sent

[Rintin]

Maybe something is wrong with the CA. Try with setting the client to insecure.

[kenb4]

On R4 (which has dozens of CA certs baked into the firmware, and no setInsecure) this seems to work consistently to connect on demand

#include <WiFi.h>
#include <WiFiSSLClient.h>
#include "arduino_secrets.h"

WiFiSSLClient ssl;
auto host = "script.google.com";

void setup() {
  Serial.begin(115200);
  WiFi.begin(SECRET_SSID, SECRET_PASS);  // blocking with R4
  Serial.println();
  if (WiFi.status() != WL_CONNECTED) {
    Serial.print("Failed to connected to: ");
    Serial.println(SECRET_SSID);
    for (;;);
  }
  Serial.print("Connected as: ");
  Serial.println(WiFi.localIP());
}

void loop() {
  static bool prompt = true;
  if (prompt) {
    prompt = false;
    Serial.println("Press Enter to connect");
  }
  if (Serial.available() <= 0) {
    delay(100);
    return;
  }
  prompt = true;
  Serial.println("Connecting to host....");
  Serial.readStringUntil('\n');
  if (ssl.connect(host, 443)) {
    ssl.println("GET /.well-known/token_endpoint HTTP/1.0");
    ssl.println();
    uint8_t buf[80];
    while (ssl.connected()) {
      if (ssl.available() > 0) {
        auto len = ssl.read(buf, sizeof(buf));
        Serial.write(buf, len);
      }
    }
    Serial.println();
    ssl.stop();
  } else {
    Serial.print("Failed to connect to: ");
    Serial.println(host);
  }
}

Note that the actual /.well-known path is irrelevant -- the question is whether it connects or not. A 404 response still means it worked.