Skip to content

Instantly share code, notes, and snippets.

@mahmoodkhan
Last active August 29, 2015 14:13
Show Gist options
  • Save mahmoodkhan/839b030003326c46df3b to your computer and use it in GitHub Desktop.
Save mahmoodkhan/839b030003326c46df3b to your computer and use it in GitHub Desktop.
Exporting data to Google Spreadsheet using gdata.spreadsheets.client and gdata.gauth.OAuth2Token
from silo.models import Silo, DataField, ValueStore
from django.contrib.auth.decorators import login_required
import json as simplejson
from django.template import RequestContext
from django.contrib.auth.models import User
from django.http import HttpResponseRedirect, HttpResponseBadRequest, HttpResponse
from django.shortcuts import render_to_response, get_object_or_404, redirect, render
from oauth2client.client import flow_from_clientsecrets
from oauth2client.django_orm import Storage
from oauth2client import xsrfutil
from django.conf import settings
from django.views.decorators.csrf import csrf_protect
from .models import GoogleCredentialsModel
from apiclient.discovery import build
import os, logging, httplib2, json, datetime
import gdata.spreadsheets.client
CLIENT_SECRETS = os.path.join(os.path.dirname(__file__), 'client_secrets.json')
FLOW = flow_from_clientsecrets(
CLIENT_SECRETS,
scope='https://www.googleapis.com/auth/drive https://spreadsheets.google.com/feeds',
redirect_uri='http://localhost:8000/oauth2callback/')
@login_required
def google_export(request):
storage = Storage(GoogleCredentialsModel, 'id', request.user, 'credential')
credential = storage.get()
if credential is None or credential.invalid == True:
FLOW.params['state'] = xsrfutil.generate_token(settings.SECRET_KEY, request.user)
authorize_url = FLOW.step1_get_authorize_url()
return HttpResponseRedirect(authorize_url)
else:
#
credential_json = json.loads(credential.to_json())
silo_id = 1
silo_name = Silo.objects.get(pk=silo_id).name
http = httplib2.Http()
# Authorize the http object to be used with "Drive API" service object
http = credential.authorize(http)
# Build the Google Drive API service object
service = build("drive", "v2", http=http)
# The body of "insert" API call for creating a blank Google Spreadsheet
body = {
'title': "TEST2",
'description': "TEST FILE FROM API",
'mimeType': "application/vnd.google-apps.spreadsheet"
}
# Create a new blank Google Spreadsheet file in user's Google Drive
#google_spreadsheet = service.files().insert(body=body).execute()
# Get the spreadsheet_key of the newly created Spreadsheet
#spreadsheet_key = google_spreadsheet['id']
# Create OAuth2Token for authorizing the SpreadsheetClient
token = gdata.gauth.OAuth2Token(
client_id = credential_json['client_id'],
client_secret = credential_json['client_secret'],
scope = 'https://spreadsheets.google.com/feeds',
user_agent = "TOLA",
access_token = credential_json['access_token'],
refresh_token = credential_json['refresh_token'])
# Instantiate the SpreadsheetClient object
sp_client = gdata.spreadsheets.client.SpreadsheetsClient(source="TOLA")
# authorize the SpreadsheetClient object
sp_client = token.authorize(sp_client)
# Create a Spreadsheet Query object: Just for testing purposes
# so that I can work with one spreadsheet instead of creating a new spreadsheet every time.
spreadsheets_query = gdata.spreadsheets.client.SpreadsheetQuery (title="TEST2", title_exact=True)
# Get a XML feed of all the spreadsheets that match the query
spreadsheets_feed = sp_client.get_spreadsheets(query = spreadsheets_query)
# Get the spreadsheet_key of the first match
spreadsheet_key = spreadsheets_feed.entry[0].id.text.rsplit('/',1)[1]
# Create a WorksheetQuery object to allow for filtering for worksheets by the title
worksheet_query = gdata.spreadsheets.client.WorksheetQuery(title="Sheet1", title_exact=True)
# Get a feed of all worksheets in the specified spreadsheet that matches the worksheet_query
worksheets_feed = sp_client.get_worksheets(spreadsheet_key, query=worksheet_query)
# Retrieve the worksheet_key from the first match in the worksheets_feed object
worksheet_key = worksheets_feed.entry[0].id.text.rsplit("/", 1)[1]
# The three lines below is an alternate way of getting to the first worksheet.
#worksheets_feed = sp_client.get_worksheets(spreadsheet_key)
#id_parts = worksheets_feed.entry[0].id.text.split('/')
#worksheet_key = id_parts[len(id_parts) - 1]
# Loop through and print each worksheet's title, rows and columns
#for j, wsentry in enumerate(worksheets_feed.entry):
# print '%s %s - rows %s - cols %s\n' % (j, wsentry.title.text, wsentry.row_count.text, wsentry.col_count.text)
silo_data = ValueStore.objects.all().filter(field__silo__id=silo_id)
num_cols = len(silo_data)
# By default a blank Google Spreadsheet has 26 columns but if our data has more column
# then add more columns to Google Spreadsheet otherwise there would be a 500 Error!
if num_cols and num_cols > 26:
worksheet = worksheets_feed.entry[0]
#worksheet.row_count.text = "1500"
worksheet.col_count.text = str(num_cols)
#worksheet.title.text = "Sheet1"
# Send the worksheet update call to Google Server
sp_client.update(worksheet, force=True)
# Define a Google Spreadsheet range string, where data would be written
range = "R1C1:R1C" + str(num_cols)
# Create a CellQuery object to query the worksheet for all the cells that are in the range
cell_query = gdata.spreadsheets.client.CellQuery(range=range, return_empty='true')
# Retrieve all cells thar match the query as a CellFeed
cells_feed = sp_client.GetCells(spreadsheet_key, worksheet_key, q=cell_query)
# Create a CellBatchUpdate object so that all cells update is sent as one http request
batch = gdata.spreadsheets.data.BuildBatchCellsUpdate(spreadsheet_key, worksheet_key)
#print(type(cells.entry))
print(cells_feed.entry[0].cell)
print(cells_feed.entry[1].cell)
print(cells_feed.entry[2].cell)
# Populate the CellBatchUpdate object with data
n = 0
for row in silo_data:
#print("%s : %s" % (row.field.name, row.char_store))
c = cells_feed.entry[n]
c.cell.input_value = str(row.field.name)
batch.add_batch_entry(c, c.id.text, batch_id_string=c.title.text, operation_string='update')
n = n + 1
# Finally send the CellBatchUpdate object to Google
sp_client.batch(batch, force=True)
"""
# Single Cell Update request
cell_query = gdata.spreadsheets.client.CellQuery(
min_row=1, max_row=1, min_col=1, max_col=1, return_empty=True)
cells = sp_client.GetCells(spreadsheet_key, worksheet_key, q=cell_query)
cell_entry = cells.entry[0]
cell_entry.cell.input_value = 'Address'
sp_client.update(cell_entry)
"""
"""
# Batch update request
range = "R6C1:R1113C4" #"A6:D1113"
cellq = gdata.spreadsheets.client.CellQuery(range=range, return_empty='true')
cells = sp_client.GetCells(spreadsheet_key, worksheet_key, q=cellq)
batch = gdata.spreadsheets.data.BuildBatchCellsUpdate(spreadsheet_key, worksheet_key)
n = 1
for cell in cells.entry:
cell.cell.input_value = str(n)
batch.add_batch_entry(cell, cell.id.text, batch_id_string=cell.title.text, operation_string='update')
n = n + 1
sp_client.batch(batch, force=True)
"""
#return HttpResponse(json.dumps(google_spreadsheet['id']), content_type="application/json")
return HttpResponse("OK")
@login_required
def oauth2callback(request):
if not xsrfutil.validate_token(settings.SECRET_KEY, request.REQUEST['state'], request.user):
return HttpResponseBadRequest()
credential = FLOW.step2_exchange(request.REQUEST)
storage = Storage(GoogleCredentialsModel, 'id', request.user, 'credential')
storage.put(credential)
#print(credential.to_json())
return HttpResponseRedirect("/")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment