http.get to Google Spreadsheet

I have a project where I am trying to send data directly into google spreadsheets through google script. I have a get function in google script:

function doGet(e) {
    var obj = e.parameter;
    var data = [];
    var key = "spreadsheetKey";
    var sheetName = "Names";
    for(var name in obj) {
          data.push(obj[name]);
    }
    var sheet = SpreadsheetApp.openById(key).getSheetByName(sheetName);
    sheet.appendRow(data);
    var result = {result:data}; //could be any value that you want to return
    return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}

As of now this script will return the same data it was sent in JSON format. The problem I have at this moment is that google redirects the get call to another page. See bottom of this page. How do I get espruino to redirect?? Thank you in advance for your help on the subject.

Espruino won't redirect itself, but it's dead easy to add code for this. Try:

function getRedirect(url, callback) {
  require("http").get(url, function(res) {
    var contents = "";
    res.on('data', function(data) { contents += data; });
    res.on('close', function() { 
      if (res.statusCode==301 || res.statusCode==302) {
        console.log("Redirecting to "+res.headers.Location); 
        contents = ""; // free memory
        getRedirect(res.headers.Location, callback);
      } else
        callback(contents);
    });
  });
}

// espruino.com redirects to http://www.espruino.com
getRedirect("http://espruino.com", function(contents) { 
  console.log("Got:"+contents); 
});

Only thing I'd say is Espruino doesn't do HTTPS at the moment - the resources needed to handle SSL are really high for a microcontroller, and I haven't had time to try and shoehorn it in yet. It might be that the Google app redirects to an HTTPS URL, and won't accept non-secure connections. Not sure about that though.

Thats probably what is going on. The headers.location returns the same url, not the one we are supposed to be redirected to. It also tries to set a cookie.

Well I did find a work around. Its not perfect but it works well enough.
On google script use the following function. It can then be called by a Google script project trigger. You can expand the code to insert the data into the spreadsheet as a database.

function myFunction() {
  var response = UrlFetchApp.fetch("IP Address");
  Logger.log(response.getContentText());
}

I used the "Hello World" server example from the CC3000 page. I then set port forwarding on my router for the espruino server address through port 80. When called the Logger reports "Hello World"!!

Wow, great! So you can get the Google spreadsheet to request arbitrary URLs - it's just going the other way that's a problem?

You might be able to use some service like pastebin to allow the Espruino to HTTP POST data, and Google spreadsheets to GET that same data - without having to have a server.

There was a great-looking Twitter-ish kind of website that would have been perfect, but I'm afraid I can't find the link now.

I found a great way to get data from Espruino into Google Spreadsheets using PushingBox! And here
is a great video to boot (Although its with Arduino) about 2 minutes in.

@Cale I am using pushingbox as well. do you know of a way to get the response of a "GET" request made to Google sheets through pushingbox, back to the client? I don't have any text response from pushingbox at all, even though the actual pushing of data works.

Just to add - since the Espruino Pico can now do HTTPS, it might be that you can communicate directly with the spreadsheet?

@electronicsguy sounds like your wanting a confirmation returned back to the espruino? HTTPS is your best bet. I'm working to get that up and running right now. I haven't done anything with this for a while as I've been waiting for the HTTPS. Its here, YAY!

@Gordon what do you make of this?

Connecting with TLS...
Loading the CA root certificate...
ERROR: HTTPS init failed! mbedtls_x509_crt_parse of 'ca': -0x2362
ERROR: Unable to create socket

Scratch that..... Im getting closer. Now im getting

Loading the CA root certificate...
Loading the Client certificate...
Loading the Client Key...
Performing the SSL/TLS handshake...
ERROR: Failed! mbedtls_ssl_handshake returned -0x7200

First one was (I think) a certificate in the wrong format?

The error code 0x7200 is An invalid SSL record was received. - I looked it up here: https://github.com/espruino/Espruino/blob/master/libs/crypto/mbedtls/include/mbedtls/ssl.h

Does it happen every time?

Are you using a Pico + ESP8266 for this, and you're using basically the same code?

@Cale @Gordon I wrote a library to handle to redirects automatically, as long as you can use TLS to connect (which you now can, on the esp8266).

More info here: HTTPSRedirect

Does your code work with Espruino? It looks a lot like it's C - so Arduino-only

@Gordon Well the TLS 1.1 (HTTPS) support is required. If Espruino can do those requests, then it'll work. I don't have a Pico board to test.

Espruino does do HTTPS - as I guess this thread is a bit old, there's actually now example code for writing to Google Sheets here: http://www.espruino.com/Logging+to+Google+Sheets

I think if you ask for https in the first place, you don't get the redirect.

But Espruino runs JavaScript, not C++ - it looks to me like your code is C++ for an Arduino, so I'm not really sure how it can work with Espruino?

@Gordon Ok let me correct my previous comment. I didn't realize the espruino is only JS. So the answer is no, it won't work as-is. The logic is pretty simple though, so it's not hard to adapt it for JS. I'll work on it when I have the time. Is there a espruino simulator I could test it on?

Thanks! There's no simulator, but you can build it for something like a Raspberry Pi very easily.

I'd posted some code above that handles redirects - would your code do anything different to that? It might be worth using as a base - the HTTP and callbacks in Espruino make doing stuff like parsing the header a lot easier than in an Arduino.

@Gordon Looks the same. The C++ has some extra stuff to do the parsing and passing variables, and checking X.509 certificate fingerprints. Your code is very seamless. Kudos.

@gordon Error code "302" also refers to redirection (moved temporarily, like with google apps script). i think you should add this to the logic above.

Thanks! I just updated that code snippet...