/gdata/spreadsheet/service.py

  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""")