Suppose you want to write to a Google Spreadsheet from a Python script. Here’s an example spreadsheet that you might want to update from a script:
I did some searching and found this page, which quickly led me to the Python Developer’s Guide for the Google Spreadsheet API.
There’s a simple “Getting started with Gdata and Python” page. The upshot is 1) make sure you have a recent version of Python (e.g. 2.5 or higher), then 2) install the Google Data Library. The commands I used were pretty much
mkdir ~/gdata
(download the latest Google data Python library into the ~/gdata directory)
unzip gdata.py-1.2.4.zip (or whatever version you downloaded)
sudo ./setup.py install
That’s it. You can test that everything installed fine by running “./tests/run_data_tests.py” to verify that the tests all pass. The program “./samples/docs/docs_example.py” lets you list all of your Google Spreadsheets, for example. An extremely useful program that lets you insert rows right into a spreadsheet is “./samples/spreadsheets/spreadsheetExample.py” and someone has also got a really nice example of uploading a machine’s dynamic IP address to a spreadsheet.
The most painful thing is that InsertRow() must be called with a spreadsheet key and a worksheet key. If you find out those values, you could hardcode them into the script and probably cut the size of the script in half. Or you could just look in the url to see the key value. That’s what I did. So here’s an miniature example script to write to a Google Spreadsheet from a Python script:
#!/usr/bin/python
import time
import gdata.spreadsheet.service
email = 'youraccount@gmail.com'
password = 'yourpassword'
weight = '180'
# Find this value in the url with 'key=XXX' and copy XXX below
spreadsheet_key = 'pRoiw3us3wh1FyEip46wYtW'
# All spreadsheets have worksheets. I think worksheet #1 by default always
# has a value of 'od6'
worksheet_id = 'od6'
spr_client = gdata.spreadsheet.service.SpreadsheetsService()
spr_client.email = email
spr_client.password = password
spr_client.source = 'Example Spreadsheet Writing Application'
spr_client.ProgrammaticLogin()
# Prepare the dictionary to write
dict = {}
dict['date'] = time.strftime('%m/%d/%Y')
dict['time'] = time.strftime('%H:%M:%S')
dict['weight'] = weight
print dict
entry = spr_client.InsertRow(dict, spreadsheet_key, worksheet_id)
if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
print "Insert row succeeded."
else:
print "Insert row failed."
That’s it. Run the script to append a new row to the current spreadsheet. By the way, if you make a chart from the spreadsheet data, you can right-click on the chart, select “Publish chart…” from the menu, and get a snippet of HTML to copy/paste that will embed the chart on a web page. It will look like this:
That’s a live image served up by Google, and when the spreadsheet gets new data, the image should update too.
Why would we like to do that?
Sound anything else than a User friendly interface. Let’s say apart of
prof. programmers.
Looks easy – I gave up on doing this to a MS Excel document earlier on, so good to keep this in mind for future reference..
Well reading this has certainly boosted my motivation this morning. Looks like my efficiency barrier has just been pushed up that little bit more.
Thanks for the tip Matt!
I am an SEO no idea about Python script 🙂 but i have already refered to my known Python geeks. Hope to get feedback from them soon
Chris, I have a couple follow-up blog posts that will show a neat application (to me at least).
Great! But why Python script? Is it also available for PHP?
Is there a php version too please?
Thanks
Anthony
Great post! Reading your post has certainly peaked my interested in learning more about how to use python in publishing graphics on websites. As Henri said in a post, I’m interested in knowing whether Google spreadsheet supports PHP.
Matt,
It’s good to see a conversation on the news which i’ve read in December on Ubuntu Forums on following Post,
http://ubuntuforums.org/showthread.php?t=1011105.
On that time it made me curious to know that either Google is working for every programing language that works for different operating systems ? Not sure if it will continue or not.
kinda neat
I could see some uses I know some on who publishes his weather station stats online who could use this – so when are google spreadsheets they going to to suport asynconous queus 🙂
@Henri, GData for PHP is supported by the Zend Framework: http://framework.zend.com/manual/en/zend.gdata.html Also, Matt, WordPress 2.7.1 is out now, time to jump ship from 2.6.5 and get comment replies and automattic upgrades 😉
Matt, python script is not so popular. So, only a few will find this article useful. We, all want you to write some articles which have more universal appeal and reach. Hope, as a writer you will consider our request.
RE: “We, all”
Err, I think “all” can speak for themselves, thanks.
Hmm. I like the Python script idea. You know what is amazing, is that thanks to Matt Cutts I actually know what all this stuff is now, and I dont know of any lawyer who knows as much about social media, SEO, etc., as me because of Matt’s videos and blog.
I used to think Google were a bunch of closed society creeps, but their one employee, Matt Cutts, changed that. I am sold.
Thanks Matt. BTW, I redesigned my ehlinelaw dot com site and based it in large part on what I learned from you. I would appreciate a comment or two from you and any of the old timers in here.
oops, I had one last question. Can I use this feature on my Blackberry Bold?
Matt, what’s Google’s stance on the use of Google Docs as the spreadsheet / graphing / pdf producing engine for external sites? Would it be ok to embed gdocs spreadsheet functionality within another app?
how to find the number for the other worksheets?
I mean, worksheet_id = ‘od6’ is for the first, but the other?
Anyway thanks for the nice post 🙂
Easier to use a form (which can still be POSTed to using Python, if you need to)?
Tried to run the script using Cronjob, didn’t work for me 🙁
Hi Matt,
Saw your blog post this morning thanks to Peter Norvig’s writeup. Thank you so much for posting this, it’s always nice to learn about an API through some concrete examples. Python is a pleasure to work with. Just wanted to encourage you that many of us appreciate your writeups, so please don’t listen to the negative & ungrateful voices above-
Matt, Thanks for this great video! It’s clear, concise and a real pleasure to watch! And the info you gave us is for me: PRICELESS!
Thanks again! 😀
I seem to have to go to my spreadsheet, and click “publish” on the chart manually every time the spreadsheet changes. Any way to fix this?
The latest (2.01) gdata version didn’t work with the latest Python. It only supports 2.4-2.6.
Thank you for posting this up! I found the example extremely useful, as it is just about exactly what I want to do. In my case, I wanted to received the data in the URL and append it to a spreadsheet, so this meant all I had to do was figure out extracting the variables from the URL. Turns out the gdata module has the required functions for that, so I’m good to go.
Thanks for this I found your example very useful, It’s pretty much what I wanted to do, In my case I wanted to grab some of the query string from URL and put it into a spreadsheet.
Got it all working and have leanrt alot of new features about the google spreadsheets.
This broke in late 2009? Anyone know if this method still works?
pretty damn useful. the fact that i now know about being able to do this is going to help incredibly! 🙂
Hello,
I got a error when going to insert data in the spreadsheet
gdata.service.RequestError: {‘status’: 400, ‘body’: ‘We're sorry, a server error occurred. Please wait a bit and try reloading your spreadsheet.’, ‘reason’: ‘Bad Request’}
Would please help me?
Thanks
Hello Matt,
Thank you for the sample code and the links. I do have a question though. I need to access individual cells, and the Developer API is more like selected interfaces with some sample code. Is there anywhere that list *all* the interface functions along with their descriptions, like a man page?
Thanks again,
Mike
Hey Matt and Community,
I just found this at work. I love me some Python and I repeated your example today using Python 2.7.1. I saw, via youtube, at IO a Python script populate and format a PowerPoint document. I’d love to to see your take on mixing up presentations, spreadsheet graphical data and Python. Maybe a follow up is due?
Thanks for the information about the api!
James
Dude, thanks. The Google documentation was rather hard to understand, and this helped me GREATLY. Thanks