I recently built a quick internal web app for mobiles using Sencha Touch, and I needed a back end system for very simple content management. As this was a lightweight project without a need for ongoing support and with no expectation of high use, I didn’t want to build a whole back end, or pay for an out of the box solution that would feel like overkill – and not having control of the server was a factor too.

I decided to use Google Spreadsheets for the ad hoc back end, knowing they had an API that supports JSON, but not having explored it before. Sencha Touch comes with handy AJAX and JSONP handlers that I wanted to use as well; the JSON needs to be clean and in a useful format for this to work out of the box – unfortunately what the Spreadsheets API gives you is very complete and generic, and doesn’t for example associate column headers with every JSON object it returns; you need to parse the data yourself to make the format clean enough for Sencha to consume.

Here’s a sample of the sort of data I wanted to administer

Here’s the Spreadsheet API’s default output for that sheet…

https://spreadsheets.google.com/feeds/cells/0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E/1/private/basic2013-05-16T11:11:51.102ZSheet1 meme@marcelkornblum.com91https://spreadsheets.google.com/feeds/cells/0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E/1/private/basic/R1C12013-05-16T11:11:51.102ZA1name https://spreadsheets.google.com/feeds/cells/0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E/1/private/basic/R1C22013-05-16T11:11:51.102ZB1email address https://spreadsheets.google.com/feeds/cells/0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E/1/private/basic/R1C32013-05-16T11:11:51.102ZC1customerid https://spreadsheets.google.com/feeds/cells/0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E/1/private/basic/R2C12013-05-16T11:11:51.102ZA2Marcel Kornblum https://spreadsheets.google.com/feeds/cells/0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E/1/private/basic/R2C22013-05-16T11:11:51.102ZB2me@mydomain.com https://spreadsheets.google.com/feeds/cells/0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E/1/private/basic/R2C32013-05-16T11:11:51.102ZC20000001 https://spreadsheets.google.com/feeds/cells/0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E/1/private/basic/R3C12013-05-16T11:11:51.102ZA3Someone Else https://spreadsheets.google.com/feeds/cells/0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E/1/private/basic/R3C22013-05-16T11:11:51.102ZB3someone@else.com https://spreadsheets.google.com/feeds/cells/0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E/1/private/basic/R3C32013-05-16T11:11:51.102ZC30000002

…which you can see is pretty complex and raw. Here’s the JSONP response I was hoping for:

callbackFunction(
{
data: [
{
name: "Marcel Kornblum",
emailAddress: "me@mydomain.com",
customerid: "0000001"
},
{
name: "Someone Else",
emailAddress: "someone@else.com",
customerid: "0000002"
}
]
}
)

After a bit of digging around I found Google Apps Scripts: I’d heard of these before but never figured out how to use them or what they were for. In a nutshell, it’s a very powerful and flexible system that plugs into your Google Docs and that can be deployed in a huge number of ways, from allowing you to create custom functions that are exposed within specific spreadsheets (so users can put their data through your specific algorithm), all the way to creating public facing web forms and sites. One of the things it allows is JSON and JSONP responses to public facing URLs, supporting GET variables and having easy Docs API handlers.

The documentation is extensive, but it took me a long time to figure out whether or not this would be possible, which is why I’m writing this post – basically a how-to for getting spreadsheet and calendar data into a nicely formatted and sensible JSONP structure that’s web-accessible, and that can then be used to power your prototypes and small web apps.

So let’s get started.

Obviously you need a spreadsheet to start with – here’s the one I’m using for this tutorial. First of all, you should publish it to the web; this will allow you to access the data from the Scripts API without having to implement authentication (although you can turn that on if you’d prefer).

Next, you need to define a Data Range. This is basically a named way of accessing a set of cells you define as important, and we’ll use it in the Script. The easiest way to set one up is to highlight all the content cells (don’t include the headers) and add as many extra rows as you think you might need. Then use the menu to get to Data > Named and Protected Ranges, and you’ll see a sidebar appear, as in the screenshots below.

Once you’ve got that set up, put some dummy data in so we can see when it’s working, and it’ll be time to start scripting.

The first time you make a Google Apps Script you need to set it up in your Drive account, as follows:

Go to Google Drive and select Create > Connect more apps as shown below. You’ll see an overlay with a huge number of options for apps you can connect to your account – you can search for ‘script’ and it should be your top result.

Once you’ve connected the Script app, the next time you hit “create” you’ll get an option to make a script. Go ahead and make one – I followed this tutorial, which goes through connecting to the spreadsheet, getting the date out of the ranges, associating it with header cells and formatting the JSON objects.

In order to make your script accessible on a URL you need to save a version of it, and then deploy it as a web app; although you can access the unversioned script for testing through the tiny link on the web app confirmation overlay as seen here.

To make your script return JSONP you need to wrap the JSON inside a function call; the function name needs to be variable in order to work with Sencha’s system out of the box.

Luckily Google Apps Scripts can accept GET variables which allow you to easily capture the function name you want and then simply string concatenate the output like so:

function doGet(e) {
var cb = e.parameter.cb;
var outputStr = cb + '(' + JSON.stringify(output) + ');';
return ContentService.createTextOutput(outputStr).setMimeType(ContentService.MimeType.JSON);
}


The only other things I ended up changing were allowing the spreadsheet ID, worksheet name and data range to all be passed in as variables (because I wanted to use different pieces of managed data in my app without duplicating the script), and a tweak letting empty cells show up as object properties with empty strings instead of being skipped.

Here’s my final code (and here as a script) – it’s almost identical to the guide code I followed as you’ll see


