0

I'm working with Google drive sheets and using them as a simple data store. Google makes it very friendly to obtain a feed from a public sheet via a client side script call in the browser. 

<script type="text/javascript">
    function useJSONdata(root) {
        var feed = root.feed;
        var entries = feed.entry || [];
        var html = [''];
        for (var i = 0; i < entries.length; i++) {
            var entry = entries[i];
            (process the data, etc...)
        }
    }
</script>

<body>
    <script src='https://spreadsheets.google.com/feeds/list/xxxxxxxxxxxxxxxx/od6/public/full?alt=json-in-script&callback=useJSONdata'></script>
</body>

There is a lot of info available on this technique. Old technique here, and here. Here's a link to a handy flash card tool I wrote, code all visible in your browser's dev tools.

But in my current use case, I'd like access to the Google spreadsheet data from node.js in the server. I'm not seeing the best translation of <script src='https://...&callback=...'></script> from document client code to valid server code.

I've been attempting to make this work using https requests, both GET and POST. I can see that those requests are obtaining data, but the returned data is bizzare. I'm looking at 7000 lines of returned info on a spreadsheet with ten lines of content.

here is a curl command to look at my test spreadsheet data: curl https://spreadsheets.google.com/feeds/list/1s9-AyqYqp25s2OeGiglCSNF2UcvJJm50Ipvm8v0Mgo0/od6/public/full?alt=json-in-script

Data returned from a browser <script src=... request:

encoding:"UTF-8"
feed:
    author:[{…}]
    category:[{…}]
    entry:(167) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, …]
    id:{$t: "https://spreadsheets.google.com/feeds/list/xxxxxxxx/od6/public/full"}
    link:(4) [{…}, {…}, {…}, {…}]
    openSearch$startIndex:{$t: "1"}
    openSearch$totalResults:{$t: "167"}
    title:{type: "text", $t: "Sheet1"}
    updated:{$t: "2015-04-04T19:31:32.089Z"}
    xmlns:"http://www.w3.org/2005/Atom"
    xmlns$gsx:"http://schemas.google.com/spreadsheets/2006/extended"
    xmlns$openSearch:"http://a9.com/-/spec/opensearchrss/1.0/"
__proto__:Object

I've been looking at the returned data from my node.js https GET server request (via chrome debugger tools) and that data is unusable. A POST request nets me a statusCode 405 error. 

response: IncomingMessage
    client:TLSSocket {_tlsOptions: {…}, _secureEstablished: true, _securePending: false, _newSessionPending: false, _controlReleased: true, …}
    complete:false
    connection:TLSSocket {_tlsOptions: {…}, _secureEstablished: true, _securePending: false, _newSessionPending: false, _controlReleased: true, …}
    domain:null
    headers:{content-type: "application/atom+xml; charset=UTF-8", x-robots-tag: "noindex, nofollow, nosnippet", expires: "Wed, 27 Dec 2017 02:50:15 GMT", date: "Wed, 27 Dec 2017 02:50:15 GMT", cache-control: "private, max-age=0, must-revalidate, no-transform", …}
    httpVersion:"1.1"
    httpVersionMajor:1
    httpVersionMinor:1
    method:null
    rawHeaders:(38) ["Content-Type", "application/atom+xml; charset=UTF-8", "X-Robots-Tag", "noindex, nofollow, nosnippet", "Expires", "Wed, 27 Dec 2017 02:50:15 GMT", "Date", "Wed, 27 Dec 2017 02:50:15 GMT", "Cache-Control", "private, max-age=0, must-revalidate, no-transform", "Vary", "Accept, X-GData-Authorization, GData-Version", "GData-Version", "1.0", "Last-Modified", "Tue, 26 Dec 2017 20:36:41 GMT", "Transfer-Encoding", "chunked", "P3P", "CP="This is not a P3P policy! See g.co/p3phelp for more info."", "P3P", "CP="This is not a P3P policy! See g.co/p3phelp for more info."", "X-Content-Type-Options", "nosniff", "X-Frame-Options", "SAMEORIGIN", "X-XSS-Protection", "1; mode=block", "Server", "GSE", "Set-Cookie", "NID=120=CFx_iglHazKNNR7tH33m4UkGABss7TV9bB03EVnZrH…=/;Expires=Thu, 28-Jun-2018 02:50:15 GMT;HttpOnly", "Set-Cookie", "NID=120=W3n0zQ1UWJz6pmTOjKjAxuMtG5WOLx2D3OiQca5R9A…=/;Expires=Thu, 28-Jun-2018 02:50:15 GMT;HttpOnly", "Alt-Svc", "hq=":443"; ma=2592000; quic=51303431; quic=5130333…03335,quic=":443"; ma=2592000; v="41,39,38,37,35"", "Connection", "close"]
    rawTrailers:[]
    read:ƒ (n)
    readable:true
    req:ClientRequest {domain: null, _events: {…}, _eventsCount: 2, _maxListeners: undefined, output: Array(0), …}
    socket:TLSSocket {_tlsOptions: {…}, _secureEstablished: true, _securePending: false, _newSessionPending: false, _controlReleased: true, …}
    statusCode:200
    statusMessage:"OK"
    trailers:{}
    upgrade:false
    url:""
    _consuming:true
    _dumped:false
    _events:{end: ƒ, data: ƒ}
    _eventsCount:2
    _maxListeners:undefined
    _readableState:ReadableState {objectMode: false, highWaterMark: 16384, buffer: BufferList, length: 0, pipes: null, …}
    destroyed:(...)
    readableHighWaterMark:(...)
    __proto__:Readable

Is there a better way to convert that <script src=... code to a server request for data from a Google Drive Spreadsheet?? Anybody been here before?

zipzit
  • 3,778
  • 4
  • 35
  • 63
  • There's a whole node.js library for Google Sheets that you could look in to: https://developers.google.com/sheets/api/quickstart/nodejs – Chris Riebschlager Dec 27 '17 at 03:46
  • @ChrisRiebschlager Obviously you are correct, that is the best way forward. The new Google Sheets API (ver 4) uses OAuth permissions, something not required for previous simple feeds. I will utilize that for new projects going forward, but I intend to answer my own question for previous API version feeds. thx for the link. – zipzit Dec 27 '17 at 09:59

1 Answers1

0

So I was able to figure this one out. I ended up using the 'request' tool, in lieu of an HTTPS format. 

var request = require('request');
request.get(
    'https://spreadsheets.google.com/feeds/list/1s9-AyqYqp25s2OeGiglCSNF2UcvJJm50Ipvm8v0Mgo0/od6/public/full?alt=json',
    function(error, response, body) {
        console.log('error: ', error); // print the error if one occurred.
        console.log('statusCode: ', response && response.statusCode, "\n\n\n"); // Print the response status code if a response was received
        console.log("response.body: ", response.body, "\n\n\n");
    }
}

This worked easily enough. There was one trick, the use of ?alt=json in the feed address.

Do note, this technique seems to be superseded with version 4 of the Google Sheets Api, which now includes OAuth tokens / credentials. The use of that API makes sense for all new work going forward.

zipzit
  • 3,778
  • 4
  • 35
  • 63