#!/usr/bin/env python

#

# Copyright (C) 2009 Google Inc.

#

# 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.

"""Contains a client to communicate with the Google Spreadsheets servers.

For documentation on the Spreadsheets API, see:

http://code.google.com/apis/spreadsheets/

"""

__author__ = 'j.s@google.com (Jeff Scudder)'

import gdata.client

import gdata.gauth

import gdata.spreadsheets.data

import atom.data

import atom.http_core

SPREADSHEETS_URL = ('https://spreadsheets.google.com/feeds/spreadsheets'

'/private/full')

WORKSHEETS_URL = ('https://spreadsheets.google.com/feeds/worksheets/'

'%s/private/full')

WORKSHEET_URL = ('https://spreadsheets.google.com/feeds/worksheets/'

'%s/private/full/%s')

TABLES_URL = 'https://spreadsheets.google.com/feeds/%s/tables'

RECORDS_URL = 'https://spreadsheets.google.com/feeds/%s/records/%s'

RECORD_URL = 'https://spreadsheets.google.com/feeds/%s/records/%s/%s'

CELLS_URL = 'https://spreadsheets.google.com/feeds/cells/%s/%s/private/full'

CELL_URL = ('https://spreadsheets.google.com/feeds/cells/%s/%s/private/full/'

'R%sC%s')

LISTS_URL = 'https://spreadsheets.google.com/feeds/list/%s/%s/private/full'

class SpreadsheetsClient(gdata.client.GDClient):

api_version = '3'

auth_service = 'wise'

auth_scopes = gdata.gauth.AUTH_SCOPES['wise']

ssl = True

def get_spreadsheets(self, auth_token=None,

desired_class=gdata.spreadsheets.data.SpreadsheetsFeed,

**kwargs):

"""Obtains a feed with the spreadsheets belonging to the current user.

Args:

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

desired_class: class descended from atom.core.XmlElement to which a

successful response should be converted. If there is no

converter function specified (converter=None) then the

desired_class will be used in calling the

atom.core.parse function. If neither

the desired_class nor the converter is specified, an

HTTP reponse object will be returned. Defaults to

gdata.spreadsheets.data.SpreadsheetsFeed.

"""

return self.get_feed(SPREADSHEETS_URL, auth_token=auth_token,

desired_class=desired_class, **kwargs)

GetSpreadsheets = get_spreadsheets

def get_worksheets(self, spreadsheet_key, auth_token=None,

desired_class=gdata.spreadsheets.data.WorksheetsFeed,

**kwargs):

"""Finds the worksheets within a given spreadsheet.

Args:

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

desired_class: class descended from atom.core.XmlElement to which a

successful response should be converted. If there is no

converter function specified (converter=None) then the

desired_class will be used in calling the

atom.core.parse function. If neither

the desired_class nor the converter is specified, an

HTTP reponse object will be returned. Defaults to

gdata.spreadsheets.data.WorksheetsFeed.

"""

return self.get_feed(WORKSHEETS_URL % spreadsheet_key,

auth_token=auth_token, desired_class=desired_class,

**kwargs)

GetWorksheets = get_worksheets

def add_worksheet(self, spreadsheet_key, title, rows, cols,

auth_token=None, **kwargs):

"""Creates a new worksheet entry in the spreadsheet.

Args:

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

title: str, The title to be used in for the worksheet.

rows: str or int, The number of rows this worksheet should start with.

cols: str or int, The number of columns this worksheet should start with.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

"""

new_worksheet = gdata.spreadsheets.data.WorksheetEntry(

title=atom.data.Title(text=title),

row_count=gdata.spreadsheets.data.RowCount(text=str(rows)),

col_count=gdata.spreadsheets.data.ColCount(text=str(cols)))

return self.post(new_worksheet, WORKSHEETS_URL % spreadsheet_key,

auth_token=auth_token, **kwargs)

AddWorksheet = add_worksheet

def get_worksheet(self, spreadsheet_key, worksheet_id,

desired_class=gdata.spreadsheets.data.WorksheetEntry,

auth_token=None, **kwargs):

"""Retrieves a single worksheet.

Args:

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

worksheet_id: str, The unique ID for the worksheet withing the desired

spreadsheet.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

desired_class: class descended from atom.core.XmlElement to which a

successful response should be converted. If there is no

converter function specified (converter=None) then the

desired_class will be used in calling the

atom.core.parse function. If neither

the desired_class nor the converter is specified, an

HTTP reponse object will be returned. Defaults to

gdata.spreadsheets.data.WorksheetEntry.

"""

