accessing google sheets through Python_1

Accessing Google Sheets through Python IDE

Google Sheets is a good alternative for storing data and accessing it online. In fact, if we have relatively small data of ~1,000 rows which we want to analyze, then we can go for Google Sheets, instead of creating a database.

 

For one of my projects, I wanted end-users to fill surveys, which can be aggregated in one place, and analyzed. Rather that creating a contact form in WordPress to populate a DB, and then querying it, I have experimented with Google Docs / Forms. The survey responses are stored in a Google Sheet, which I have to access using Python script.

 

I have used the gspread library, through following steps:

1) Go to Google Developers’ console and create a new project

accessing google sheets through Python_1

 

2) Go to ‘Credentials’ > Select ”Service Account Key”

accessing google sheets through Python_2

3) Select ‘App Engine default service account’ under ‘service account’ and ‘JSON’ under ‘Key type’

accessing google sheets through Python_3

4) Open the .json file using notepad, and copy the ‘client_email’ > Save the .json file in a directory

accessing google sheets through Python_4

5) Open Command Prompt > install oauth2client and PyOpenSSL through PIP

 

6) Open the Google Sheet that has to be accessed > Click on ‘File’ > Click on ‘Share’ > enter the client_email from .json file in step#4 > Click done

 

7) Open a new Python file (.py) in the same directory where .json file is saved in step#4, and enter the following code:

import gspread
from oauth2client.service_account import ServiceAccountCredentials


scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('#ENTER JSON FILE NAME HERE#', scope)

gc = gspread.authorize(credentials)

wks = gc.open_by_key('#ENTER KEY OF GOOGLE SHEET HERE#').sheet1
val = wks.acell('#ENTER A CELL NUMBER HERE E.G. B1#').value
print(val)

 

Build will give value of the cell B1