Access spreadsheets using OAuth 2.0 from Python

background

Regarding the use of Google API, access with Google user ID and password was banned in May 2015. Currently you have to access using OAuth 2.0. Regarding authentication from Python to OAuth 2.0, there was surprisingly little information and it took a lot of time, so I will leave the method. This article is about spreadsheets, but I suspect that other APIs that use the gdata library can be authenticated as well (unconfirmed).

environment

python 2.7.5 MacOS X 10.9.5

Authentication method with OAuth 2.0

There are some things you need to prepare for authentication with OAuth 2.0. Leave the steps in order.

1. Enable Drive API

Spreadsheets will be operated using the Drive API of Google Apps API, so enable the required API by the following operations.

Go to the Developers Console (https://console.developers.google.com/project) and select your project. If the project does not exist, create one with an appropriate name.

Select "API" from the menu on the left.

"Drive API" is on the right.

"Enable API"

Enabled.

2. Create client ID for OAuth

Next, you need to create a client ID and key to use with OAuth. I will post the method.

Similarly, on the screen that opens the project in Developers Console, select "Credentials" from the menu on the left.

"Create a new client ID"

Select "Service Account" and "Create Client ID"

The json file will be downloaded locally and a completion dialog will be displayed. (This json file is not used in this procedure)

Confirm that the client ID has been issued. Select "Generate new P12 key" and download the private key (.p12).

The private key was stored locally. (The saved private key is "MyProject.p12")

3. Spreadsheet sharing settings

For the client ID created in the above procedure, it is necessary to set sharing on the SpreadSheet side.

First, make a note of the email address of the issued client ID.

Open the spreadsheet you want to access with the API and select "Share".

Enter the issued email address and click "Send".

This completes the sharing settings.

4. Source code

Now it's finally ready. Access with python code.

First, install the required libraries.

python

$ pip install gdata oauth2client

The authentication part is the following source.

oauth2.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

from oauth2client.client import SignedJwtAssertionCredentials
import gdata.spreadsheets.client

#Information required for authentication
client_email = "[email protected]" #Email address issued in step 2
with open("MyProject.p12") as f: private_key = f.read() #Private key issued in step 2

#Creating credentials
scope = ["https://spreadsheets.google.com/feeds"]
credentials = SignedJwtAssertionCredentials(client_email, private_key,
    scope=scope)

#Preparing the client for the spreadsheet
client = gdata.spreadsheets.client.SpreadsheetsClient()

# OAuth2.Authentication setting at 0
auth_token = gdata.gauth.OAuth2TokenFromCredentials(credentials)
auth_token.authorize(client)

# ----You can now use the library to access your spreadsheet---- #

#Get worksheet
sheets = client.get_worksheets("1TAVVsyhCM_nprkpa0-LGWBheaXt_ipX84fIIhJw2fa0") #Specify Spreadsheet ID
for sheet in sheets.entry:
    print sheet.get_worksheet_id(), sheet.title

If the last print statement is output like this, it is proof that you could access normally.

python

od6 <ns0:title xmlns:ns0="http://www.w3.org/2005/Atom">&#12471;&#12540;&#12488;1</ns0:title>

Error encountered

PKCS12 format is not supported by the PyCrypto library.

error contents

PKCS12 format is not supported by the PyCrypto library. Try converting to a "PEM" (openssl pkcs12 -in xxxxx.p12 -nodes -nocerts > privatekey.pem) or using PyOpenSSL if native code is an option.

The error that occurred in the following part.

with open("MyProject.p12") as f

Correspondence

You can change the format of the private key as it appears in the error statement.

python

$ openssl pkcs12 -in MyProject.p12 -nodes -nocerts > MyProject.pem
Enter Import Password: #Enter "not a secret"

Then change the file to read in the code.

with open("MyProject.pem") as f

reference

GoogleAppEngine-Using Google Drive API with Google App Engine for PHP-Qiita http://qiita.com/hikoalpha/items/04ef84cd5f035ff64f23

python - Using Spreadsheet API OAuth2 with Certificate Authentication - Stack Overflow http://stackoverflow.com/questions/20209178/using-spreadsheet-api-oauth2-with-certificate-authentication/20211057#20211057

Using OAuth 2.0 for Server to Server Applications | Google Identity Platform | Google Developers https://developers.google.com/identity/protocols/OAuth2ServiceAccount?hl=ja

OAuth 2.0   |   API Client Library for Python   |   Google Developers https://developers.google.com/api-client-library/python/guide/aaa_oauth?hl=ja

google app engine - SignedJwtAssertionCredentials on AppEngine doesn't recognize PEM key - Stack Overflow http://stackoverflow.com/questions/17993604/signedjwtassertioncredentials-on-appengine-doesnt-recognize-pem-key

gdata: gdata.spreadsheets.client Namespace Reference - doxygen documentation | Fossies Dox http://fossies.org/dox/gdata-2.0.18/namespacegdata_1_1spreadsheets_1_1client.html