[转]Write to a Google Spreadsheet from a Python script

Saturday, January 30, 2010

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:

Example
spreadsheet

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:

image

That’s a live image served up by Google, and when the spreadsheet gets
new data, the image should update too.

This entry was tagged Python

comments powered by Disqus

© 2009-2013 lxneng.com. All rights reserved. Powered by Pyramid

go to Top