Arduino HTTPS interface with GET

OK, Looking for some help on an Arduino / ESP8266 project.

I’m still trying to learn the coding side of things and have a two sets of code, the example code that I got from the web, and of course my code based on that.

In the code, I’m using an HTTPS GET command to interface with a Google Sheets spreadsheet that will be logging temperatures and providing a set point.

I understand how the formatting of the strings should be, and the google script is responding when I type the strings in manually, but for some reason, when I use the Arduino to send the string, it doesn’t work… on my string… on the example code, it works fine (of course!).

Is there anyone who has done some Arduino coding with the ESP8266 using GET (or POST for that matter) that might be interested in taking a look at the code with me to see what I’m missing?

-Ian

Example code? As in ESP8266 example code? Example code that interacts with Google Sheets?

HTTPS on Arduino is a pain in the dick… post your code

Unfortunately, the code doesn’t show well here in the forum, but I’ll give it a go:

Arduino Code:

/

/-----------------------------------------------------
//  All the setup stuff                              --
//-----------------------------------------------------
  #include <ESP8266WiFi.h>
  #include "HTTPSRedirect.h"
  #include "DebugMacros.h"

  const char* ssid = "";
  const char* password = "";
  const char* host = "script.google.com";
    /* 
    // Replace with your own script id to make server side changes
    https://script.google.com/macros/s/<GSCRIPTID>/exec?setpoint=12345&temp=54321&relayStatus=testingString
    https://script.google.com/macros/s/<GSCRIPTID>/exec?read=1
    //-- Test Code From API ------------------------------
    */
  const char *GScriptId = "<GSCRIPTID>";
  const int httpsPort = 443;
  const char* fingerprint = "";
  String urlToGoogle = String("/macros/s/") + GScriptId + "/exec?";
  String payload = "";
  HTTPSRedirect* client = nullptr;






void setup() {
  Serial.begin(115200);
  Serial.flush();
  // Start the Wifi
    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; i<5; i++){
      int retval = client->connect(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;
    }

    
    // Send something to Google Sheets
    payload = "setpoint=5555&temp=6666&relayStatus=testingString";
    String url = urlToGoogle + payload;
    Serial.println("the URL is:"); 
    Serial.println(url);
    client->GET(url, host, 1);
    

    delay(5000);

    
    // fetch spreadsheet data
    payload = "read=1";
    url = urlToGoogle + payload;
    Serial.println("the URL is:");
    Serial.println(url);
    client->GET(url, host, false);
    String returnedValue = client->getResponseBody();
    Serial.println(returnedValue.toInt() +5);


    delete client;
    client = nullptr;
    
}

void loop() {
  // put your main code here, to run repeatedly:
  Serial.println("Waiting...");
  delay(10000);
}

The Google code:

function doGet(e){
  Logger.log("--- doGet ---");
 
 var read =        e.parameters.read,
     setpoint =    e.parameters.setpoint,
     temp =        e.parameters.temp,
     relayStatus = e.parameters.relayStatus,
     ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/<GoogleSheetID>/edit"),
     sheet = ss.getSheetByName("Sheet1");
 
  try {
 
    // this helps during debuggin
    if (e == null){e={}; e.parameters = {setpoint:"12",value:"-1",relayStatus:"testing"};}
     
 //   setpoint = e.parameters.setpoint;
 //   temp = e.parameters.temp;
 //   relayStatus = e.parameters.relayStatus;
    
    if (read !== undefined){
      return ContentService.createTextOutput(sheet.getRange('B1').getValue());
    }
    if (setpoint !== undefined){
      save_data(setpoint, temp, relayStatus);
      return ContentService.createTextOutput("Wrote:\n  Setpoint: " + setpoint + "\n  temp: " + temp + "\n  relayStatus: " + relayStatus);
    }
   }catch(error) { 
    Logger.log(error);    
    return ContentService.createTextOutput("oops...." + error.message 
                                            + "\n" + new Date() 
                                            + "\nsetpoint: " + setpoint +
                                            + "\ntemp: " + temp
                                            + "\nrelayStatus: " + relayStatus);
  }  
}
 
