Using Google Spreadsheets as a Database in the Cloud

Using Google Spreadsheets as a Database in the Cloud


Hi I’m Jeff Scudder and I work
with the Google Spreadsheets Data API. You might already be
familiar with Google Spreadsheets. An online
spreadsheets application that allows for real-time
collaboration. It’s part of our Google Docs
suite. The spreadsheets API allows you to programmatically
access and modify your spreadsheet data in your
language of choice. Google provides client
libraries in a variety of languages, which simplify
working with the spreadsheets API. And I’ve recently made an
addition to the Python library that makes it easier to use
spreadsheets as a simple text database. One of the great things about
using Google Spreadsheets as a simple database is that you
can see and modify all of your data from a nice
user interface. and your data is available on
any machine as long as you’re connected to the internet you
can interact with the data in your Google Documents. Let’s get started. First off, the Python client
library is open source and available at
code.google.com/p/gdata-python-client This module is called “text db”
and it lives within the gdata spreadsheet package of the
gdata client library. The spreadsheet module allows
you to use all of the features in the spreadsheet’s API and
the new “text db” module is built on top of it. The “text db” module
streamlines a specific use case and makes
using the spreadsheets API feel like using a simple database. There’s much more that you can
do with the spreadsheet’s API like use formulas, pulling
data from other sources, and update a large number of
cells at once. But I decided to make this
module about ease of use. Let’s take a look a look at a
sample of how to use the text “db module.” We’ll begin by importing the
module which has classes that make it easy to use Google
Spreadsheets like a simple database in the cloud. Next, I’ll import a demo
module which enters my email address for me so that it’s not
broadcast to the world thanks to this video. Now we can create a new Google
Spreadsheet using our client. It’s easy. All you need to
provide is a name. The database object represents
our new Google Spreadsheet and we’re ready to create a table. But first, let’s take a look at
this document in Google Docs. Okay, let’s say that we have a
table of knights and we want to ask their name,
quest, and favorite color. Our spreadsheet now has a new
worksheet and we’re ready to add some information. Let me open that now. Now we’ll see a new row appear
in our spreadsheet. We can also add information
through the spreadsheet’s user interface. If we’re working with existing
data we may need to start by finding the spreadsheet, which
you can do by looking up the name of the spreadsheet or the
unique spreadsheet key id. Since you can have multiple
spreadsheets with the same name, the “getdatabases” method
returns a list. You can retrieve rows of data
in several ways. You can look up a single row,
or request a range of rows using “getrecords.” Since we only have three rows
of data, rows contains three items. You can also search for rows
which match certain requirements using spreadsheet
structured query language. Structured queries can contain
and or less than greater than exact matches and not equals. We had two knights with the
favorite color of blue. So, as you can see we have
two matches. Once we have our rows, we can
make changes to the data in our Google Spreadsheet. One of the potential challenges
in storing your data in the cloud is that read and write
operations may be slow because they’re transferring data over
slow and potentially flakey network connections. To avoid
this problem in the “text db” library, changes to local
objects are only sent to the server when you call the push
method. In our example earlier, we forgot to enter
Galahad’s quest. Let’s do that now. Let’s take a look at the
content in our record. Now we’re going to send this
change to the server and we should see the quest in our
spreadsheet update. I also see that we
got Galahad’s favorite color wrong. To show retrieving updates
which have happened outside of the local copy of the data
we’ll make a change in the spreadsheet. Our local copy doesn’t have
the change yet but after we pull the data you can see the
content has been updated. I’ve just walked you through
a simple example of using the “text db” module to use Google
Spreadsheets as if it were a database in the cloud. This is just one of the ways
that you can use the spreadsheets API. And although this example is
in Python, a similar library could be built in almost
any language. All of the code that I’ve used
in this sample is open source so you’re free to copy it and
modify it as you like. If you think of some other
interesting things that you’d like to do using the
spreadsheet’s API, we would love to hear about it. Please visit us in the Google
Docs Data API Group. You can also check out the
documentation for the API on code.google.com

