http.get to Google Spreadsheet

Posted on
  • 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).getSheetByN­ame(sheetName);
        sheet.appendRow(data);
        var result = {result:data}; //could be any value that you want to return
        return ContentService.createTextOutput(JSON.str­ingify(result)).setMimeType(ContentServi­ce.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/blo­b/master/libs/crypto/mbedtls/include/mbe­dtls/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+Googl­e+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...

  • Post a reply
    • Bold
    • Italics
    • Link
    • Image
    • List
    • Quote
    • code
    • Preview
About

http.get to Google Spreadsheet

Posted by Avatar for Cale @Cale

Actions