yoavaviram / python-google-spreadsheet Goto Github PK

View Code? Open in Web Editor NEW

63.0 7.0 18.0 139 KB

A simple Python wrapper for the Google Spreadsheet API

License: Other

Python 100.00%

python-google-spreadsheet's Introduction

Google Spreadsheets API

A simple Python wrapper for the Google Spreadsheeta API.

Features

An object oriented interface for Worksheets
Supports List Feed view of spreadsheet rows, represented as dictionaries
Compatible with Google App Engine

Requirements

Before you get started, make sure you have:

Installed Gdata (pip install gdata)

Usage

List Spreadsheets and Worksheets:

 >>> from google_spreadsheet.api import SpreadsheetAPI
 >>> api = SpreadsheetAPI(GOOGLE_SPREADSHEET_USER, 
        GOOGLE_SPREADSHEET_PASSWORD, GOOGLE_SPREADSHEET_SOURCE)
 >>> spreadsheets = api.list_spreadsheets()
 >>> spreadsheets
 [('MyFirstSpreadsheet', 'tkZQWzwHEjKTWFFCAgw'), ('MySecondSpreadsheet', 't5I-ZPGdXjTrjMefHcg'), 
 ('MyThirdSpreadsheet', 't0heCWhzCmm9Y-GTTM_Q')]
 >>> worksheets = api.list_worksheets(spreadsheets[0][1])
 >>> worksheets
 [('MyFirstWorksheet', 'od7'), ('MySecondWorksheet', 'od6'), ('MyThirdWorksheet', 'od4')]

Please note that in order to work with a Google Spreadsheet it must be accessible to the user who's login credentials are provided. The GOOGLE_SPREADSHEET_SOURCE argument is used by Google to identify your application and track API calls.

Working with a Worksheet:

>>> sheet = spreadsheet.get_worksheet('tkZQWzwHEjKTWFFCAgw', 'od7')
>>> rows = sheet.get_rows()
>>> len(rows)
18
>>> row_to_update = rows[0]
>>> row_to_update['name'] = 'New Name'
>>> sheet.update_row(row_to_update)
{'name': 'New Name'...}
>>> row_to_insert = rows[0]
>>> row_to_insert['name'] = 'Another Name'
>>> row = sheet.insert_row(row_to_insert)
{'name': 'Another Name'...}
>>> sheet.delete_row(row)
>>> sheet.delete_all_rows()

Advanced Queries:

>>> sheet = spreadsheet.get_worksheet('tkZQWzwHEjKTWFFCAgw', 'od7')
>>> rows = sheet.get_rows(query='name = "Joe" and height < 175')

Or filter in memory:

>>> sheet = spreadsheet.get_worksheet('tkZQWzwHEjKTWFFCAgw', 'od7')
>>> filtered_rows = sheet.get_rows(
        filter_func=lambda row: row['status'] == "READY")

Sort:

>>> sheet = spreadsheet.get_worksheet('tkZQWzwHEjKTWFFCAgw', 'od7')
>>> rows = sheet.get_rows(order_by='column:age', reverse='true')

That's it.

For more information about these calls, please consult the Google Spreadsheets API Developer Guide.

Tests

To run the test suite please follow these steps:

Make sure Nose is installed: (pip install nose)
Create a local file named: test_settings.py with the following variables set to the relevant values: GOOGLE_SPREADSHEET_USER, GOOGLE_SPREADSHEET_PASSWORD, GOOGLE_SPREADSHEET_SOURCE, GOOGLE_SPREADSHEET_KEY, GOOGLE_WORKSHEET_KEY, COLUMN_NAME, COLUMN_UNIQUE_VALUE
Run nosetests

License

See LICENSE for details.

python-google-spreadsheet's People

Contributors

Stargazers

Watchers

Forkers

yoyossy rwest meltwaterarchive vincentleeuwen owenfox brazilianjoe alwaystraining r2k0 bekhzod91 nlokare drussel jbruce12000 srisreedhar gregorynicholas domekmadelyn evokerking

python-google-spreadsheet's Issues

