Testing two different methods for pulling data from a google spreadsheet for display on a web page:
using a direct link to the automatically generated csv as described by Kristin Henry.
using Tabletop.js to parse the spreadsheet automatically generated in 'Web page' format.
It appears that the csv version of the data generated by Google updates less frequently than the 'Web page' version, so using Tabletop.js is more reliable if you need immediate updates on data without manual refresh.
| <html> | |
| <head> | |
| <title>csv vs tabletop example</title> | |
| <style type="text/css"> | |
| body { | |
| margin: 0; | |
| padding: 0; | |
| background-color: #151926; | |
| font-family: "MyriadPro-Regular", "Myriad Pro Regular", MyriadPro, "Myriad Pro", Helvetica, Arial, sans-serif; | |
| font-size: 24px; | |
| color: #ccc; | |
| } | |
| #wrapper { | |
| margin: 20px 20px 150px 20px; | |
| width: 100%; | |
| } | |
| h2 { | |
| color: #eee; | |
| } | |
| input { | |
| border: none; | |
| display: inline-block; | |
| font-family: inherit; | |
| font-size: inherit; | |
| padding-left: 5px; | |
| padding-top: 3px; | |
| vertical-align: middle; | |
| } | |
| input.textinput { | |
| width: 450px; | |
| } | |
| input.button { | |
| cursor: pointer; | |
| } | |
| #csvdiv, #tabletopdiv { | |
| display: inline-block; | |
| width: 350px; | |
| vertical-align: top; | |
| } | |
| </style> | |
| <script type="text/javascript" src="tabletop.js"></script> | |
| <script type="text/javascript" src="http://mbostock.github.com/d3/d3.min.js"></script> | |
| </head> | |
| <body> | |
| <div id="wrapper"> | |
| <h2>csv vs tabletop.js example</h2> | |
| <!-- form handling --> | |
| <script type="text/javascript">var submitted=false;</script> | |
| <iframe name="hidden_iframe" id="hidden_iframe" style="display:none;" onload="if(submitted) {window.location=document.URL;}"></iframe> | |
| <form action="https://docs.google.com/forms/d/1U6tF62V18UKVlWIJTXlaBzWufN50FzVuSNT9hRL5PS4/formResponse" method="POST" id="ss-form" target="hidden_iframe" onsubmit="submitted=true;"> | |
| <input class="textinput" placeholder="Enter an item" type="text" name="entry.991199735" value="" id="entry_991199735" dir="auto" autocorrect="off" autocomplete="off"> | |
| <input type="hidden" name="draftResponse" value="[]"> | |
| <input type="hidden" name="pageHistory" value="0"> | |
| <input type="submit" name="submit" value="Submit" id="ss-submit" class="button"> | |
| </form> | |
| <div id="csvdiv"><p>csv</p></div> | |
| <div id="tabletopdiv"><p>tabletop</p></div> | |
| <!-- pull the spreadsheet data from google drive generated spreadsheet file via tabletop.js --> | |
| <script type="text/javascript"> | |
| window.onload = function() { init() }; | |
| function init() { | |
| Tabletop.init({key: '0ApMOA8U0y53_dGJpUzdMTGYyLWVGckZMOWRGNXB1Vnc', callback: showInfo, simpleSheet: true}); | |
| } | |
| function showInfo(data) { | |
| data.reverse(); | |
| var tabletopdiv = d3.select("#tabletopdiv") | |
| .append("ul"); | |
| var tabletoprows = tabletopdiv.selectAll("li") | |
| .data(data) | |
| .enter() | |
| .append("li") | |
| .text(function(d) { return d.input; }); | |
| } | |
| </script> | |
| <!-- pull the spreadsheet data directly from google drive generated csv file --> | |
| <script type="text/javascript"> | |
| d3.csv("https://docs.google.com/spreadsheet/pub?key=0ApMOA8U0y53_dGJpUzdMTGYyLWVGckZMOWRGNXB1Vnc&output=csv", function(data, error) { | |
| data.reverse(); | |
| var csvdiv = d3.select("#csvdiv") | |
| .append("ul"); | |
| var csvrows = csvdiv.selectAll("li") | |
| .data(data) | |
| .enter() | |
| .append("li") | |
| .text(function(d) { return d.input; }); | |
| }); | |
| </script> | |
| </div> <!-- /wrapper --> | |
| </body> | |
| </html> |
| (function(global) { | |
| "use strict"; | |
| var inNodeJS = false; | |
| if (typeof process !== 'undefined') { | |
| inNodeJS = true; | |
| var request = require('request'); | |
| } | |
| // from https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/indexOf | |
| if (!Array.prototype.indexOf) { | |
| Array.prototype.indexOf = function (searchElement, fromIndex) { | |
| if (this == null) { | |
| throw new TypeError(); | |
| } | |
| var t = Object(this); | |
| var len = t.length >>> 0; | |
| if (len === 0) { | |
| return -1; | |
| } | |
| var n = 0; | |
| if (arguments.length > 1) { | |
| n = Number(arguments[1]); | |
| if (n != n) { // shortcut for verifying if it's NaN | |
| n = 0; | |
| } else if (n != 0 && n != Infinity && n != -Infinity) { | |
| n = (n > 0 || -1) * Math.floor(Math.abs(n)); | |
| } | |
| } | |
| if (n >= len) { | |
| return -1; | |
| } | |
| var k = n >= 0 ? n : Math.max(len - Math.abs(n), 0); | |
| for (; k < len; k++) { | |
| if (k in t && t[k] === searchElement) { | |
| return k; | |
| } | |
| } | |
| return -1; | |
| } | |
| } | |
| /* | |
| Initialize with Tabletop.init( { key: '0AjAPaAU9MeLFdHUxTlJiVVRYNGRJQnRmSnQwTlpoUXc' } ) | |
| OR! | |
| Initialize with Tabletop.init( { key: 'https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0AjAPaAU9MeLFdHUxTlJiVVRYNGRJQnRmSnQwTlpoUXc&output=html&widget=true' } ) | |
| OR! | |
| Initialize with Tabletop.init('0AjAPaAU9MeLFdHUxTlJiVVRYNGRJQnRmSnQwTlpoUXc') | |
| */ | |
| var Tabletop = function(options) { | |
| // Make sure Tabletop is being used as a constructor no matter what. | |
| if(!this || !(this instanceof Tabletop)) { | |
| return new Tabletop(options); | |
| } | |
| if(typeof(options) === 'string') { | |
| options = { key : options }; | |
| } | |
| this.callback = options.callback; | |
| this.wanted = options.wanted || []; | |
| this.key = options.key; | |
| this.simpleSheet = !!options.simpleSheet; | |
| this.parseNumbers = !!options.parseNumbers; | |
| this.wait = !!options.wait; | |
| this.postProcess = options.postProcess; | |
| this.debug = !!options.debug; | |
| this.query = options.query || ''; | |
| this.endpoint = options.endpoint || "https://spreadsheets.google.com"; | |
| this.singleton = !!options.singleton; | |
| this.simple_url = !!options.simple_url; | |
| this.callbackContext = options.callbackContext; | |
| if(typeof(options.proxy) !== 'undefined') { | |
| this.endpoint = options.proxy; | |
| this.simple_url = true; | |
| this.singleton = true; | |
| } | |
| this.parameterize = options.parameterize || false; | |
| if(this.singleton) { | |
| if(typeof(Tabletop.singleton) !== 'undefined') { | |
| this.log("WARNING! Tabletop singleton already defined"); | |
| } | |
| Tabletop.singleton = this; | |
| } | |
| /* Be friendly about what you accept */ | |
| if(/key=/.test(this.key)) { | |
| this.log("You passed a key as a URL! Attempting to parse."); | |
| this.key = this.key.match("key=(.*?)&")[1]; | |
| } | |
| if(!this.key) { | |
| this.log("You need to pass Tabletop a key!"); | |
| return; | |
| } | |
| this.log("Initializing with key " + this.key); | |
| this.models = {}; | |
| this.model_names = []; | |
| this.base_json_path = "/feeds/worksheets/" + this.key + "/public/basic?alt="; | |
| if (inNodeJS) { | |
| this.base_json_path += 'json'; | |
| } else { | |
| this.base_json_path += 'json-in-script'; | |
| } | |
| if(!this.wait) { | |
| this.fetch(); | |
| } | |
| }; | |
| // A global storage for callbacks. | |
| Tabletop.callbacks = {}; | |
| // Backwards compatibility. | |
| Tabletop.init = function(options) { | |
| return new Tabletop(options); | |
| }; | |
| Tabletop.sheets = function() { | |
| this.log("Times have changed! You'll want to use var tabletop = Tabletop.init(...); tabletop.sheets(...); instead of Tabletop.sheets(...)"); | |
| }; | |
| Tabletop.prototype = { | |
| fetch: function(callback) { | |
| if(typeof(callback) !== "undefined") { | |
| this.callback = callback; | |
| } | |
| this.requestData(this.base_json_path, this.loadSheets); | |
| }, | |
| /* | |
| This will call the environment appropriate request method. | |
| In browser it will use JSON-P, in node it will use request() | |
| */ | |
| requestData: function(path, callback) { | |
| if (inNodeJS) { | |
| this.serverSideFetch(path, callback); | |
| } else { | |
| this.injectScript(path, callback); | |
| } | |
| }, | |
| /* | |
| Insert the URL into the page as a script tag. Once it's loaded the spreadsheet data | |
| it triggers the callback. This helps you avoid cross-domain errors | |
| http://code.google.com/apis/gdata/samples/spreadsheet_sample.html | |
| Let's be plain-Jane and not use jQuery or anything. | |
| */ | |
| injectScript: function(path, callback) { | |
| var script = document.createElement('script'); | |
| var callbackName; | |
| if(this.singleton) { | |
| if(callback === this.loadSheets) { | |
| callbackName = 'Tabletop.singleton.loadSheets'; | |
| } else if (callback === this.loadSheet) { | |
| callbackName = 'Tabletop.singleton.loadSheet'; | |
| } | |
| } else { | |
| var self = this; | |
| callbackName = 'tt' + (+new Date()) + (Math.floor(Math.random()*100000)); | |
| // Create a temp callback which will get removed once it has executed, | |
| // this allows multiple instances of Tabletop to coexist. | |
| Tabletop.callbacks[ callbackName ] = function () { | |
| var args = Array.prototype.slice.call( arguments, 0 ); | |
| callback.apply(self, args); | |
| script.parentNode.removeChild(script); | |
| delete Tabletop.callbacks[callbackName]; | |
| }; | |
| callbackName = 'Tabletop.callbacks.' + callbackName; | |
| } | |
| var url = path + "&callback=" + callbackName; | |
| if(this.simple_url) { | |
| // We've gone down a rabbit hole of passing injectScript the path, so let's | |
| // just pull the sheet_id out of the path like the least efficient worker bees | |
| if(path.indexOf("/list/") !== -1) { | |
| script.src = this.endpoint + "/" + this.key + "-" + path.split("/")[4]; | |
| } else { | |
| script.src = this.endpoint + "/" + this.key; | |
| } | |
| } else { | |
| script.src = this.endpoint + url; | |
| } | |
| if (this.parameterize) { | |
| script.src = this.parameterize + encodeURIComponent(script.src); | |
| } | |
| document.getElementsByTagName('script')[0].parentNode.appendChild(script); | |
| }, | |
| /* | |
| This will only run if tabletop is being run in node.js | |
| */ | |
| serverSideFetch: function(path, callback) { | |
| var self = this | |
| request({url: this.endpoint + path, json: true}, function(err, resp, body) { | |
| if (err) { | |
| return console.error(err); | |
| } | |
| callback.call(self, body); | |
| }); | |
| }, | |
| /* | |
| Is this a sheet you want to pull? | |
| If { wanted: ["Sheet1"] } has been specified, only Sheet1 is imported | |
| Pulls all sheets if none are specified | |
| */ | |
| isWanted: function(sheetName) { | |
| if(this.wanted.length === 0) { | |
| return true; | |
| } else { | |
| return this.wanted.indexOf(sheetName) !== -1; | |
| } | |
| }, | |
| /* | |
| What gets send to the callback | |
| if simpleSheet === true, then don't return an array of Tabletop.this.models, | |
| only return the first one's elements | |
| */ | |
| data: function() { | |
| // If the instance is being queried before the data's been fetched | |
| // then return undefined. | |
| if(this.model_names.length === 0) { | |
| return undefined; | |
| } | |
| if(this.simpleSheet) { | |
| if(this.model_names.length > 1 && this.debug) { | |
| this.log("WARNING You have more than one sheet but are using simple sheet mode! Don't blame me when something goes wrong."); | |
| } | |
| return this.models[ this.model_names[0] ].all(); | |
| } else { | |
| return this.models; | |
| } | |
| }, | |
| /* | |
| Add another sheet to the wanted list | |
| */ | |
| addWanted: function(sheet) { | |
| if(this.wanted.indexOf(sheet) === -1) { | |
| this.wanted.push(sheet); | |
| } | |
| }, | |
| /* | |
| Load all worksheets of the spreadsheet, turning each into a Tabletop Model. | |
| Need to use injectScript because the worksheet view that you're working from | |
| doesn't actually include the data. The list-based feed (/feeds/list/key..) does, though. | |
| Calls back to loadSheet in order to get the real work done. | |
| Used as a callback for the worksheet-based JSON | |
| */ | |
| loadSheets: function(data) { | |
| var i, ilen; | |
| var toLoad = []; | |
| this.foundSheetNames = []; | |
| for(i = 0, ilen = data.feed.entry.length; i < ilen ; i++) { | |
| this.foundSheetNames.push(data.feed.entry[i].title.$t); | |
| // Only pull in desired sheets to reduce loading | |
| if( this.isWanted(data.feed.entry[i].content.$t) ) { | |
| var sheet_id = data.feed.entry[i].link[3].href.substr( data.feed.entry[i].link[3].href.length - 3, 3); | |
| var json_path = "/feeds/list/" + this.key + "/" + sheet_id + "/public/values?sq=" + this.query + '&alt=' | |
| if (inNodeJS) { | |
| json_path += 'json'; | |
| } else { | |
| json_path += 'json-in-script'; | |
| } | |
| toLoad.push(json_path); | |
| } | |
| } | |
| this.sheetsToLoad = toLoad.length; | |
| for(i = 0, ilen = toLoad.length; i < ilen; i++) { | |
| this.requestData(toLoad[i], this.loadSheet); | |
| } | |
| }, | |
| /* | |
| Access layer for the this.models | |
| .sheets() gets you all of the sheets | |
| .sheets('Sheet1') gets you the sheet named Sheet1 | |
| */ | |
| sheets: function(sheetName) { | |
| if(typeof sheetName === "undefined") { | |
| return this.models; | |
| } else { | |
| if(typeof(this.models[ sheetName ]) === "undefined") { | |
| // alert( "Can't find " + sheetName ); | |
| return; | |
| } else { | |
| return this.models[ sheetName ]; | |
| } | |
| } | |
| }, | |
| /* | |
| Parse a single list-based worksheet, turning it into a Tabletop Model | |
| Used as a callback for the list-based JSON | |
| */ | |
| loadSheet: function(data) { | |
| var model = new Tabletop.Model( { data: data, | |
| parseNumbers: this.parseNumbers, | |
| postProcess: this.postProcess, | |
| tabletop: this } ); | |
| this.models[ model.name ] = model; | |
| if(this.model_names.indexOf(model.name) === -1) { | |
| this.model_names.push(model.name); | |
| } | |
| this.sheetsToLoad--; | |
| if(this.sheetsToLoad === 0) | |
| this.doCallback(); | |
| }, | |
| /* | |
| Execute the callback upon loading! Rely on this.data() because you might | |
| only request certain pieces of data (i.e. simpleSheet mode) | |
| Tests this.sheetsToLoad just in case a race condition happens to show up | |
| */ | |
| doCallback: function() { | |
| if(this.sheetsToLoad === 0) { | |
| this.callback.apply(this.callbackContext || this, [this.data(), this]); | |
| } | |
| }, | |
| log: function(msg) { | |
| if(this.debug) { | |
| if(typeof console !== "undefined" && typeof console.log !== "undefined") { | |
| Function.prototype.apply.apply(console.log, [console, arguments]); | |
| } | |
| } | |
| } | |
| }; | |
| /* | |
| Tabletop.Model stores the attribute names and parses the worksheet data | |
| to turn it into something worthwhile | |
| Options should be in the format { data: XXX }, with XXX being the list-based worksheet | |
| */ | |
| Tabletop.Model = function(options) { | |
| var i, j, ilen, jlen; | |
| this.column_names = []; | |
| this.name = options.data.feed.title.$t; | |
| this.elements = []; | |
| this.raw = options.data; // A copy of the sheet's raw data, for accessing minutiae | |
| if(typeof(options.data.feed.entry) === 'undefined') { | |
| options.tabletop.log("Missing data for " + this.name + ", make sure you didn't forget column headers"); | |
| this.elements = []; | |
| return; | |
| } | |
| for(var key in options.data.feed.entry[0]){ | |
| if(/^gsx/.test(key)) | |
| this.column_names.push( key.replace("gsx$","") ); | |
| } | |
| for(i = 0, ilen = options.data.feed.entry.length ; i < ilen; i++) { | |
| var source = options.data.feed.entry[i]; | |
| var element = {}; | |
| for(var j = 0, jlen = this.column_names.length; j < jlen ; j++) { | |
| var cell = source[ "gsx$" + this.column_names[j] ]; | |
| if (typeof(cell) !== 'undefined') { | |
| if(options.parseNumbers && cell.$t !== '' && !isNaN(cell.$t)) | |
| element[ this.column_names[j] ] = +cell.$t; | |
| else | |
| element[ this.column_names[j] ] = cell.$t; | |
| } else { | |
| element[ this.column_names[j] ] = ''; | |
| } | |
| } | |
| if(element.rowNumber === undefined) | |
| element.rowNumber = i + 1; | |
| if( options.postProcess ) | |
| options.postProcess(element); | |
| this.elements.push(element); | |
| } | |
| }; | |
| Tabletop.Model.prototype = { | |
| /* | |
| Returns all of the elements (rows) of the worksheet as objects | |
| */ | |
| all: function() { | |
| return this.elements; | |
| }, | |
| /* | |
| Return the elements as an array of arrays, instead of an array of objects | |
| */ | |
| toArray: function() { | |
| var array = [], | |
| i, j, ilen, jlen; | |
| for(i = 0, ilen = this.elements.length; i < ilen; i++) { | |
| var row = []; | |
| for(j = 0, jlen = this.column_names.length; j < jlen ; j++) { | |
| row.push( this.elements[i][ this.column_names[j] ] ); | |
| } | |
| array.push(row); | |
| } | |
| return array; | |
| } | |
| }; | |
| if(inNodeJS) { | |
| module.exports = Tabletop; | |
| } else { | |
| global.Tabletop = Tabletop; | |
| } | |
| })(this); |