Hyou Documentation¶
Hyou provides a simple Pythonic interface to access your Google Spreadsheet data.
Synopsis¶
import hyou
# Login to Google Spreadsheet with credentials
collection = hyou.login('/path/to/credentails.json')
# Open a spreadsheet by ID
spreadsheet = collection['1ZYeIFccacgHkL0TPfdgXiMfPCuEEWUtbhXvaB9HBDzQ']
print spreadsheet.title # => "Hyou Test Sheet"
# Open a worksheet in a spreadsheet by sheet name
worksheet = spreadsheet['Sheet1']
print worksheet.title # => "Sheet1"
print worksheet.rows # => 5
print worksheet.cols # => 3
# Worksheet objects can be accessed just like two-dimensional lists
print worksheet[1][0] # => "banana"
print worksheet[1][1] # => "50"
# Call Worksheet.commit() to apply changes
worksheet[2][0] = 'cinamon'
worksheet[2][1] = 40
worksheet.commit()
User Guide¶
Installation¶
Hyou can be installed from pypi with pip.
$ sudo pip install hyou
or
$ pip install --user hyou
Source code is available on GitHub.
Preparing Credentials¶
The first step is to prepare a credential you access Google Spreadsheet with.
There are three options:
- Authorize as your Google account with OAuth2 using a shared application project.
- Authorize as your Google account with OAuth2 using your own application project.
- Authorize as a service account (a bot account not associated with any Google account).
If you just want to access your spreadsheet programatically, 1 is the safe and easy way. In other options, you need some steps to register an application at Google Developer Console. (TODO(nya): Describe those options too)
To authorize as your Google account with a shared application project, run generate_oauth2_credentials.py
.
$ generate_oauth2_credentials.py ~/.drive.json
Please visit this URL to get the authorization code:
https://accounts.google.com/o/oauth2/auth?scope=...
Code:_
Open the URL with a web browser, click “Accept” button, copy-and-paste the authorization code to the console and hit enter. Then the credential JSON is saved to the specified file.
Keep the credential file in a safe location. With the credentials, all your Google Drive documents can be accessed.
Once you prepared a credential JSON file, it is very simple to connect to Google Spreadsheet service using it:
collection = hyou.login('/path/to/credentails.json')
Working with Collections¶
A Collection
object represents a set of Google Spreadsheet documents. It is a dictionary-like object, whose key is spreadsheet ID and value is a Spreadsheet
object.
You can enumerate the spreadsheets you own by accessing a Collection
object like a dictionary.
for id, spreadsheet in collection.iteritems():
print id, spreadsheet.title
If you know a spreadsheet ID, you can open it just by indexing. This is faster than iterating through Collection
because it does not fetch the list of spreadsheets. For example, to open https://docs.google.com/spreadsheets/d/1ZYeIFccacgHkL0TPfdgXiMfPCuEEWUtbhXvaB9HBDzQ/edit :
spreadsheet = collection['1ZYeIFccacgHkL0TPfdgXiMfPCuEEWUtbhXvaB9HBDzQ']
Working with Spreadsheets¶
A Spreadsheet
object is an ordered dictionary-like object, whose key is a worksheet title and value is a Worksheet
object.
worksheet = spreadsheet['Sheet1']
It also behaves just like a list when accessed with integer indices since it is ordered.
worksheet = spreadsheet[0] # Open the first worksheet
To add or delete worksheets, use Spreadsheet.add_worksheet()
and Spreadsheet.delete_worksheet()
.
new_worksheet = spreadsheet.add_worksheet('worksheet title', rows=1000, cols=26)
spreadsheet.delete_worksheet('worksheet title')
Spreadsheet.title
read-write property holds the title of the spreadsheet.
print spreadsheet.title # => "Current spreadsheet name"
spreadsheet.title = 'New spreadsheet name'
Working with Worksheets¶
A Worksheet
object can be accessed just like two-dimensional string lists.
for i, row in enumerate(worksheet):
print i, row[0], '/'.join(row[1:])
A cell value is a bare input string, represented as a unicode string (str
in Python 3, unicode
in Python 2).
- Numbers are converted to strings.
- Formulas (e.g. “=SUM(A2:A)”) are never expanded, and returned as-is.
Inversely, you can create a formula cell by writing a formula string like “=SUM(A2:A)”.
If you attempt to write a non-string value (e.g. numbers) to a cell, it is automatically converted to a string.
worksheet[0][0] = 7
print type(worksheet[0][0]) # => str in Python 3, unicode in Python 2
Writes to cells are never committed until Worksheet.commit()
is called. You can use with statements to make sure Worksheet.commit()
is called:
with worksheet:
worksheet[0][0] = 'apple'
worksheet[1][0] = 'banana'
worksheet[2][0] = 'cinamon'
# Changes have been committed at this point
Cache Behavior¶
To reduce network traffic and round-trips, data is fetched on demand and cached. For example, calling Worksheet.values()
first time takes some time to fetch data to servers, but subsequent calls return immediately because the server response is cached.
To clear the cache to access the up-to-date data, call refresh()
.
Please be aware that any uncommitted writes to worksheet cells are discarded when refresh()
is called.
As for Worksheet
, all worksheet cells are fetched when a cell is attempted to read for the first time. This can be waste of time and bandwidth if you are interested in a subrange of a worksheet. In such case, you can use views described next.
Using Views¶
If you are interested in a subrange of a worksheet, you can use WorksheetView
for efficiency to reduce the number of fetched cells. For example, this code snippet will create a 20x10 view of a worksheet:
view = worksheet.view(start_row=100, end_row=120, start_col=200, end_col=210)
assert view[0][0] == worksheet[100][200]
Each view has independent cache. Reading a cell of a view will fetch contained cells only, instead of all cells in the worksheet.
API Reference¶
-
hyou.
SCOPES
¶ A tuple of strings representing the scopes needed to access spreadsheets. Use this constant to request OAuth2 credentials.
-
hyou.
login
(json_path=None, json_text=None)¶ Logs in to Google Spreadsheet, and returns a new
Collection
object.Parameters: - json_path (str) – The filesystem path to a credential JSON file.
- json_text (str) – A credential JSON in text format.
Either one of json_path or json_text should be given.
This method accepts two formats of credential JSONs:
- JSON file that serialized
oauth2client.client.Credentials
. - JSON file downloaded from Google Developer Console (for service accounts)
-
class
hyou.
Collection
[source]¶ Representation of your spreadsheet collection.
This is a dictionary-like object, implementing several dictionary methods like
keys()
,values()
,items()
,iterkeys()
,itervalues()
,iteritems()
,__len__()
,__iter__()
. In contrast to usualdict
, it is immutable (unlessrefresh()
is called).-
create_spreadsheet
(title, rows=1000, cols=26)[source]¶ Creates a new spreadsheet, and returns a
Spreadsheet
instance.Parameters: - title (str) – The title of a new spreadsheet.
- rows (int) – The number of rows of a new spreadsheet.
- cols (int) – The number of cols of a new spreadsheet.
Addition of a spreadsheet is committed immediately and
refresh()
is automatically called to reflect changes.
-
refresh
()¶ Discards the associated cache. See Cache Behavior for details.
-
-
class
hyou.
Spreadsheet
[source]¶ Representation of a spreadsheet.
This is a dictionary-like object, implementing several dictionary methods like
keys()
,values()
,items()
,iterkeys()
,itervalues()
,iteritems()
,__len__()
,__iter__()
. In contrast to usualdict
, it is immutable (unlessrefresh()
is called), and elements are ordered.Ordered values can by accessed by indices. That is,
obj[i]
is equivalent toobj.values()[i]
wheni
is an integer.-
key
¶ The spreadsheet ID.
This property is read-only.
-
title
¶ The title of the spreadsheet.
This property is writable. Writes are committed immediately and
refresh()
is automatically called to reflect changes.
-
url
¶ The URL of the spreadsheet.
This property is read-only.
-
updated
¶ The last update time of the spreadsheet as a
datetime.datetime
object.This property is read-only.
-
add_worksheet
(title, rows=100, cols=26)[source]¶ Adds a new worksheet and returns a new
Worksheet
object.Parameters: - title (str) – The title of a new worksheet.
- rows (int) – The number of rows of a new worksheet.
- cols (int) – The number of cols of a new worksheet.
Addition of a worksheet is committed immediately and
refresh()
is automatically called to reflect changes.
-
delete_worksheet
(title)[source]¶ Deletes a worksheet.
Parameters: title (str) – The title of the worksheet to be deleted. Deletion of a worksheet is committed immediately and
refresh()
is automatically called to reflect changes.
-
refresh
()[source]¶ Discards the associated cache. See Cache Behavior for details.
-
-
class
hyou.
Worksheet
[source]¶ Representation of a worksheet.
This object behaves just like two-dimensional string lists. The first dimension is rows and the second is columns.
-
title
¶ The title of the worksheet.
This property is writable. Writes are committed immediately and
refresh()
is automatically called to reflect changes.
-
rows
¶ The number of rows of the worksheet.
This property is writable. Writes are committed immediately and
refresh()
is automatically called to reflect changes.Use
set_size()
to change the number of both rows and columns simultaneously.
-
cols
¶ The number of columns of the worksheet.
This property is writable. Writes are committed immediately and
refresh()
is automatically called to reflect changes.Use
set_size()
to change the number of both rows and columns simultaneously.
-
commit
()¶ Commits writes to cells. Until this method is called, writes to cells never take effect.
-
__enter__
()¶
-
set_size
(rows, cols)[source]¶ Changes the dimension of the worksheet.
Parameters: - rows (int) – The new number of rows.
- cols (int) – The new number of cols.
Changes are committed immediately and
refresh()
is automatically called to reflect changes.
-
view
(start_row=None, end_row=None, start_col=None, end_col=None)[source]¶ Creates a new
WorksheetView
representing a subrange of the worksheet.Parameters: - start_row (integer) – The index of the first row included in a new view. Defaults to 0 if not specified.
- end_row (integer) – The index of the first row NOT included in a new view. Default to
rows
if not specified. - start_col (integer) – The index of the first column included in a new view. Defaults to 0 if not specified.
- end_col (integer) – The index of the first column NOT included in a new view. Default to
cols
if not specified.
-
refresh
()[source]¶ Discards the associated cache. Please be aware that any uncommitted writes to cells are also discarded. See Cache Behavior for details.
-
-
class
hyou.
WorksheetView
¶ Representation of a subrange of a worksheet.
Similarly as
Worksheet
, this object behaves just like two-dimensional string lists.-
rows
¶ The number of rows in this view. Read-only.
-
cols
¶ The number of columns in this view. Read-only.
-
commit
()¶ Commits writes to cells. Until this method is called, writes to cells never take effect.
-
__enter__
()¶
-
refresh
()¶ Discards the associated cache. Please be aware that any uncommitted writes to cells are also discarded. See Cache Behavior for details.
-
Changelog¶
3.0.0 (2017-02-XX)
- Added Python 3.3+ support.
- Dropped Python 2.6 support.
- Switched to Sheets API v4.
- Now cell values are always represented as a unicode string even in Python 2.
2.1.1 (2016-07-04)
- Support oauth2client v2.0.0+.
2.1.0 (2015-10-28)
- Worksheets emulate standard lists better.
- Support Python 2.6.
- Bugfixes.
2.0.0 (2015-08-14)
- First stable release with 100% test coverage.
1.x
- Beta releases.
Notices¶
Disclaimer¶
This library is authored by a Googler and copyrighted by Google, but is not an official Google product.
License¶
Copyright 2015 Google Inc. All Rights Reserved.
Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.