Sample error

worksheets = spreadsheet.list_worksheets(spreadsheets[0][1])

worksheets = api.list_worksheets(spreadsheets[0][1])

pypi source

Can't install with pip. Thanks for lib :)

Flush cache on function get_rows()

Function get_rows() without query param returns the right value, but if following you use get_rows(query='...'), with a query param, the result is wrong, and it returns the same as get_rows().

The problem is the condition if self.query is not None and self.query != new_query: in function get_rows(), because you have to do the _flush_cache()

I've just deleted self.query is not None, and now it works perfectly.

row header lost it's underscore

I have a spreadsheet, where the first row are the labels for the columns. Many of my labels contain underscores like "failures_today"

When I try to edit that value with:
row['failures_today'] = 1
I get a key error, and if I print out the row dictionary to string, I see that the key is now
"failurestoday"

I need to maintain my column names. Do you ahve any suggestions

Some feature enhancements

Yoav,

thanks for your package. I was half way writing basically the same when I stumbled on yours which saved me a bit of time.

I however needed some more functionality which you may wish to implement in your package.

My application requires unique row identification for later retrieval.

Sorting through the whole listfeed seemed grossly inefficient, so I am using the following as workaround.

identification:

sheet=api.get_worksheet(spreadsheet, worksheet)
rows = sheet.get_rows()
for i, row in enumerate(rows):
id = sheet._get_row_entries(sheet.query)[i].id.text.rsplit("/",1)[1] #NAUGHTY!

retrieval:
rowkey='4su05' #unique row key
sheet=api.get_worksheet(spreadsheet, worksheet)

fudge to get single row only

sheet.keys['row_id']=rowkey #NAUGHTY!
sheet.entries=[sheet.gd_client.GetListFeed(**sheet.keys)] #NAUGHTY!
rows=sheet.get_rows()

Now you can update etc as intended

Regards

Tarek

ParseError

Nothing has changed in my App but it stopped working in May. After connection I call SpreadsheetAPI.self.api.list_spreadsheets(), I get:

File "/home/ubuntu/play/python-google-spreadsheet/google_spreadsheet/api.py", line 62, in list_spreadsheets
sheets = self._get_client().GetSpreadsheetsFeed()
File "/usr/lib/python2.7/dist-packages/gdata/spreadsheet/service.py", line 98, in GetSpreadsheetsFeed
converter=gdata.spreadsheet.SpreadsheetsSpreadsheetsFeedFromString)
File "/usr/lib/python2.7/dist-packages/gdata/service.py", line 1073, in Get
return converter(result_body)
File "/usr/lib/python2.7/dist-packages/gdata/spreadsheet/init.py", line 394, in SpreadsheetsSpreadsheetsFeedFromString
xml_string)
File "/usr/lib/python2.7/dist-packages/atom/init.py", line 92, in optional_warn_function
return f(_args, *_kwargs)
File "/usr/lib/python2.7/dist-packages/atom/init.py", line 126, in CreateClassFromXMLString
tree = ElementTree.fromstring(xml_string)
File "", line 125, in XML
cElementTree.ParseError: no element found: line 1, column 0

They suggest moving to
https://github.com/burnash/gspread

Do you have any plans to fix this issue? If not I will have to try the gspread.

GOOGLE_SPREADSHEET_SOURCE

Where can I find the google_spreadsheet_source?

feature request for creating sheet and dynamically appending columns

I would like to do something like:

ws = api.new_worksheet('Data')
ws.insert_row({'a':1})
ws.insert_row({'a':1, 'b':2, 'c':3})
ws.insert_row({'b':2, 'c':3, 'd':4})

Resulting in the table:
a,b,c,d
1,,,
1,2,3,
,2,3,4

I looked into it briefly, and it looked like it was going to take too much effort for me to do for my current goals, so I went with a human in the loop work around where I will manually create a blank worksheet, and then manually add columns on demand. Here is the blog post I found with the best info:

http://www.garrettpatterson.com/2011/08/04/add-new-column-to-google-spreadsheet/