/gdata/spreadsheet/service.py
Python | 484 lines | 432 code | 13 blank | 39 comment | 6 complexity | d6f792f2200714cf96eff187ad200c1d MD5 | raw file
1#!/usr/bin/python 2# 3# Copyright (C) 2007 Google Inc. 4# 5# Licensed under the Apache License, Version 2.0 (the "License"); 6# you may not use this file except in compliance with the License. 7# You may obtain a copy of the License at 8# 9# http://www.apache.org/licenses/LICENSE-2.0 10# 11# Unless required by applicable law or agreed to in writing, software 12# distributed under the License is distributed on an "AS IS" BASIS, 13# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 14# See the License for the specific language governing permissions and 15# limitations under the License. 16 17"""SpreadsheetsService extends the GDataService to streamline Google 18Spreadsheets operations. 19 20 SpreadsheetService: Provides methods to query feeds and manipulate items. 21 Extends GDataService. 22 23 DictionaryToParamList: Function which converts a dictionary into a list of 24 URL arguments (represented as strings). This is a 25 utility function used in CRUD operations. 26""" 27 28__author__ = 'api.laurabeth@gmail.com (Laura Beth Lincoln)' 29 30 31import gdata 32import atom.service 33import gdata.service 34import gdata.spreadsheet 35import atom 36 37 38class Error(Exception): 39 """Base class for exceptions in this module.""" 40 pass 41 42 43class RequestError(Error): 44 pass 45 46 47class SpreadsheetsService(gdata.service.GDataService): 48 """Client for the Google Spreadsheets service.""" 49 50 def __init__(self, email=None, password=None, source=None, 51 server='spreadsheets.google.com', additional_headers=None, 52 **kwargs): 53 """Creates a client for the Google Spreadsheets service. 54 55 Args: 56 email: string (optional) The user's email address, used for 57 authentication. 58 password: string (optional) The user's password. 59 source: string (optional) The name of the user's application. 60 server: string (optional) The name of the server to which a connection 61 will be opened. Default value: 'spreadsheets.google.com'. 62 **kwargs: The other parameters to pass to gdata.service.GDataService 63 constructor. 64 """ 65 gdata.service.GDataService.__init__( 66 self, email=email, password=password, service='wise', source=source, 67 server=server, additional_headers=additional_headers, **kwargs) 68 69 def GetSpreadsheetsFeed(self, key=None, query=None, visibility='private', 70 projection='full'): 71 """Gets a spreadsheets feed or a specific entry if a key is defined 72 Args: 73 key: string (optional) The spreadsheet key defined in /ccc?key= 74 query: DocumentQuery (optional) Query parameters 75 76 Returns: 77 If there is no key, then a SpreadsheetsSpreadsheetsFeed. 78 If there is a key, then a SpreadsheetsSpreadsheet. 79 """ 80 81 uri = ('http://%s/feeds/spreadsheets/%s/%s' 82 % (self.server, visibility, projection)) 83 84 if key is not None: 85 uri = '%s/%s' % (uri, key) 86 87 if query != None: 88 query.feed = uri 89 uri = query.ToUri() 90 91 if key: 92 return self.Get(uri, 93 converter=gdata.spreadsheet.SpreadsheetsSpreadsheetFromString) 94 else: 95 return self.Get(uri, 96 converter=gdata.spreadsheet.SpreadsheetsSpreadsheetsFeedFromString) 97 98 def GetWorksheetsFeed(self, key, wksht_id=None, query=None, 99 visibility='private', projection='full'): 100 """Gets a worksheets feed or a specific entry if a wksht is defined 101 Args: 102 key: string The spreadsheet key defined in /ccc?key= 103 wksht_id: string (optional) The id for a specific worksheet entry 104 query: DocumentQuery (optional) Query parameters 105 106 Returns: 107 If there is no wksht_id, then a SpreadsheetsWorksheetsFeed. 108 If there is a wksht_id, then a SpreadsheetsWorksheet. 109 """ 110 111 uri = ('http://%s/feeds/worksheets/%s/%s/%s' 112 % (self.server, key, visibility, projection)) 113 114 if wksht_id != None: 115 uri = '%s/%s' % (uri, wksht_id) 116 117 if query != None: 118 query.feed = uri 119 uri = query.ToUri() 120 121 if wksht_id: 122 return self.Get(uri, 123 converter=gdata.spreadsheet.SpreadsheetsWorksheetFromString) 124 else: 125 return self.Get(uri, 126 converter=gdata.spreadsheet.SpreadsheetsWorksheetsFeedFromString) 127 128 def AddWorksheet(self, title, row_count, col_count, key): 129 """Creates a new worksheet in the desired spreadsheet. 130 131 The new worksheet is appended to the end of the list of worksheets. The 132 new worksheet will only have the available number of columns and cells 133 specified. 134 135 Args: 136 title: str The title which will be displayed in the list of worksheets. 137 row_count: int or str The number of rows in the new worksheet. 138 col_count: int or str The number of columns in the new worksheet. 139 key: str The spreadsheet key to the spreadsheet to which the new 140 worksheet should be added. 141 142 Returns: 143 A SpreadsheetsWorksheet if the new worksheet was created succesfully. 144 """ 145 new_worksheet = gdata.spreadsheet.SpreadsheetsWorksheet( 146 title=atom.Title(text=title), 147 row_count=gdata.spreadsheet.RowCount(text=str(row_count)), 148 col_count=gdata.spreadsheet.ColCount(text=str(col_count))) 149 return self.Post(new_worksheet, 150 'http://%s/feeds/worksheets/%s/private/full' % (self.server, key), 151 converter=gdata.spreadsheet.SpreadsheetsWorksheetFromString) 152 153 def UpdateWorksheet(self, worksheet_entry, url=None): 154 """Changes the size and/or title of the desired worksheet. 155 156 Args: 157 worksheet_entry: SpreadsheetWorksheet The new contents of the 158 worksheet. 159 url: str (optional) The URL to which the edited worksheet entry should 160 be sent. If the url is None, the edit URL from the worksheet will 161 be used. 162 163 Returns: 164 A SpreadsheetsWorksheet with the new information about the worksheet. 165 """ 166 target_url = url or worksheet_entry.GetEditLink().href 167 return self.Put(worksheet_entry, target_url, 168 converter=gdata.spreadsheet.SpreadsheetsWorksheetFromString) 169 170 def DeleteWorksheet(self, worksheet_entry=None, url=None): 171 """Removes the desired worksheet from the spreadsheet 172 173 Args: 174 worksheet_entry: SpreadsheetWorksheet (optional) The worksheet to 175 be deleted. If this is none, then the DELETE reqest is sent to 176 the url specified in the url parameter. 177 url: str (optaional) The URL to which the DELETE request should be 178 sent. If left as None, the worksheet's edit URL is used. 179 180 Returns: 181 True if the worksheet was deleted successfully. 182 """ 183 if url: 184 target_url = url 185 else: 186 target_url = worksheet_entry.GetEditLink().href 187 return self.Delete(target_url) 188 189 def GetCellsFeed(self, key, wksht_id='default', cell=None, query=None, 190 visibility='private', projection='full'): 191 """Gets a cells feed or a specific entry if a cell is defined 192 Args: 193 key: string The spreadsheet key defined in /ccc?key= 194 wksht_id: string The id for a specific worksheet entry 195 cell: string (optional) The R1C1 address of the cell 196 query: DocumentQuery (optional) Query parameters 197 198 Returns: 199 If there is no cell, then a SpreadsheetsCellsFeed. 200 If there is a cell, then a SpreadsheetsCell. 201 """ 202 203 uri = ('http://%s/feeds/cells/%s/%s/%s/%s' 204 % (self.server, key, wksht_id, visibility, projection)) 205 206 if cell != None: 207 uri = '%s/%s' % (uri, cell) 208 209 if query != None: 210 query.feed = uri 211 uri = query.ToUri() 212 213 if cell: 214 return self.Get(uri, 215 converter=gdata.spreadsheet.SpreadsheetsCellFromString) 216 else: 217 return self.Get(uri, 218 converter=gdata.spreadsheet.SpreadsheetsCellsFeedFromString) 219 220 def GetListFeed(self, key, wksht_id='default', row_id=None, query=None, 221 visibility='private', projection='full'): 222 """Gets a list feed or a specific entry if a row_id is defined 223 Args: 224 key: string The spreadsheet key defined in /ccc?key= 225 wksht_id: string The id for a specific worksheet entry 226 row_id: string (optional) The row_id of a row in the list 227 query: DocumentQuery (optional) Query parameters 228 229 Returns: 230 If there is no row_id, then a SpreadsheetsListFeed. 231 If there is a row_id, then a SpreadsheetsList. 232 """ 233 234 uri = ('http://%s/feeds/list/%s/%s/%s/%s' 235 % (self.server, key, wksht_id, visibility, projection)) 236 237 if row_id is not None: 238 uri = '%s/%s' % (uri, row_id) 239 240 if query is not None: 241 query.feed = uri 242 uri = query.ToUri() 243 244 if row_id: 245 return self.Get(uri, 246 converter=gdata.spreadsheet.SpreadsheetsListFromString) 247 else: 248 return self.Get(uri, 249 converter=gdata.spreadsheet.SpreadsheetsListFeedFromString) 250 251 def UpdateCell(self, row, col, inputValue, key, wksht_id='default'): 252 """Updates an existing cell. 253 254 Args: 255 row: int The row the cell to be editted is in 256 col: int The column the cell to be editted is in 257 inputValue: str the new value of the cell 258 key: str The key of the spreadsheet in which this cell resides. 259 wksht_id: str The ID of the worksheet which holds this cell. 260 261 Returns: 262 The updated cell entry 263 """ 264 row = str(row) 265 col = str(col) 266 # make the new cell 267 new_cell = gdata.spreadsheet.Cell(row=row, col=col, inputValue=inputValue) 268 # get the edit uri and PUT 269 cell = 'R%sC%s' % (row, col) 270 entry = self.GetCellsFeed(key, wksht_id, cell) 271 for a_link in entry.link: 272 if a_link.rel == 'edit': 273 entry.cell = new_cell 274 return self.Put(entry, a_link.href, 275 converter=gdata.spreadsheet.SpreadsheetsCellFromString) 276 277 def _GenerateCellsBatchUrl(self, spreadsheet_key, worksheet_id): 278 return ('http://spreadsheets.google.com/feeds/cells/%s/%s/' 279 'private/full/batch' % (spreadsheet_key, worksheet_id)) 280 281 def ExecuteBatch(self, batch_feed, url=None, spreadsheet_key=None, 282 worksheet_id=None, 283 converter=gdata.spreadsheet.SpreadsheetsCellsFeedFromString): 284 """Sends a batch request feed to the server. 285 286 The batch request needs to be sent to the batch URL for a particular 287 worksheet. You can specify the worksheet by providing the spreadsheet_key 288 and worksheet_id, or by sending the URL from the cells feed's batch link. 289 290 Args: 291 batch_feed: gdata.spreadsheet.SpreadsheetsCellFeed A feed containing 292 BatchEntry elements which contain the desired CRUD operation and 293 any necessary data to modify a cell. 294 url: str (optional) The batch URL for the cells feed to which these 295 changes should be applied. This can be found by calling 296 cells_feed.GetBatchLink().href. 297 spreadsheet_key: str (optional) Used to generate the batch request URL 298 if the url argument is None. If using the spreadsheet key to 299 generate the URL, the worksheet id is also required. 300 worksheet_id: str (optional) Used if the url is not provided, it is 301 oart of the batch feed target URL. This is used with the spreadsheet 302 key. 303 converter: Function (optional) Function to be executed on the server's 304 response. This function should take one string as a parameter. The 305 default value is SpreadsheetsCellsFeedFromString which will turn the result 306 into a gdata.spreadsheet.SpreadsheetsCellsFeed object. 307 308 Returns: 309 A gdata.BatchFeed containing the results. 310 """ 311 312 if url is None: 313 url = self._GenerateCellsBatchUrl(spreadsheet_key, worksheet_id) 314 return self.Post(batch_feed, url, converter=converter) 315 316 def InsertRow(self, row_data, key, wksht_id='default'): 317 """Inserts a new row with the provided data 318 319 Args: 320 uri: string The post uri of the list feed 321 row_data: dict A dictionary of column header to row data 322 323 Returns: 324 The inserted row 325 """ 326 new_entry = gdata.spreadsheet.SpreadsheetsList() 327 for k, v in row_data.iteritems(): 328 new_custom = gdata.spreadsheet.Custom() 329 new_custom.column = k 330 new_custom.text = v 331 new_entry.custom[new_custom.column] = new_custom 332 # Generate the post URL for the worksheet which will receive the new entry. 333 post_url = 'http://spreadsheets.google.com/feeds/list/%s/%s/private/full'%( 334 key, wksht_id) 335 return self.Post(new_entry, post_url, 336 converter=gdata.spreadsheet.SpreadsheetsListFromString) 337 338 def UpdateRow(self, entry, new_row_data): 339 """Updates a row with the provided data 340 341 If you want to add additional information to a row, it is often 342 easier to change the values in entry.custom, then use the Put 343 method instead of UpdateRow. This UpdateRow method will replace 344 the contents of the row with new_row_data - it will change all columns 345 not just the columns specified in the new_row_data dict. 346 347 Args: 348 entry: gdata.spreadsheet.SpreadsheetsList The entry to be updated 349 new_row_data: dict A dictionary of column header to row data 350 351 Returns: 352 The updated row 353 """ 354 entry.custom = {} 355 for k, v in new_row_data.iteritems(): 356 new_custom = gdata.spreadsheet.Custom() 357 new_custom.column = k 358 new_custom.text = v 359 entry.custom[k] = new_custom 360 for a_link in entry.link: 361 if a_link.rel == 'edit': 362 return self.Put(entry, a_link.href, 363 converter=gdata.spreadsheet.SpreadsheetsListFromString) 364 365 def DeleteRow(self, entry): 366 """Deletes a row, the provided entry 367 368 Args: 369 entry: gdata.spreadsheet.SpreadsheetsList The row to be deleted 370 371 Returns: 372 The delete response 373 """ 374 for a_link in entry.link: 375 if a_link.rel == 'edit': 376 return self.Delete(a_link.href) 377 378 379class DocumentQuery(gdata.service.Query): 380 381 def _GetTitleQuery(self): 382 return self['title'] 383 384 def _SetTitleQuery(self, document_query): 385 self['title'] = document_query 386 387 title = property(_GetTitleQuery, _SetTitleQuery, 388 doc="""The title query parameter""") 389 390 def _GetTitleExactQuery(self): 391 return self['title-exact'] 392 393 def _SetTitleExactQuery(self, document_query): 394 self['title-exact'] = document_query 395 396 title_exact = property(_GetTitleExactQuery, _SetTitleExactQuery, 397 doc="""The title-exact query parameter""") 398 399 400class CellQuery(gdata.service.Query): 401 402 def _GetMinRowQuery(self): 403 return self['min-row'] 404 405 def _SetMinRowQuery(self, cell_query): 406 self['min-row'] = cell_query 407 408 min_row = property(_GetMinRowQuery, _SetMinRowQuery, 409 doc="""The min-row query parameter""") 410 411 def _GetMaxRowQuery(self): 412 return self['max-row'] 413 414 def _SetMaxRowQuery(self, cell_query): 415 self['max-row'] = cell_query 416 417 max_row = property(_GetMaxRowQuery, _SetMaxRowQuery, 418 doc="""The max-row query parameter""") 419 420 def _GetMinColQuery(self): 421 return self['min-col'] 422 423 def _SetMinColQuery(self, cell_query): 424 self['min-col'] = cell_query 425 426 min_col = property(_GetMinColQuery, _SetMinColQuery, 427 doc="""The min-col query parameter""") 428 429 def _GetMaxColQuery(self): 430 return self['max-col'] 431 432 def _SetMaxColQuery(self, cell_query): 433 self['max-col'] = cell_query 434 435 max_col = property(_GetMaxColQuery, _SetMaxColQuery, 436 doc="""The max-col query parameter""") 437 438 def _GetRangeQuery(self): 439 return self['range'] 440 441 def _SetRangeQuery(self, cell_query): 442 self['range'] = cell_query 443 444 range = property(_GetRangeQuery, _SetRangeQuery, 445 doc="""The range query parameter""") 446 447 def _GetReturnEmptyQuery(self): 448 return self['return-empty'] 449 450 def _SetReturnEmptyQuery(self, cell_query): 451 self['return-empty'] = cell_query 452 453 return_empty = property(_GetReturnEmptyQuery, _SetReturnEmptyQuery, 454 doc="""The return-empty query parameter""") 455 456 457class ListQuery(gdata.service.Query): 458 459 def _GetSpreadsheetQuery(self): 460 return self['sq'] 461 462 def _SetSpreadsheetQuery(self, list_query): 463 self['sq'] = list_query 464 465 sq = property(_GetSpreadsheetQuery, _SetSpreadsheetQuery, 466 doc="""The sq query parameter""") 467 468 def _GetOrderByQuery(self): 469 return self['orderby'] 470 471 def _SetOrderByQuery(self, list_query): 472 self['orderby'] = list_query 473 474 orderby = property(_GetOrderByQuery, _SetOrderByQuery, 475 doc="""The orderby query parameter""") 476 477 def _GetReverseQuery(self): 478 return self['reverse'] 479 480 def _SetReverseQuery(self, list_query): 481 self['reverse'] = list_query 482 483 reverse = property(_GetReverseQuery, _SetReverseQuery, 484 doc="""The reverse query parameter""")