return self.get_entry(WORKSHEET_URL % (spreadsheet_key, worksheet_id,),

auth_token=auth_token, desired_class=desired_class,

**kwargs)

GetWorksheet = get_worksheet

def add_table(self, spreadsheet_key, title, summary, worksheet_name,

header_row, num_rows, start_row, insertion_mode,

column_headers, auth_token=None, **kwargs):

"""Creates a new table within the worksheet.

Args:

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

title: str, The title for the new table within a worksheet.

summary: str, A description of the table.

worksheet_name: str The name of the worksheet in which this table

should live.

header_row: int or str, The number of the row in the worksheet which

will contain the column names for the data in this table.

num_rows: int or str, The number of adjacent rows in this table.

start_row: int or str, The number of the row at which the data begins.

insertion_mode: str

column_headers: dict of strings, maps the column letters (A, B, C) to

the desired name which will be viewable in the

worksheet.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

"""

data = gdata.spreadsheets.data.Data(

insertion_mode=insertion_mode, num_rows=str(num_rows),

start_row=str(start_row))

for index, name in column_headers.iteritems():

data.column.append(gdata.spreadsheets.data.Column(

index=index, name=name))

new_table = gdata.spreadsheets.data.Table(

title=atom.data.Title(text=title), summary=atom.data.Summary(summary),

worksheet=gdata.spreadsheets.data.Worksheet(name=worksheet_name),

header=gdata.spreadsheets.data.Header(row=str(header_row)), data=data)

return self.post(new_table, TABLES_URL % spreadsheet_key,

auth_token=auth_token, **kwargs)

AddTable = add_table

def get_tables(self, spreadsheet_key,

desired_class=gdata.spreadsheets.data.TablesFeed,

auth_token=None, **kwargs):

"""Retrieves a feed listing the tables in this spreadsheet.

Args:

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

desired_class: class descended from atom.core.XmlElement to which a

successful response should be converted. If there is no

converter function specified (converter=None) then the

desired_class will be used in calling the

atom.core.parse function. If neither

the desired_class nor the converter is specified, an

HTTP reponse object will be returned. Defaults to

gdata.spreadsheets.data.TablesFeed.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

"""

return self.get_feed(TABLES_URL % spreadsheet_key,

desired_class=desired_class, auth_token=auth_token,

**kwargs)

GetTables = get_tables

def add_record(self, spreadsheet_key, table_id, fields,

title=None, auth_token=None, **kwargs):

"""Adds a new row to the table.

Args:

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

table_id: str, The ID of the table within the worksheet which should

receive this new record. The table ID can be found using the

get_table_id method of a gdata.spreadsheets.data.Table.

fields: dict of strings mapping column names to values.

title: str, optional The title for this row.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

"""

new_record = gdata.spreadsheets.data.Record()

if title is not None:

new_record.title = atom.data.Title(text=title)

for name, value in fields.iteritems():

new_record.field.append(gdata.spreadsheets.data.Field(

name=name, text=value))

return self.post(new_record, RECORDS_URL % (spreadsheet_key, table_id),

auth_token=auth_token, **kwargs)

AddRecord = add_record

def get_records(self, spreadsheet_key, table_id,

desired_class=gdata.spreadsheets.data.RecordsFeed,

auth_token=None, **kwargs):

"""Retrieves the records in a table.

Args:

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

table_id: str, The ID of the table within the worksheet whose records

we would like to fetch. The table ID can be found using the

get_table_id method of a gdata.spreadsheets.data.Table.

desired_class: class descended from atom.core.XmlElement to which a

successful response should be converted. If there is no

converter function specified (converter=None) then the

desired_class will be used in calling the

atom.core.parse function. If neither

the desired_class nor the converter is specified, an

HTTP reponse object will be returned. Defaults to

gdata.spreadsheets.data.RecordsFeed.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

"""

return self.get_feed(RECORDS_URL % (spreadsheet_key, table_id),

desired_class=desired_class, auth_token=auth_token,

**kwargs)

GetRecords = get_records

def get_record(self, spreadsheet_key, table_id, record_id,

desired_class=gdata.spreadsheets.data.Record,

auth_token=None, **kwargs):

