RSS 2.0 Feed
RSS 2.0


Atom 1.0 Feed
Atom 1.0

  Using Google Docs as an Online Database 


qt8gt0bxhw|20009F4EEE83|RyanMain|subtext_Content|Text|0xfbffe71803000000ae00000001000500
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.


                   



Leave a comment below.

Comments

  1. Nicolas Galler 9/24/2010 6:35 AM
    Gravatar
    That is a great idea - thanks for sharing it!
  2. Mauricio Scheffer 9/24/2010 6:48 AM
    Gravatar
    Hehe, I'm also using it for a VERY similar scenario (also an app for my gf). The spreadsheet-as-DB thing also works very nicely in a Goggle App Engine app (and quite fast too). BTW here's my original article about GDataDB: http://bugsquash.blogspot.com/2008/10/crud-api-for-google-spreadsheets.html .
    I really should have written a couple of examples, cheers for that!! :-)
  3. Jason Buss 9/24/2010 9:35 AM
    Gravatar
    So glad you posted this! Thanks Ryan.
  4. Nariman 9/24/2010 10:44 AM
    Gravatar
    Great post, Ryan; I looked at this stuff a few years back and was surprised that there wasn't a formal "list" concept (like WSS) in the Apps suite: http://www.onpreinit.com/2009/06/call-for-structured-content-gadgets-101.html. This is a nice approach to abstract away the unfortunate use of the spreadsheet until a stand-alone offering materializes though. Thanks.
  5. mohammadkad 9/25/2010 12:45 AM
    Gravatar
    wow ;) very good : thanks.
  6. Tom 9/30/2010 7:51 AM
    Gravatar
    Thanks! This is a great simple way to handle cheap online data. I have found a few issues I am trying to work through, maybe someone here has the answers....

    I have a spreadsheet with 10000+ rows, 14 cols. I only want to bring back the first 100, what's the best way to do that?

    Can we access the actual row# from the spreadsheet through a query?

    If I want rows 200-299 how would I do that?

    Again, thanks!
  7. Mauricio Scheffer 10/1/2010 10:17 AM
    Gravatar
    @Tom: you can use LINQ's Skip() and Take() to do pagination.
  8. Tom 10/4/2010 2:08 PM
    Gravatar
    OK, did a little benchmarking.... With a table of about 10k records I am seeing a performance issue. It takes 13 seconds to find one row, and 13 seconds to find the last few rows. Any ideas?

    Here's the output,

    Connecting
    10/4/2010 4:57:25 PM Opening or creating database
    10/4/2010 4:57:27 PM Opening or creating table
    10/4/2010 4:57:32 PM Rows for this table is '10563'
    10/4/2010 4:57:45 PM get 1
    10/4/2010 4:57:46 PM propername>"" first 100 count: 81
    10/4/2010 4:57:46 PM propername>"" next 100 count: 0
    10/4/2010 4:58:00 PM get from end count: 63
    Press any key...

    Here is the code snippet that does the above:
    Console.WriteLine("Connecting");
    var client = new DatabaseClient(DecryptString(Properties.Settings.Default.dbUser), DecryptString(Properties.Settings.Default.dbPass));
    const string dbName = "StarCat";
    Console.WriteLine(DateTime.Now + " Opening or creating database");
    var db = client.GetDatabase(dbName) ?? client.CreateDatabase(dbName);
    const string tableName = "Hyg";
    Console.WriteLine(DateTime.Now + "Opening or creating table");
    var tHyg = db.GetTable<Hyg>(tableName);
    Console.WriteLine(DateTime.Now + " Rows for this table is '{0}'", tHyg.Rows());
    var dr = tHyg.Get(5000);
    Console.WriteLine(DateTime.Now + " get 1");
    var ds = tHyg.FindStructured("propername>\"\"", 0, 100);
    Console.WriteLine(DateTime.Now + " propername>\"\" first 100 count: {0}", ds.Count);
    ds = null;
    ds = tHyg.FindStructured("propername>\"\"", 100, 100); // .FindAll();
    Console.WriteLine(DateTime.Now + " propername>\"\" next 100 count: {0}", ds.Count);
    ds = null;
    ds = tHyg.FindStructured("starid>0", 10500, 100); // .FindAll();
    Console.WriteLine(DateTime.Now + " get from end count: {0}", ds.Count);
  9. Mauricio Scheffer 10/27/2010 8:53 PM
    Gravatar
    @Tom: can you make that spreadsheet public so I can test it? Also, I recommend using the github issue tracker to communicate this kind of things instead of Ryan's blog. http://github.com/mausch/GDataDB/issues
  10. Jordan 12/14/2010 7:06 AM
    Gravatar
    Hi, if I wanted to search on multiple fields using wildcards, how would I do that?

    Thanks!
  11. test 12/19/2010 11:57 PM
    Gravatar
    The code works fine but
    the messege comes in the second time execution
    in second time it could not add the entry in the spreadsheet
    the error id given below:

    "Execution of request failed: https://spreadsheets.google.com/feeds/list/0AjZCMrgOJbkydEFiTEg5bHdXbVB0U2FJdHd4ay1MeXc/od7/private/full?sq=username="test3.indnic@gmail.com""

    pls solve my error
  12. swetha 2/22/2011 12:11 PM
    Gravatar
    hi i just want to create a spreadsheet on googledocs and want to perform edit,insert,delete etc., operations on it through asp.net under c#. I think here i can get solution for my problem.I didnt get solution anywhere plzz help me.
  13. andrew 3/18/2011 2:56 PM
    Gravatar
    And like that google comes to help us. I sometimes think what can you do with our technology and what you could do 12 years ago, when I had a win98:) Nice work with this article. Explained in such a way that even my grandma could understand. Nice job!
  14. Pacquiao 4/6/2011 4:49 AM
    Gravatar
    any kind of feature like this would be really cool! i'm a student in biomedical engineering, so even a basic way to manage references in google docs would be AWESOME, especially because I have to collaborate with other students on papers and lab reports a lot. right now, we either have to make our own system of keeping track of our references within a google doc (limiting the google doc to a draft version), or do the old emailing MS Office documents back and forth.
  15. Keylogger 4/23/2011 5:47 AM
    Gravatar
    This is one of informative post I have read yet. It will surely helpful for my .Net college project. Thanks for this useful stuff. :-)
  16. jojee 4/24/2011 6:57 AM
    Gravatar
    thanks for such a nice info sharing with us
  17. sdasda 5/17/2011 3:18 PM
    Gravatar
    Thank you alot.
  18. Kindle Review Blog 5/25/2011 8:12 AM
    Gravatar
    Good article. Sometimes you are surprised how just using simple things you can make a lot.
  19. Supra For Sale 6/7/2011 3:10 AM
    Gravatar
    The nice thing about college is that there is usually a gym and it is usually a free membership because you are paying the tuition and the cost of it is covered. The reason people gain weight when they go to college is because they are studying late at night and they like to have many late night snacks.
  20. Free Credit Report 7/13/2011 8:33 AM
    Gravatar
    Nice idea. I like it and will try to use in my next project.
  21. Phone 4G 7/17/2011 4:38 AM
    Gravatar
    As I understand, Google Docs is an online word processor, spreadsheet, and data base manager. But there are also some cons of this program - you should have a Google account and Internet access. An if you don't have either of them? Also if compared to MS Office programs, Google Docs offer limited features. Though thanks for your information.
  22. Sascha 7/21/2011 1:19 AM
    Gravatar
    Great idea, but it´s a bit to difficult for me with the code.

    BR
  23. HOP 8/1/2011 12:00 PM
    Gravatar
    Hello, I was wondering if there is a way to create a client base database using python, if so how should I approach creating one, any suggestions?

    Thanks
  24. Staffing Software 8/8/2011 2:40 AM
    Gravatar
    Hi, It is very interesting information. I think it will be work perfectly for simple functional activity like edit,insert delete & etc.

  25. Derek 8/25/2011 12:58 AM
    Gravatar
    Worked great for prototyping. Excellent write-up, thanks for sharing!
  26. Efren 8/26/2011 10:42 AM
    Gravatar
    This sounds like what I need to do in order to create a simple school database. Can you post your wife's input application as an example so we can see it work in real time?
  27. Joerg 10/2/2011 1:10 AM
    Gravatar
    gread idea using google docs. i will try it.
    Greetings from Germany

    BR
    Joerg
  28. David 10/10/2011 12:50 AM
    Gravatar
    I notice that the gmail username and password is in plain text in the code. Does that mean anyone can see the gmail username and password is visible to anyone who looks at the page source?
  29. Ryan Farley 10/10/2011 8:50 AM
    Gravatar
    @David,

    No. This is not client-side code. It is server-side code. If this code was in the page's code-behind then anyone with physical access to the server could open the file and see the code. However, this code could easily be wrapped up in a separate DLL (which is the route I would take for sure).

    -Ryan
Comments have been closed on this topic.



 

News


Also see my CRM Developer blog

Connect:   @ryanfarley@mastodon.social

         

Sponsor

Sections