// Method to save given data to a sheet
function save_data(setpoint, temp, relayStatus){
  Logger.log("--- save_data ---"); 
 
 
  try {
    var dateTime = new Date();
 
    // Paste the URL of the Google Sheets starting from https thru /edit
    // For e.g.: https://docs.google.com/..../edit 
    var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/<Google Sheet ID/edit");
    var dataSheet = ss.getSheetByName("Sheet1");
 
 
    // Get last edited row from DataLogger sheet
    var row = dataSheet.getLastRow() + 1;
 
 
    // Start Populating the data
    dataSheet.getRange("A" + row).setValue(dateTime); // datetime
    dataSheet.getRange("B" + row).setValue(setpoint); // setpoint
    dataSheet.getRange("C" + row).setValue(temp); // temp
    dataSheet.getRange("D" + row).setValue(relayStatus); // relay status (heating / cooling / off)
 
 
  }
 
  catch(error) {
    Logger.log(JSON.stringify(error));
  }
 
  Logger.log("--- save_data end---"); 
}

When I paste the strings into a web browser, I get exactly what I need

https://script.google.com/macros/s//exec?setpoint=12345&temp=54321&relayStatus=testingString

populates 12345, 54321, and testingString into their cells in the worksheet and returns that it was added.
https://script.google.com/macros/s//exec?read=1

returns what is in cell A2 (currently 85).

Furthermore,

The example code I based this on is essentially the same as far as the GET commands. I can post that if y’all are interested, but not sure it’ll really help?

When I run the Arduino, because I have the GET line with a 1 (True) it does a serial print with the return… which gets me this:

......
WiFi connected
IP address: 
192.168.0.18
Connecting to script.google.com
the URL is:
/macros/s/<GSCRIPTID>/exec?setpoint=5555&temp=6666&relayStatus=testingString