function doGet(e) {
var cb = e.parameter.cb;
var sheetno = e.parameter.sn;
var sheetid = e.parameter.s;
var datarange = e.parameter.dr;
var ss = SpreadsheetApp.openById(sheetid);
var sheet = ss.getSheetByName(sheetno);
// Get the range of cells that store employee data.
var eDataRange = ss.getRangeByName(datarange);
// For every row of employee data, generate an employee object.
var resultObjects = getRowsData(sheet, eDataRange);
var output = {'data':resultObjects}
var outputStr = cb + '(' + JSON.stringify(output) + ');'
return ContentService.createTextOutput(outputStr).setMimeType(ContentService.MimeType.JSON);
// get DB obj
//var db = ScriptDb.getMyDb();
//var saveResults = db.saveBatch(resultObjects, false);
//Logger.log(resultObjects);
}
// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// - columnHeadersRowIndex: specifies the row number where the column names are stored.
// This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
var numColumns = range.getLastColumn() - range.getColumn() + 1;
var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
var headers = headersRange.getValues()[0];
return getObjects(range.getValues(), normalizeHeaders(headers));
}
// getColumnsData iterates column by column in the input range and returns an array of objects.
// Each object contains all the data for a given column, indexed by its normalized row name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// - rowHeadersColumnIndex: specifies the column number where the row names are stored.
// This argument is optional and it defaults to the column immediately left of the range;
// Returns an Array of objects.
function getColumnsData(sheet, range, rowHeadersColumnIndex) {
rowHeadersColumnIndex = rowHeadersColumnIndex || range.getColumnIndex() - 1;
var headersTmp = sheet.getRange(range.getRow(), rowHeadersColumnIndex, range.getNumRows(), 1).getValues();
var headers = normalizeHeaders(arrayTranspose(headersTmp)[0]);
return getObjects(arrayTranspose(range.getValues()), headers);
}
// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
// - data: JavaScript 2d array
// - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
var objects = [];
for (var i = 0; i < data.length; ++i) {
var object = {};
var hasData = false;
for (var j = 0; j < data[i].length; ++j) {
var cellData = data[i][j];
/*
EDITED TO HAVE EMPTY STRINGS FOR EMPTY CELLS
if (isCellEmpty(cellData)) {
continue;
}
*/
if (isCellEmpty(cellData)) {
cellData = '';
}
else
{
hasData = true;
}
object[keys[j]] = cellData;
}
if (hasData) {
objects.push(object);
}
}
return objects;
}
// Returns an Array of normalized Strings.
// Arguments:
// - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
var keys = [];
for (var i = 0; i < headers.length; ++i) { var key = normalizeHeader(headers[i]); if (key.length > 0) {
keys.push(key);
}
}
return keys;
}
// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
// - header: string to normalize
// Examples:
// "First Name" -> "firstName"
// "Market Cap (millions) -> "marketCapMillions
// "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
var key = "";
var upperCase = false;
for (var i = 0; i < header.length; ++i) { var letter = header[i]; if (letter == " " && key.length > 0) {
upperCase = true;
continue;
}
if (!isAlnum(letter)) {
continue;
}
if (key.length == 0 && isDigit(letter)) {
continue; // first character must be a letter
}
if (upperCase) {
upperCase = false;
key += letter.toUpperCase();
} else {
key += letter.toLowerCase();
}
}
return key;
}
// Returns true if the cell where cellData was read from is empty.
// Arguments:
// - cellData: string
function isCellEmpty(cellData) {
return typeof(cellData) == "string" && cellData == "";
}
// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
return char >= 'A' && char <= 'Z' || char >= 'a' && char <= 'z' || isDigit(char); } // Returns true if the character char is a digit, false otherwise. function isDigit(char) { return char >= '0' && char <= '9';
}
// Given a JavaScript 2d Array, this function returns the transposed table.
// Arguments:
// - data: JavaScript 2d Array
// Returns a JavaScript 2d Array
// Example: arrayTranspose([[1,2,3],[4,5,6]]) returns [[1,4],[2,5],[3,6]].
function arrayTranspose(data) {
if (data.length == 0 || data[0].length == 0) {
return null;
}
var ret = [];
for (var i = 0; i < data[0].length; ++i) {
ret.push([]);
}
for (var i = 0; i < data.length; ++i) {
for (var j = 0; j < data[i].length; ++j) {
ret[j][i] = data[i][j];
}
}
return ret;
}


Here you can see the JSONP output of the script acting on the spreadsheet – and doesn’t it look lovely!

https://script.google.com/macros/s/AKfycby8snUVINBHncRzPumnjMGgbg0r23gpoDzpzJ4HzK85t_bDFo4/exec?s=0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E&sn=Sheet1&dr=testRange&cb=callbackFunction

Of course, this probably isn’t the best thing to use in a production environment as there might well be usage restrictions and it’s definitely not built to be bulletproof, but for knocking together  a quick prototype and letting staff edit content in an environment they’re used to, it seems like a decent enough solution to me.

If you’re interested, this is how I called it from the Sencha Store JSONP Proxy:

Ext.define('MyApp.store.Demo', {
extend: 'Ext.data.Store',
config: {
storeId: "demoStore",
model: "MyApp.model.Demo",
autoLoad: true,
proxy: {
type: 'jsonp',
callbackKey: 'cb',
url: 'https://script.google.com/macros/s/AKfycby8snUVINBHncRzPumnjMGgbg0r23gpoDzpzJ4HzK85t_bDFo4/exec?s=0AsVzV2H8zZOedHc4bDUwX0QzYnhXdDFFWE1jcEpXN0E&sn=Sheet1&dr=testRange',
reader: {
type: 'json',
rootProperty: 'data'
},
},
}
});

I hope this is useful to you!