Programming with web API's is a new thing to me, and this is the first web api I have used. I have encounter a number of troubles before getting things to work and that is why I am writing this post as an additional to their guide especially for beginners like me.
I am using python to work with the api, but this could be applicable to other languages since most of the problems I am dealing with are XML related I guess. For this little & simple application that I am trying to create the operations I perform are:
- Connecting to Google Spreadsheet Service
- Opening the spreadsheet and worksheet you wish to work with
- Writing into the spreadsheet
- Reading spreadsheet
Connecting to Google Spreadsheet service is easy, here's the code:
gd_client = gdata.spreadsheet.service.SpreadsheetsService()
gd_client.email = 'your_username'
gd_client.password = 'your_password'
gd_client.ProgrammaticLogin()
Opening the speadsheet and worksheet
As of now, the api doesn't allow us to create a spreadsheet, we can only work on the existing spreadsheet. The first thing to do is to get the key which will tell the api what spreadsheet you want to work with. Currently I am getting the spreadsheet key by opening the document itself and you could get from the url.
You can also code your way to get your spreadsheet's key like this.
spreadsheet_feed = gd_client.GetSpreadsheetsFeed()
for spreadsheet in spreadsheet_feed.entry:
print spreadsheet.id.text.rsplit('/', 1)[1]
Now we need to know which worksheet we want to work with, the only way I know to get the worksheet key is to code it. We select one of the spreadsheet key from the above
spreadsheet_key = 'o04042323673667114780.4164488110474914499'
worksheet_feed = gd_client.GetWorksheetsFeed(spreadsheet_key)
for worksheet in worksheet_feed.entry:
print worksheet.id.text.rsplit('/', 1)[1]
Now we know the spreadsheet key and worksheet key, we can now start reading or writing.
spreadsheet_key = 'o04042323673667114780.4164488110474914499'
worksheet_key = 'od6'
gd_client = gdata.spreadsheet.service.SpreadsheetsService(spreadsheet_key, worksheet_key)
gd_client.email = 'your_username'
gd_client.password = 'your_password'
gd_client.ProgrammaticLogin()
Writing into the spreadsheet
Currently, the API provide 2 ways of writing by row and by cell. There are things you need to do and know when writing by rows. The api treats the first row as the column header, so before you write by rows you need to set the column header manually.
We need first define a dictionary first, before we can write by rows.
dict = {'firstname':'Kervin', 'lastname':'Ang', 'age':29, 'birthday': '9/12/1977'}
gd_client.InsertRow(dict, spreadsheet_key, worksheet_key)
The keys of the dictionary will tell to which column we will write the data. As you can see spaces, capital letters, and special character are disregarded. For example, if your column header are $ Money Market and Php Money Market the keys of dictionary are moneymarket and phpmoneymarket respectively.
This where I spend I lot of making things work, underneath the api deals with a lot of XML/ATOM/RSS as I don't have any idea about it.
To check if the insert row is success, you could do something like this
entry = gd_client.InsertRow(dict, spreadsheet_key, worksheet_key)
if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
print "Insert row is success."
Writing by cell will be very useful in setting up column header.
Reading spreadsheet
Same with writing into spreadsheet, you could read the spreadsheet by rows or cells. An example of reading all the rows of spreadsheet
list_feed = gd.client.GetListFeed(spreadsheet_key, worksheet_key)
for entry in feed.entry:
print "%s: %s\n" % (entry.title.text, entry.content.text)
You could search or filter rows using the gdata.spreadhseet.service.ListQuery like this
query = gdata.spreadsheet.service.ListQuery()
query.sq = 'age==29'
list_feed = gd_client.GetListFeed(ss_key, ws_key, query = query)
Just like writing into spreadsheet by rows, you need to provide the correct key to match the column head.
With this I was able to retrieve some data from the internet and store them in google spreadsheet for my data analysis, and I am looking for more ways of using it.
