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