Exporting data to Google Spreadsheet using gdata.spreadsheets.client and gdata.gauth.OAuth2Token

google_spreadsheet.py

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