http.get to Google Spreadsheet

[espruino-discuss]

Posted at 2015-06-13 by Cale

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.

[gfwilliams]

Posted at 2015-06-15 by @gfwilliams

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 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.

[espruino-discuss]

Posted at 2015-06-15 by Cale

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.

[espruino-discuss]

Posted at 2015-06-16 by Cale

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"!!

[gfwilliams]

Posted at 2015-06-17 by @gfwilliams

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.

[espruino-discuss]

Posted at 2015-07-21 by Cale

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.

[espruino-discuss]

Posted at 2016-01-12 by electronicsguy

@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.

[gfwilliams]

Posted at 2016-01-13 by @gfwilliams

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

[espruino-discuss]

Posted at 2016-02-06 by Cale

@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

[espruino-discuss]

Posted at 2016-02-06 by Cale

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

[gfwilliams]

Posted at 2016-02-06 by @gfwilliams

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?

[espruino-discuss]

Posted at 2016-04-25 by electronicsguy

@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

[gfwilliams]

Posted at 2016-04-26 by @gfwilliams

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

[espruino-discuss]

Posted at 2016-04-26 by electronicsguy

@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.

[gfwilliams]

Posted at 2016-04-26 by @gfwilliams

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?

[espruino-discuss]

Posted at 2016-04-26 by electronicsguy

@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?

[gfwilliams]

Posted at 2016-04-27 by @gfwilliams

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.

[espruino-discuss]

Posted at 2016-04-27 by electronicsguy

@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.

[espruino-discuss]

Posted at 2016-08-03 by electronicsguy

@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.

[gfwilliams]

Posted at 2016-08-03 by @gfwilliams

Thanks! I just updated that code snippet...