Daniel Ostrander

Related Posts

44 thoughts on “Using Google Spreadsheets as a Database in the Cloud

  1. James Throgmorton says:

    How about giving him an "Awesome" for containing his enthusiasm, as he presented an amazing new api. I would have geeked out and crapped my pants!

  2. rrrrr says:

    Why don't you add a rudimentary database offering to join Google docs and spreadsheets?

  3. greekmonstervt91 says:

    this is so hard..i'm confused…what the hell is he talking about…

  4. kelmer carvalho says:

    Really cool !!! this can be really useful, a free simple database on the web.

  5. Mikolaj Dawidowski says:

    man, why oh why didn't you show us HOW TO LOGIN.
    Now I have to snoop around and try to figure what the hell did you do in the demo object…
    Sheeet

  6. Drew LeSueur says:

    can we please see the demo class?

  7. Michael Kariv says:

    This is a very useful feature. My silver light Gantt editor written 2 years ago works with gdata on the back end using all that complexity that Jeff hides with text db.
    Like krislyttle I would like to see SQL features – especially JOIN between "tables" ("worksheets"). Still, current state is very appreciated. Thanks Jeff

  8. thebarak says:

    Yikes, a green on black command line window and mentions of knights. It's like 1980 all over again. I hope normal people will be able to use Google Spreadsheets as full-blown databases one day soon. I do, but mine are really just Google Spreadsheets with data in them, and everything in your command window looks like Martian to me. Luckily, Google takes care of business and I don't seem to need to do that.

  9. SuperAmok Ash says:

    ca n this database link the iphone?

  10. Luis Carlos Ramírez Rodríguez says:

    @ashlovemira if you can get python to run on the iPhone :S

    Otherwise youll have to use REST services to call the API from your server, which in turn fetches data from Gdocs.

  11. Joseph Vaughan says:

    It would be really nice if you could post the contents of demo, so that we could understand what demo.Client() does

  12. Faisal says:

    import demo

    this is not working…?

  13. M T says:

    Someone kill me now…I just want a database to track training in the cloud so my clients can access it. I c
    ould NEVER do this mess.

  14. luinva says:

    My favorite is when he pauses to swallow :40
    I know, I'm just being stupid. I love this.

  15. Alex Toussieh says:

    The guy is too advanced for me. He goes straight into coding and doing geeky things I have no idea about, and I don't know how to even get started. Actually when I open the p/gdata etc. page I get it slightly different because I don't have all his documents saved up there and he just jumps into the next thing without explaining where I should click to open up his database thingy and it's more… encouraging… than it is teaching… but thanks!

  16. Carlos Jorge says:

    @luinva hahahahahha

  17. bikingviking says:

    This tutorial is useless without showing what is in the demo module. You'd think the geniuses at Google would figure that out!

  18. woodygar says:

    Can someone tell me whats in the demo module please

  19. gachola2 gaginho says:

    nhbn

  20. bikingviking says:

    If you want to see more code for this, do a search for Andreas Kahler's blog (i can't post the url here). He has a great example which includes the authentication code that is not shown here.

  21. Sylvan012 says:

    I have no idea what Python is. All I want to do is pull specific cells of data from a spreadsheet onto a Web page. HOW DO I DO THIS? Is there a simple tutorial that just says "type this for your jQuery script to pull 'x' data from your spreadsheet"?!

  22. Naveen Venkat says:

    is there a similar Google Spreadsheet API in java?

  23. kelekokerupuk says:

    Your Video Is Very Useful Sharing Watch Jeffrey Scudder demonstrate his GData Python Client Library extension that makes it easy to use Google Spreadsheets as a database in the cloud.

  24. Will Nicholson says:

    This is the step he skipped for login…

    Instead of doing:
    client = demo.Client()

    you can just do:
    client = gdata.spreadsheet.text_db.DatabaseClient(username='[email protected]', password='12345')

    Worked like a champ for me.

  25. Wa33ab1 says:

    I'm having trouble at the part where you do the record= db.AddRecord({'Knight':,'Lancelot'}) It gives me the error: Please wait…' , 'reason': 'Bad Request'. Which is bullshit because that's what it says to do in table_db.

    In addition. table.db[0].GetTables(name='Knight') returns an empty list which is also bullshit because I clearly made the table in the google spreadsheet.

    I would really like someone to help me with this, please?

  26. Mat says:

    title should be "using google spreadsheet for coders only"

  27. Conan Li says:

    how did you type so fast

  28. Rickson Menezes says:

    but It can only be used for Python? Or can it work for PHP etc?

  29. Alex Miller says:

    Cool, I think Google should make this type of use into a complete product, something like Ragic, not just simple form builder like Google Form or this.

  30. MrBeardo says:

    I dislike Google Docs, it is horrible and disorganised, so many problems with it, I'd rather stick to a paid Suite

  31. Almir Bispo says:

    Hello! I develop the CSV Comp Framework.Is a system to manipulate CSV files (tables) in de cloud (with CQL) (something like sql).I'll could like to use in the Google Cloud,but needs Pascal CGI in Server.In the Server run Pascal.Watch my channel.

  32. LiquidSequence says:

    We're extensively using Google Spreadsheets to poplulate/map data to databases (import) as well as using Spreadsheets to expose/export data.
    See slideshare LiquidSequence – creating-a-banking-application-using-google-docs-spreadsheets

  33. John Fenley says:

    This is super great. So much easier than the gdata spreadsheets service.
    for anyone wondering, i think demo just calls:
    client = gdata.spreadsheet.text_db.DatabaseClient()
    client.SetCredentials('<username>','<password>')

  34. Michael Maguire says:

    There's a book on this here:
    https://leanpub.com/googlespreadsheetprogramming
    There's quite a bit of free content that includes Google Apps Script/Excel VBA examples side-by-side

  35. Arthur Penta says:

    I'm curious to know which program you used to create this instructional video?

  36. TOMYSSHADOW says:

    I'm really confused and wondering if anyone can help me out.

    I have a game entirely written in Javascript, and my server will not allow me to use PHP, Python etc. as such, I need to do this solely in Javascript. Anyhow, my game is written in Javascript, and I want to add a highscore table. What I plan to do is have a spreadsheet hosted on my Google account. Then, when the user scores, I want to update the spreadsheet – which is on my account – with their score.

    I don't actually want to put my username and password at risk, so from my understanding I must use OAuth. But I don't understand how to use it in this case. I don't want to log into another user's account. I just want my script to temporarily be able to modify my spreadsheet, using some sort of key deal. And I only want that key to be able to modify that spreadsheet, nothing else. Naturally, I want the spreadsheet to be private, because I don't want people overriding values in the spreadsheet with their own, fake, made up scores.

    I know there are APIs, such as this one, for that purpose, but videos such as this are focused more on letting other users edit their spreadsheets with the API, which is not what I want. And naturally, I want to be able to EDIT the sheet, not just view it.

    I'm lost – where on earth do I start?

  37. impactime22 says:

    hello Google Developers ! thanks for this great insight!
    i have a question.
    do you know how to use google spreadsheet as a video metadata cataloger? Having the ability to timestamp video footage from a hard drive and save the metadata in google spreadsheets online to share with easily customize searches for different clips of footage? it would be similar to CatDV. 
    thanks!

  38. LachyWarner says:

    This video was gay lol

  39. Ridhwanul Haque says:

    whats in the client file?

  40. TheNanoWeb says:

    If you are not very technical this someone can help you on Fiverr. I used this quy, link is here
    http://bit.ly/2E4yhqg

  41. Rod Parker says:

    simple = not

  42. Pipeliner says:

    Please make a 2019 video about this.

Leave a Reply

Your email address will not be published. Required fields are marked *