GoogleDocToCSV.py

#!/usr/bin/python

# Script to automate downloading CSV files from GoogleDocs into the lens asset folders
# Note that google broke password authentication in April 2015, so the interface changed slightly
# to use the oauth-supporting library gspread.
#
# Expects a Google oauth key file named google_oauth_key.json, created and downloaded
# from  http://console.developers.google.com
#
# Usage example:
#   #!/usr/bin/python
#   import GoogleDocToCSV
#
#   GoogleDocToCSV.download("Spreadsheet Name", "Worksheet Title", "../Destination/Path/sheet.csv");
#

import json
import gspread
from oauth2client.client import SignedJwtAssertionCredentials
import csv, codecs, cStringIO

class UnicodeWriter:
  """
  A CSV writer which will write rows to CSV file "f",
  which is encoded in the given encoding.
  """

  def __init__(self, f, dialect=csv.excel, encoding="utf-8", **kwds):
    # Redirect output to a queue
    self.queue = cStringIO.StringIO()
    self.writer = csv.writer(self.queue, dialect=dialect, **kwds)
    self.stream = f
    self.encoder = codecs.getincrementalencoder(encoding)()

  def writerow(self, row):
    self.writer.writerow([s.encode("utf-8") for s in row])
    # Fetch UTF-8 output from the queue ...
    data = self.queue.getvalue()
    data = data.decode("utf-8")
    # ... and reencode it into the target encoding
    data = self.encoder.encode(data)
    # write to the target stream
    self.stream.write(data)
    # empty queue
    self.queue.truncate(0)

  def writerows(self, rows):
    for row in rows:
      self.writerow(row)
    

# Just get the file-like object
def download(spreadsheet_title, worksheet_title, destination):
  print "Downloading " + spreadsheet_title + " " + str(worksheet_title) + " to " + destination
  
  json_key = json.load(open('google_oauth_key.json'))
  scope = ['https://spreadsheets.google.com/feeds']
  credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'], scope)
  google = gspread.authorize(credentials) 
  
  worksheet = google.open(spreadsheet_title).worksheet(worksheet_title);
  with open(destination, 'wb') as f:
    writer = UnicodeWriter(f)
    writer.writerows(worksheet.get_all_values())