"""Retrieves a single record from the table.

Args:

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

table_id: str, The ID of the table within the worksheet whose records

we would like to fetch. The table ID can be found using the

get_table_id method of a gdata.spreadsheets.data.Table.

record_id: str, The ID of the record within this table which we want to

fetch. You can find the record ID using get_record_id() on

an instance of the gdata.spreadsheets.data.Record class.

desired_class: class descended from atom.core.XmlElement to which a

successful response should be converted. If there is no

converter function specified (converter=None) then the

desired_class will be used in calling the

atom.core.parse function. If neither

the desired_class nor the converter is specified, an

HTTP reponse object will be returned. Defaults to

gdata.spreadsheets.data.RecordsFeed.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

"""

return self.get_entry(RECORD_URL % (spreadsheet_key, table_id, record_id),

desired_class=desired_class, auth_token=auth_token,

**kwargs)

GetRecord = get_record

def get_cells(self, spreadsheet_key, worksheet_id,

desired_class=gdata.spreadsheets.data.CellsFeed,

auth_token=None, **kwargs):

"""Retrieves the cells which have values in this spreadsheet.

Blank cells are not included.

Args:

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

worksheet_id: str, The unique ID of the worksheet in this spreadsheet

whose cells we want. This can be obtained using

WorksheetEntry's get_worksheet_id method.

desired_class: class descended from atom.core.XmlElement to which a

successful response should be converted. If there is no

converter function specified (converter=None) then the

desired_class will be used in calling the

atom.core.parse function. If neither

the desired_class nor the converter is specified, an

HTTP reponse object will be returned. Defaults to

gdata.spreadsheets.data.CellsFeed.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

"""

return self.get_feed(CELLS_URL % (spreadsheet_key, worksheet_id),

auth_token=auth_token, desired_class=desired_class,

**kwargs)

GetCells = get_cells

def get_cell(self, spreadsheet_key, worksheet_id, row_num, col_num,

desired_class=gdata.spreadsheets.data.CellEntry,

auth_token=None, **kwargs):

"""Retrieves a single cell from the worksheet.

Indexes are 1 based so the first cell in the worksheet is 1, 1.

Args:

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

worksheet_id: str, The unique ID of the worksheet in this spreadsheet

whose cells we want. This can be obtained using

WorksheetEntry's get_worksheet_id method.

row_num: int, The row of the cell that we want. Numbering starts with 1.

col_num: int, The column of the cell we want. Numbering starts with 1.

desired_class: class descended from atom.core.XmlElement to which a

successful response should be converted. If there is no

converter function specified (converter=None) then the

desired_class will be used in calling the

atom.core.parse function. If neither

the desired_class nor the converter is specified, an

HTTP reponse object will be returned. Defaults to

gdata.spreadsheets.data.CellEntry.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

"""

return self.get_entry(

CELL_URL % (spreadsheet_key, worksheet_id, row_num, col_num),

auth_token=auth_token, desired_class=desired_class, **kwargs)

GetCell = get_cell

def get_list_feed(self, spreadsheet_key, worksheet_id,

desired_class=gdata.spreadsheets.data.ListsFeed,

auth_token=None, **kwargs):

"""Retrieves the value rows from the worksheet's list feed.

The list feed is a view of the spreadsheet in which the first row is used

for column names and subsequent rows up to the first blank line are

records.

Args:

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

worksheet_id: str, The unique ID of the worksheet in this spreadsheet

whose cells we want. This can be obtained using

WorksheetEntry's get_worksheet_id method.

desired_class: class descended from atom.core.XmlElement to which a

successful response should be converted. If there is no

converter function specified (converter=None) then the

desired_class will be used in calling the

atom.core.parse function. If neither

the desired_class nor the converter is specified, an

HTTP reponse object will be returned. Defaults to

gdata.spreadsheets.data.ListsFeed.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

"""

return self.get_feed(LISTS_URL % (spreadsheet_key, worksheet_id),

auth_token=auth_token, desired_class=desired_class,

**kwargs)

GetListFeed = get_list_feed

def add_list_entry(self, list_entry, spreadsheet_key, worksheet_id,

auth_token=None, **kwargs):

