Using Google Drive as your database

header
I was recently asked to create a quick prototype for a data visualisation tool. I decided to go ahead with d3.js. The data needed to be dynamic, and added by non-technical people. At that point I didn’t have time to implement a complete CMS solution. Since this prototype was internal, I decided to explore the Google Drive route, a tool we’re already using as part of our workflow.

Let’s start with a static spreadsheet
To start, go to your Drive, and create a new spreadsheet, like the one below:
table

To be able to retrieve any data from this document, you need to publish it to the web and get the document ID.
Go to File > Publish to the web… and select the sheets you want to publish. You then get the document ID in the URL, as highlighted here:
publishing

You can then load your data like so (using jQuery for convenience) :

var path = 'https://spreadsheets.google.com/feeds/list/1E2uluTjqmf8Y_kzkFR0LHi7Z8YbccLtcoOzmEun55hk/1/public/basic?alt=json';
$.when($.ajax({
    url: path,
    type: 'GET',
    crossDomain: true,
    dataType:'json'
}).done(function(data) {
    console.log('complete');
}).fail(function() {
    console.log('error');
}))
.then(function(data){
    callback(data);
});

Hat tip to my friend Farhad for showing me this original solution. I soon found out this wouldn’t do. If you have a look at the URL above, you will notice the basic parameter. This parameter is fine if your spreadsheet only has 2 columns: the data object that you get has a feed object that contains an entry array that has a title parameter (which is your first column) and an content parameter. If you have more than 2 columns, content is a concatenated string of all your columns, which proves hard to format.

Query result, using the "basic" parameter

Query result, using the “basic” parameter

Luckily, this basic parameter can be changed, either to values or full. I am not clear on what the difference between these two is; I chose to use values, like in this demo.

Query result, using the "values" parameter

Query result, using the “values” parameter

As you can see, we now have an entry for each category. However, you may have noticed that this response is quite cluttered and requires a bit of reorganisation.

Introducing Briefcase.js
Formatting the JSON object retrieved from the spreadsheet was quite a tedious process, that’s why I decided to write a Javascript library that handles getting rid of all the superfluous fields and returns a much cleaner format. Meet Briefcase.js.
Briefcase takes the response shown above and creates an object with the title and categories only.

When using Briefcase.js, please note that the first column needs to have a title (in this case, “friends”), otherwise Google assigns a random string to it, which makes it harder to format.

a) Example 1: a clean JSON object

briefcase.getJSON({ id:"1E2uluTjqmf8Y_kzkFR0LHi7Z8YbccLtcoOzmEun55hk", leftColumnTitle: "friends" }, callback);
function callback(data) {
    console.log(data);
}

Will give the following result:

[
	{
		"title": "Pete",
		"categories": [
			{
				"name": "carrots",
				"value": "2"
			},
			{
				"name": "apples",
				"value": "1"
			},
			{
				"name": "pears",
				"value": "0"
			},
			{
				"name": "onions",
				"value": "1"
			},
			{
				"name": "tomatoes",
				"value": "6"
			}
		]
	},
	{
		"title": "Jim",
		"categories": [
			{
				"name": "carrots",
				"value": "5"
			},
			{
				"name": "apples",
				"value": "3"
			},
			{
				"name": "pears",
				"value": "5"
			},
			{
				"name": "onions",
				"value": "0"
			},
			{
				"name": "tomatoes",
				"value": "2"
			}
		]
	},
	{
		"title": "Sally",
		"categories": [
			{
				"name": "carrots",
				"value": "1"
			},
			{
				"name": "apples",
				"value": "4"
			},
			{
				"name": "pears",
				"value": "4"
			},
			{
				"name": "onions",
				"value": "1"
			},
			{
				"name": "tomatoes",
				"value": "3"
			}
		]
	},
	{
		"title": "Laura",
		"categories": [
			{
				"name": "carrots",
				"value": "1"
			},
			{
				"name": "apples",
				"value": "2"
			},
			{
				"name": "pears",
				"value": "5"
			},
			{
				"name": "onions",
				"value": "3"
			},
			{
				"name": "tomatoes",
				"value": "4"
			}
		]
	}
]

b) Example 2: Integration with D3
Using the above result, we can now combine this array with D3.js and Radarchart.js to visualise the fruits and vegetables.
Screen Shot 2014-10-02 at 18.10.27

The full code for this is available in the examples of Briefcase.js.

Read and write your “database”
Now, if you remember the brief correctly, this tool needs to be dynamic, and users should be able to add more data (in our example, more “friends” and vegetables!). Unfortunately, you cannot write directly to the spreadsheet. What you can do, however, is create a Google Form and view the responses as a spreadsheet. That is what I am going to explain next. The process is fairly similar to what we’ve seen before, when it comes to retrieving the data, except for a few things, that I will point out.

a) Set up your form
In Google Drive, create a new Form. Add fields to your form, they will be the friends’ names and the different categories. Once you’ve created your fields, click View responses to set up a spreadsheet for those answers. You now have a spreadsheet that you need to Publish to the web, like we’ve done before.
And you can now share the form with people who need to populate your spreadsheet.

form

b) Read the responses from your form
When it’s a form response, Google Drive handles spreadsheets a little bit differently. It automatically adds a timestamp, that you may or may not want to display. Briefcase.js has a few parameters to take care of that. If you are using a form response, you should be using type: “form” (default is “spreadsheet”), and if you want to display the timestamp showTimeStamp: true (default is false).
That’s it for using forms!

Conclusion
And that’s how you use Google Drive as you database when you need to build a quick prototype. Of course, I would strongly advise against using it for production work, or even for confidential material, since you need to publish it to the web (people won’t be able to edit you spreadsheet but will be able to view it). It’s great for counting potatoes and carrots, though. 🙂
I hope you found it useful, and if you’ve got any questions, give me a shout!

Sources and related articles
More about Google Sheets and JSON format
How to use a hidden Google Form

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s