After much consternation trying to hook into an unpublished Google Sheet with Javascript, I unearthed Sheetrock.js, a javascript jQuery plugin developed by Chris Zarate ‘for querying, retrieving, and displaying data from Google Spreadsheets.’
I had thought that accessing data in a publicly shared Google Sheet would be a common and straightforward process, but was put off at every turn by directions to publish the spreadsheet before accessing it and use JSON script to reference the data. Having not worked with JSON before I needed another approach, and finally discovered this apparently little-known jQuery library. Sheetrock works by accessing the (unpublished but publicly accessible) Google Sheet, using it as a back end database (as I had intended, somewhat unconventional but attainable for a beginner) and directing its content straight into a designated html div on your webpage. This comes with preconfigured options for setting the ‘fetchSize’ (how may rows of the sheet you want to access), ‘query’ (which columns will be accessed, and in what order), options for ordering the rows (which I now see is a direct copy paste of an option baked into Google’s API), and an argument to direct Sheetrock to a third party template that will format the data appropriately.
This last function had me overwhelmed at first, but worked it out after seeing an example someone else had made with Handlebars.js. This was yet another plugin I was unfamiliar with but seemed logical enough, and so was easy enough to come to grips with. Running off one example that used Handlebars.js, I was able to replicate it and finally have access to the data held in my Google Sheet! Success. The next step was to format the data according to my own needs, for which I needed to produce my own handlebars.js template. After a bit of trial and error this was up and running, and the result was a div containing a rough and ready array of all of the data held in the sheets.
In retrospect, using a plugin that directs all of the data to a div by default was not what I needed, but it was a start. Having the data accessible was enough to do some tinkering and convert it into a string version of an array, then using the .innerHTML() function I could access this and hey presto, I could progress to the next step. I’ll look back on this and laugh at how clumsy the approach is, but it’s a first draft, and it works.