<!DOCTYPE html>
<html lang="en">
  <head>
  <meta charset="utf-8">
  <meta content="width=300, initial-scale=1" name="viewport">
  <meta name="google-site-verification" content="XXXXXXXXX"
  <title>Sign in - Google Accounts</title>
  <style>
  @font-face {
  font-family: 'Open Sans';
  font-style: normal;

And about 20 pages worth of what I believe is JSON response.

that finishes with this:

</script>
  <script nonce="aWc3RJ2qSud2akV8wQBFkg">
  if (gaia.ps.hasPrefilledIdentifier) {
  var form = document.getElementById('gaia_loginform');
  if (form) {
  form.submit();
  }
  }
  </script>
<script nonce="<StringOfChars>">
  (function(){
  gaia_onLoginSubmit = function() {
  try {
  gaia.loginAutoRedirect.stop();
  } catch (err) {
  // do not prevent form from being submitted
  }
  try {
  document.bg.invoke(function(response) {
  document.getElementById('bgresponse').value = response;
  });
  } catch (err) {
  document.getElementById('bgresponse').value = '';
  }
  return true;
  }
  document.getElementById('gaia_loginform').onsubmit = gaia_onLoginSubmit;
  var signinButton;
  signinButton = document.getElementById('next');
  gaia_attachEvent(window, 'load', function(){
  gaia_scrollToElement(signinButton);
  });
  })();
</script>
  <script nonce="<StringOfChars>">var e=this,g=function(a,c){a=a.split(".");var b=e;a[0]in b||!b.execScript||b.execScript("var "+a[0]);for(var d;a.length&&(d=a.shift());)a.length||void 0===c?b[d]?b=b[d]:b=b[d]={}:b[d]=c};var h=function(){try{return new XMLHttpRequest}catch(b){for(var a=["MSXML2.XMLHTTP.6.0","MSXML2.XMLHTTP.3.0","MSXML2.XMLHTTP","Microsoft.XMLHTTP"],c=0;c<a.length;c++)try{return new ActiveXObject(a[c])}catch(d){}}return null};g("gaia.ajax.newXmlHttpRequest",h);var k=function(){this.a=h();this.parameters={}};g("gaia.ajax.XmlHttpFormRequest",k);
k.prototype.send=function(a,c){var b=[],d;for(d in this.parameters)b.push(d+"="+encodeURIComponent(this.parameters[d]));b=b.join("&");var f=this.a;f.open("POST",a,!0);f.setRequestHeader("Content-type","application/x-www-form-urlencoded");f.onreadystatechange=function(){4==f.readyState&&c({status:f.status,text:f.responseText})};f.send(b)};k.prototype.send=k.prototype.send;
k.prototype.h=function(a,c,b){var d=this.a;d.open("POST",a,!0);d.setRequestHeader("Content-type","application/json");d.onreadystatechange=function(){4==d.readyState&&b({status:d.status,text:d.responseText})};d.send(c)};k.prototype.sendJson=k.prototype.h;k.prototype.get=function(a,c){var b=this.a;b.open("GET",a,!0);b.onreadystatechange=function(){4==b.readyState&&c({status:b.status,text:b.responseText})};b.send()};k.prototype.get=k.prototype.get;var l=/\s*;\s*/,m=function(){if(!document.cookie)return"";for(var a=document.cookie.split(l),c=0;c<a.length;c++){var b=a[c];b=b.replace(/^\s+/,"");b=b.replace(/\s+$/,"");if(0==b.indexOf("APISID="))return b.substr(7)}return""};var n=null,p=function(a,c){this.g=a;this.f=c;this.c=m();this.b=!1},q=function(){var a=n,c=m();c==a.c||a.b||(a.c=c,(new k).get(a.f,function(b){var a=n;b&&b.status&&200==b.status&&"OK"==b.text&&(a.a&&clearInterval(a.a),a.b||(window.location=a.g))}))};p.prototype.start=function(a){if(this.a)return!1;this.a=setInterval(function(){q()},a);return!0};g("gaia.loginAutoRedirect.start",function(a,c,b){if(n||!b||!c||0>=a)return!1;n=new p(c,b);return n.start(a)});
g("gaia.loginAutoRedirect.stop",function(){var a=n;a.b=!0;a.a&&(clearInterval(a.a),a.a=null)});
</script>
  <script type="text/javascript" nonce="aWc3RJ2qSud2akV8wQBFkg">
  gaia.loginAutoRedirect.start(5000,
  'https:\x2F\x2Faccounts.google.com\x2FServiceLogin?continue=https%3A%2F%2Fscript.google.com%2Fmacros%2Fs%2FAKfycbzzYDI73AJY5KytE_CY3sF7_fyIZH0lz_jmms98cjUmexcOGW_4%2Fexec%3Fsetpoint%3D5555%26temp%3D6666%26relayStatus%3DtestingString\x26followup=https%3A%2F%2Fscript.google.com%2Fmacros%2Fs%2FAKfycbzzYDI73AJY5KytE_CY3sF7_fyIZH0lz_jmms98cjUmexcOGW_4%2Fexec%3Fsetpoint%3D5555%26temp%3D6666%26relayStatus%3DtestingString\x26passive=1209600\x26noautologin=true',
  'https:\x2F\x2Faccounts.google.com\x2FPassiveLoginProber?continue=https%3A%2F%2Fscript.google.com%2Fmacros%2Fs%2FAKfycbzzYDI73AJY5KytE_CY3sF7_fyIZH0lz_jmms98cjUmexcOGW_4%2Fexec%3Fsetpoint%3D5555%26temp%3D6666%26relayStatus%3DtestingString\x26followup=https%3A%2F%2Fscript.google.com%2Fmacros%2Fs%2FAKfycbzzYDI73AJY5KytE_CY3sF7_fyIZH0lz_jmms98cjUmexcOGW_4%2Fexec%3Fsetpoint%3D5555%26temp%3D6666%26relayStatus%3DtestingString\x26passive=1209600');
  </script>
  </body>
</html>


0

I suspect that the 20 pages of JSON response is more than the Arduinoid can handle. How about setting up a script on another server to act as an agent between the ESP8266 and Google Sheets.

  1. ESP says to Agent: Add this row.
  2. Agent (running on a Linux box somewhere on the Internet) uses Google API to request add data to the sheet
  3. Agent handles the JSON response.
  4. Assuming the response works OK, the Agent tells the ESP: Done, no issue.

Advantage is that Agent abstracts most of the Google API comm and handles the heavy lifting. Disadvantage is that the Agent won’t handle ALL things that Google API supports, but that’s probably OK since the ESP won’t ASK to do those things.

My question though, is why am I getting this big JSON response when I run it via the Arduino whereas I don’t when I run it through the browser… or, why does it work when I run the same code using the example code given by the developer, but not when I run mine?

Although interestingly enough, I’m not getting the same full JSON response from the example code… so there may be something wrong with my Google side script.

it looks like you’re being asked to log in

I tried running the original URL through MY browser. I got this (among other stuff):

<title>Google Drive -- Page Not Found</title>

So I’m guessing you moved the sheet, which is fine.

When I look at your reply stream in the original post, though, I found this:

<title>Sign in - Google Accounts</title>

I’m guessing your browser is providing Cookies which allow access. The ESP code does not include the same login credentials.

1 Like

YES!!! ok… clue in the right direction… now I need to find out why the one I wrote is requiring a login and the example one that was ‘deployed’ the same way (copied it to my google docs and deployed from there) is not requiring the login!

@HankCowdog for the win!

Turns out I needed to deploy the web app on google with access to “Everyone, even anonymous”

Once I changed that, it works exactly as it is supposed to!

Owe you a beer man, been racking my brain on that for 2 days!!!

2 Likes