How to access Google Apps API from appengine?

Question

What's the actual task?

I'm trying to access google Apps API (Drive API, Sheets API) from my google appengine standard project. So when a form is submitted, it has to create a new google sheet and write the contents of the form to the sheet and then the sheet has to be stored in the google drive associated with the google service account or authorized email account (ie, email account we gave in the authorized email section)

What I actually tried?

I have used google_auth library to get the appengine's default credentials and I have enabled both drive, sheets API on my gae project console.

from googleapiclient import discovery

from google.auth import app_engine
from google.auth.transport.requests import AuthorizedSession
import httplib2

def creat_sample_sheet():
    credentials = app_engine.Credentials()
    http = AuthorizedSession(credentials)

    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                    'version=v4')
    service = discovery.build('sheets', 'v4', http=http,
                              discoveryServiceUrl=discoveryUrl)

    result = service.spreadsheets().values()
    print result

But it's not working.. Here is the tracback I got...

File "/base/data/home/apps/vidyalay/1.397393333574060152/alumni_registration_dateycollege/sheet_handler.py" in creat_sample_sheet
  30.                               discoveryServiceUrl=discoveryUrl)

File "/base/data/home/apps/vidyalay/1.397393333574060152/lib/oauth2client/_helpers.py" in positional_wrapper
  133.             return wrapped(*args, **kwargs)

File "/base/data/home/apps/vidyalay/1.397393333574060152/lib/googleapiclient/discovery.py" in build
  222.                                         cache)

File "/base/data/home/apps/vidyalay/1.397393333574060152/lib/googleapiclient/discovery.py" in _retrieve_discovery_doc
  269.   resp, content = http.request(actual_url)

Exception Type: TypeError at /alumni_dateycollege/sheet/
Exception Value: request() takes at least 3 arguments (2 given)

Don't know I'm on the right path..

Update

By following this link mentioned by Daniel works for me. But I don't know how to view the created spreadsheet .

Here is my attempt to download the created sheet.

service.spreadsheets().get(spreadsheetId=SHEET_ID, alt='media')

But this creates an get request to https://sheets.googleapis.com/v4/spreadsheets/1awnM7z_aomHx833Z5S_Z-agFusaidmgcCa0FJIFyGE8?alt=json url. But I actually want to pass media to alt parameter instead of json. I tried the above, but it won't work.

Think, I have to follow developers.google.com/identity/protocols/OAuth2ServiceAccount guide to make my service account to access drive or sheet API.. — Avinash Raj
Can you show which docs you were following? You don't seem to be passing the right thing as the http parameter. — Daniel Roseman
google-auth.readthedocs.io/en/latest/…, google-auth.readthedocs.io/en/latest/… — Avinash Raj
Argh, Google documentation is so confusing: there are hundreds of different pages which all contradict each other. But I don't think that library has anything to do with access to Google APIs. You should probably use something like this which uses your App Engine app's service account to construct an authorized http object which you can pass into discovery.build(). — Daniel Roseman
@DanielRoseman it works.. How do I view the created spreadsheet? Is that I need store again the created spreadsheet inside google drive? If yes, then I have to write code for inserting file into google drive.. — Avinash Raj

Avinash Raj Avinash Raj · Accepted Answer · 2016-11-30T14:00:33

I managed to solve the problem on my own.

I have used my google service account to create spreadsheet on the service account's google drive (sheet API takecare of storing the sheet to google drive). Then I gave writer role permission for that particular file to my own gmail id. Now I can be able to view that particular form sheet in my google drive.

from googleapiclient import discovery
import cloudstorage as gcs
# from oauth2client import client
# from oauth2client.contrib import appengine
# from google.appengine.api import memcache
import httplib2
from google.appengine.api import memcache
from oauth2client.contrib.appengine import AppAssertionCredentials
import logging
import os

import io
from googleapiclient.http import MediaIoBaseDownload

logger = logging.getLogger(__name__)
credentials = AppAssertionCredentials(scope='https://www.googleapis.com/auth/spreadsheets')

drive_credentials = AppAssertionCredentials(scope='https://www.googleapis.com/auth/drive')

http = credentials.authorize(httplib2.Http(memcache))
drive_http = drive_credentials.authorize(httplib2.Http(memcache))

discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                'version=v4')
service = discovery.build('sheets', 'v4', http=http,
                          discoveryServiceUrl=discoveryUrl)


def callback(request_id, response, exception):
    if exception:
        # Handle error
        print exception
    else:
        print "Permission Id: %s" % response.get('id')


def list_drive_files(drive_service):
    results = drive_service.files().list(
        pageSize=10).execute()

    items = results.get('files', [])
    if not items:
        print('No files found.')
    else:
        print('Files:')
        for item in items:
            print('{0} ({1})'.format(item['name'], item['id']))

def give_file_permission(drive_service, file_id):
    batch = drive_service.new_batch_http_request(callback=callback)
    user_permission = {
        'type': 'user',
        'role': 'writer',
        'emailAddress': '[email protected]' # email address of the user you want to give permission
    }
    batch.add(drive_service.permissions().create(
        fileId=file_id,
        body=user_permission,
        fields='id',
    ))
    batch.execute()

def creat_sample_sheet():

    # data = {'properties': {'title': 'Academic Sheet'}}
    # res = sheet_service.spreadsheets().create(body=data).execute()

    # SHEET_ID = res['spreadsheetId']
    SHEET_ID = '1awnM7z_aomHx833Z5S_Z-agFusaidmgcCa0FJIFyGE8'


    sheet = service.spreadsheets().get(spreadsheetId=SHEET_ID, includeGridData=True)
    drive_service = discovery.build('drive', 'v3', http=drive_http)
    list_drive_files(drive_service)

    # Sharing a file
    # file_id = '1bgvJdXG0eg2JGaNlIcdtde_XJlg2gAUT_DOzHi75zys'



def write_to_sheet(form):
    logger.info('Inside write to sheet')
    first_name = form.cleaned_data.get('first_name', '')
    sur_name = form.cleaned_data.get('sur_name', '')
    email = form.cleaned_data.get('e_mail', '')
    phone_no = form.cleaned_data.get('mobile_phone', '')
    year_of_passing = form.cleaned_data.get('year_of_passing', '')
    present_occupation = form.cleaned_data.get('present_occupation', '')
    present_city = form.cleaned_data.get('present_city', '')
    courses_attended = ', '.join([str(i) for i in form.cleaned_data.get('courses_attended', '')])
    volunteer = form.cleaned_data.get('volunteer', '')

    fields = [ 'first_name',  'sur_name', 'e_mail', 'mobile_phone', 'year_of_passing', 'present_occupation', 'present_city', 'courses_attended' , 'volunteer' ]
    # data = {'properties': {'title': 'Form Sheet'}}

    # # create sheet
    # res = service.spreadsheets().create(body=data).execute()
    # # sheet_id = res['spreadsheetId']
    sheet_id = '1bgvJdXG0eg2JGaNlIcdtde_XJlg2gAUT_DOzHi75zys'

    # print sheet_id

    # update sheet
    data = {'values': [[first_name, sur_name, email, phone_no, year_of_passing, present_occupation,
                                present_city, courses_attended, volunteer]]}
    service.spreadsheets().values().append(spreadsheetId=sheet_id,
    range='A1', body=data, valueInputOption='RAW').execute()

    # Getting rows
    # rows = service.spreadsheets().values().get(spreadsheetId=sheet_id,
    # range='Sheet1').execute().get('values', [])
    # for row in rows:
    #     print row

How to access Google Apps API from appengine?

1 Answers