Google Sheets as website backend store

Two summers ago, I spent an inordinate amount of time rebuilding my home page using a database and Django so I had a good UI for entering new data. It was a fun learning project, and it worked well, but was very tricky to get configured on my CS dept. controlled server. Fast forward to now. Some small thing broke and I realized it was going to take a long time to figure well enough how things worked so I would be able to fix it. And even if I fixed it, I'd surely have to go through this again the next time it broke.

Complexity is poor design. So I decided it was time to kill the database and Django and go for a much simpler solution.

I am now using Google Sheets as my backing store, and have straightforward Javascript that queries the store. The end result is that I deleted TONS of code, removed all the configuration junk, and now have a collaborative editing model for the backend data that drives my (simple website).

My home page uses 3 spreadsheets as data stores - News, Projects, and Recent Publications. Each one is implemented similarly, so I'll include the code here for my News in case anyone wants to try this. It relies on Google's visualization library since Google wrote this to make it easy to use their charts and visualizations based on their spreadsheet data.

In your HTML, include Google's Javascript API:

In your Javascript, load Google's visualization library

// Load Google visualization code to support spreadsheet data fetching

if (typeof google !== 'undefined') {

    google.load('visualization', '1', {'packages': []});

}

Make sure your spreadsheet is publicly readable, and that store the spreadsheet URL in your code like this.

var newsSource = 'https://docs.google.com/spreadsheet/ccc?key=0Al_L57Iqw4WSdFhUYkFrMUhsRGFXdUhfUTJydU5JekE';

Then, the following functions shows how I got the data from my spreadsheet, complete with a SQL'ish query mechanism, and displayed it. You'll have to modify it for your site, but the approach is pretty straightforward.

function loadNews(limit) {

var query = new google.visualization.Query(newsSource);

query.setQuery('select D where A=true order by B desc limit ' + limit);

numNewsLoaded = limit;

query.send(newsQueryResponse);

}

function newsQueryResponse(response) {

if (response.isError()) {

alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

return;

}

var dataTable = response.getDataTable();

news_data = convertToArray(dataTable);

newsLoaded(news_data);

}

function newsLoaded(data) {

var str = "

";

for (x in data) {

var item = data[x];

var html = item[NEWS_HTML];

str += "

" + html;

}

str += "

";

if (numNewsLoaded == initNumNews) {

str += "older news...";

}

$("#news").html(str);

}

function convertToArray(dataTable) {

    var numCols = dataTable.getNumberOfColumns();

    var r,c,arr = [];

    arr.length = dataTable.getNumberOfRows(); // pre-allocate for speed

for (r=0; r

var a = [];

        a.length = numCols; // pre-allocate for speed

for (c=0; c

arr[r] = a;

}

return arr;

};

Note that Google's new spreadsheets aren't supported yet. You can read more about Google's support for these kind of data queries.

Thanks to grad student Adil Yalcin for helping me figure out this trick.