"""Adds a new row to the worksheet's list feed.

Args:

list_entry: gdata.spreadsheets.data.ListsEntry An entry which contains

the values which should be set for the columns in this

record.

spreadsheet_key: str, The unique ID of this containing spreadsheet. This

can be the ID from the URL or as provided in a

Spreadsheet entry.

worksheet_id: str, The unique ID of the worksheet in this spreadsheet

whose cells we want. This can be obtained using

WorksheetEntry's get_worksheet_id method.

auth_token: An object which sets the Authorization HTTP header in its

modify_request method. Recommended classes include

gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken

among others. Represents the current user. Defaults to None

and if None, this method will look for a value in the

auth_token member of SpreadsheetsClient.

"""

return self.post(list_entry, LISTS_URL % (spreadsheet_key, worksheet_id),

auth_token=auth_token, **kwargs)

AddListEntry = add_list_entry

class SpreadsheetQuery(gdata.client.Query):

def __init__(self, title=None, title_exact=None, **kwargs):

"""Adds Spreadsheets feed query parameters to a request.

Args:

title: str Specifies the search terms for the title of a document.

This parameter used without title-exact will only submit partial

queries, not exact queries.

title_exact: str Specifies whether the title query should be taken as an

exact string. Meaningless without title. Possible values are

'true' and 'false'.

"""

gdata.client.Query.__init__(self, **kwargs)

self.title = title

self.title_exact = title_exact

def modify_request(self, http_request):

gdata.client._add_query_param('title', self.title, http_request)

gdata.client._add_query_param('title-exact', self.title_exact,

http_request)

gdata.client.Query.modify_request(self, http_request)

ModifyRequest = modify_request

class WorksheetQuery(SpreadsheetQuery):

pass

class ListQuery(gdata.client.Query):

def __init__(self, order_by=None, reverse=None, sq=None, **kwargs):

"""Adds List-feed specific query parameters to a request.

Args:

order_by: str Specifies what column to use in ordering the entries in

the feed. By position (the default): 'position' returns

rows in the order in which they appear in the GUI. Row 1, then

row 2, then row 3, and so on. By column:

'column:columnName' sorts rows in ascending order based on the

values in the column with the given columnName, where

columnName is the value in the header row for that column.

reverse: str Specifies whether to sort in descending or ascending order.

Reverses default sort order: 'true' results in a descending

sort; 'false' (the default) results in an ascending sort.

sq: str Structured query on the full text in the worksheet.

[columnName][binaryOperator][value]

Supported binaryOperators are:

- (), for overriding order of operations

- = or ==, for strict equality

- <> or !=, for strict inequality

- and or &&, for boolean and

- or or ||, for boolean or

"""

gdata.client.Query.__init__(self, **kwargs)

self.order_by = order_by

self.reverse = reverse

self.sq = sq

def modify_request(self, http_request):

gdata.client._add_query_param('orderby', self.order_by, http_request)

gdata.client._add_query_param('reverse', self.reverse, http_request)

gdata.client._add_query_param('sq', self.sq, http_request)

gdata.client.Query.modify_request(self, http_request)

ModifyRequest = modify_request

class TableQuery(ListQuery):

pass

class CellQuery(gdata.client.Query):

def __init__(self, min_row=None, max_row=None, min_col=None, max_col=None,

range=None, return_empty=None, **kwargs):

"""Adds Cells-feed specific query parameters to a request.

Args:

min_row: str or int Positional number of minimum row returned in query.

max_row: str or int Positional number of maximum row returned in query.

min_col: str or int Positional number of minimum column returned in query.

max_col: str or int Positional number of maximum column returned in query.

range: str A single cell or a range of cells. Use standard spreadsheet

cell-range notations, using a colon to separate start and end of

range. Examples:

- 'A1' and 'R1C1' both specify only cell A1.

- 'D1:F3' and 'R1C4:R3C6' both specify the rectangle of cells with

corners at D1 and F3.

return_empty: str If 'true' then empty cells will be returned in the feed.

If omitted, the default is 'false'.

"""

gdata.client.Query.__init__(self, **kwargs)

self.min_row = min_row

self.max_row = max_row

self.min_col = min_col

self.max_col = max_col

self.range = range

self.return_empty = return_empty

def modify_request(self, http_request):

gdata.client._add_query_param('min-row', self.min_row, http_request)

gdata.client._add_query_param('max-row', self.max_row, http_request)

gdata.client._add_query_param('min-col', self.min_col, http_request)

gdata.client._add_query_param('max-col', self.max_col, http_request)

gdata.client._add_query_param('range', self.range, http_request)

gdata.client._add_query_param('return-empty', self.return_empty,

http_request)

gdata.client.Query.modify_request(self, http_request)

ModifyRequest = modify_request