Sunday, December 02, 2007

Using Google Spreadsheets as Your Application's Database

Chances are if you're reading this blog you've likely created at least a handful of quick and dirty data driven web sites.  If you're a Microsoft technologist, you usually throw together something using SQL Server Express.  This always poses a problem for me, because I don't have hosting that includes SQL Server, and if I want it, it's a bit cost prohibitive.

One thing I've been investigating is using Google Spreadsheets as my database for these simple quick applications.  I thought I'd give it a whirl.

Luckily for us .NET developers, there's a .NET library available on Google Code that wraps the spreadsheet API.  This API allows you to authenticate and get to protected spreadsheets, or you can access your publicly shared spreadsheets without the need to authenticate. 

To get started, make references to the Google.GData.Spreadsheets.dll, Google.GData.Client.dll, and the Google.GData.Extensions.dll.  In my case, I want to leave my spreadsheet as private, so I'm going to need to authenticate.

SpreadsheetsService service = new SpreadsheetsService("jfiorato-sample-app-spreadsheets");
service.setUserCredentials("jfiorato@gmail.com", "mypassword");

I'm not sure of the significance of the application name.  Looking at the request in Fiddler, it gets passed in as the UserAgent header.  I looked around to see if you needed to register your application name with Google, but didn't find anything, and this seemed to work.  If anyone knows if there's anything special to do, chime in.

Next thing you need to do is get the spreadsheet and worksheet you want to work with.  In this case, I only need to work with one sheet.

SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = "DBExample";
SpreadsheetFeed feed = service.Query(query);
 
if (feed.Entries.Count != 1) return;
 
AtomLink link = feed.Entries[0].Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery worksheetQuery = new WorksheetQuery(link.HRef.ToString());
worksheetQuery.Title = "Sheet1";
WorksheetFeed worksheetFeed = service.Query(worksheetQuery);
 
if (worksheetFeed.Entries.Count != 1) return;
 
WorksheetEntry worksheet = (WorksheetEntry)worksheetFeed.Entries[0];

From here I can do all the stuff I need to do with a database.

To bind data to a GridView:

AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
 
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
 
IEnumerable<Person> people = GetPeople(listFeed);
 
myGrid.DataSource = people;
myGrid.DataBind();
...
 
IEnumerable<Person> GetPeople(ListFeed listFeed)
{
    foreach (ListEntry worksheetRow in listFeed.Entries)
    {
        yield return
            new Person
            {
                ID = worksheetRow.Elements[0].Value,
                FirstName = worksheetRow.Elements[1].Value,
                LastName = worksheetRow.Elements[2].Value,
                Email = worksheetRow.Elements[3].Value
            };
    }
}

Now, you can choose to add some Add/Edit/Delete functionality if you'd like, it's all possible through the .NET API here, but I'd prefer to keep it simple, and just use Google Spreadsheets to do that.

Obviously, the con to using spreadsheets as your database is that when it all boils down, it's still a spreadsheet.  You need to decide how important it is to you that the things you've come to love about relational databases won't be available.

6 comments:

KB said...

Jim, I like your blog. I don't get any of the coding (b/c I don't code), but keep up the good work!

John said...

I assume this is in C# sharp and that you are handling all of this from the file that contains the code correct? Dont you also need to include an imports section before this? your help is appreciated...

Jim Fiorato said...

Hey John

I think I did this as a Console project in VisualStudio.NET 2008 to keep it simple. The code here is C#. And you are right, the example code here does not show the imports directives. I can't recall exactly what those were, but they should be easy enough to reverse engineer.

Jim

Jack said...

For me it says something similar to: The name "service" is nota avaible... but I amde the ref to the API's you lsited in your post. What did I do wrong?

Anonymous said...

hello,
i need to how to write query for getting particular rows from google spreadsheet. can u please tell me how to write query for that????

Admirador said...

any updates in 2015 with oauth2 ?

http://blog.kateo.pl/Article/How-to-query-Google-Analytics-in-CSharp