From time to time I throw a website together for some temporary purpose. The website collects some data from users and I need to make this data available for whoever I put the site together for. A perfect example of this is a website that I put together for my wife for some craft making event. She needed to allow friends to place orders for various craft activities so she knew what materials she needed to order. She needed to be able to see these orders as they were placed. For a website like this, my goal is to do it with as little effort and as quick as possible. It's for a temporary purpose anyway. A great way to do this is to use Google Docs as the back end for storing the data.

Some of the immediate benefits of using Google Docs rather than a database are:

1. No need to use a hosting account that supports some database or document storage
2. No need to create any tables or schema of any kind as well as a data model
   
3. I can still build my own nice looking form & webpage
4. I don't need to build anything to view the collected data. This is the best part. In the scenario I mentioned about building a craft ordering website for my wife, I can create the spreadsheet right in her Google Docs (I could also create it in mine and share with her).
5. I can use built in features in Google Docs such as having it e-mail me when changes are made to the file (or as a daily digest). I could notify my wife of orders without writing any code to do so.

Now, with a spreadsheet on Google Docs, you do have the ability to make quicky forms, like you do in Access, but that is not what I am talking about here. In my scenario, I am using a Google Docs spreadsheet simply as a back end database for holding the collected data. I still want to make my own ASP.NET webpage & form, I just don't want to have to deal with the "data" itself.

Writing the code using the GData API is not that hard. It's really pretty easy. However, I came across something for this sort of thing that does make it even easier. There is an open source project on github by Mauricio Scheffer called GDataDB. This is a library that makes inserting, updating, or retrieving data or any kind in a spreadsheet on Google Docs just as easy as using a database repository. Incredibly easy.


Here's how GDataDB works. You basically just create your own POCO objects to hold your data. Then GDataDB will handle the rest, including the creation of the spreadsheet if you want it to. Let's consider this "Person" POCO that will represent the data I am collecting:

public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public DateTime DateOfBirth { get; set; }
    public int NumberOfKids { get; set; }
}

Now, I can use this object to add the data like this:

//using GDataDB; 
//...
 
 
// create the DatabaseClient passing my Gmail or Google Apps credentials
IDatabaseClient client = new DatabaseClient("user@gmail.com", "password");
 
// get or create the database. This is the spreadsheet file 
IDatabase db = client.GetDatabase(MyFileName) ?? client.CreateDatabase(MyFileName);
 
// get or create the table. This is a worksheet in the file 
// note I am using my Person object so it knows what my schema needs to be  
// for my data. It will create a header row with the property names 
ITable<Person> table = db.GetTable<Person>(MySheetName) ?? db.CreateTable<Person>(MySheetName);
 
// now I can fill a Person object and add it
var person = new Person();
person.FirstName = "Ryan";
person.LastName = "Farley";
person.Email = "me@me.com";
//...
 
table.Add(person);

Pretty easy stuff. Locating a row for editing is just as easy. Lets say using the same Person object I want to locate an existing row based on the Email property and if it exists I want to update that row, otherwise add a new row.

//using GDataDB;
//...
 
 
IDatabaseClient client = new DatabaseClient("user@gmail.com", "password");
IDatabase db = client.GetDatabase(MyFileName) ?? client.CreateDatabase(MyFileName);
ITable<Person> table = db.GetTable<Person>(MySheetName) ?? db.CreateTable<Person>(MySheetName);
 
// now I can fill a Person object and add it
var person = new Person();
person.FirstName = "Ryan";
person.LastName = "Farley";
person.Email = "me@me.com";
//...
 
// see if row exists first by matching on Email
IList<IRow<Person>> rows = table.FindStructured(string.Format("username=\"{0}\", person.Email));
if (rows == null || rows.Count == 0)
{
    // Email does not exist yet, add row
    table.Add(person);
}
else
{
    // Email was located, edit the row with the new data
    IRow<Person> row = rows[0];
    row.Element = person;
    row.Update();
}

Look ma, no SQL. Sure, there are other options out there that I could use, such as Mongo or whatever, but using Google Docs requires nothing, no setup, no anything. My wife can already see the data and tweak it if needed. I didn't have to build that. She also can get notification e-mails when someone uses the webpage to add data to the spreadsheet. I didn't have to build that. I am just working with my POCO objects, just a couple short lines of code and I can add that to the spreadsheet via GData. I didn't have to build that, GDataDB did it for me. Pretty quick and easy. I like it.