Access and Modify Google Sheet using Python

This would be my first blog where I am using Python. I am excited to share what I did using Python. I wanted to update google sheet using bash shell scripting but did not found anything I can play around using bash. Later, I opted for Python as it provides “gspread” library which could be used to work on google sheet.

Scenario: Modify google sheet using python.

Prerequisites:

1. The system should have python, gcc and pip installed or run the following command to install them:

   [js]sudo apt-get install python -y
   wget https://bootstrap.pypa.io/get-pip.py
   python get-pip.py
   apt-get install gcc -y[/js]

2. We need to install “gspread” library of Python and install/upgrade google-api-python-client to authenticate and work on google sheets as follow:

   [js]pip install gspread
   pip install –upgrade google-api-python-client[/js]

Now, we are ready to start working with google sheets as below:

Authentication: The first step would be to authenticate to our google account in order to get access to google sheets present in our account.
Python’s gspread library provide an api to directly login into google account but it has stopped working since April 20, 2015 and when used it throws following exception:

[js]gc = gspread.login(‘abc@xyz.com’, ‘P@SSw0Rd’)
/usr/local/lib/python2.7/dist-packages/gspread/client.py:73: Warning: 
           ClientLogin is deprecated:
           https://developers.google.com/identity/protocols/AuthForInstalledApps?csw=1        
           Authorization with email and password will stop working on April 20, 2015.            
           Please use oAuth2 authorization instead:
           http://gspread.readthedocs.org/en/latest/oauth2.html
 """, Warning)
/usr/local/lib/python2.7/dist-packages/requests/packages/urllib3/util/ssl_.py:318: SNIMissingWarning: An HTTPS request has been made, but the SNI (Subject Name Indication) extension to TLS is not available on this platform. This may cause the server to present an incorrect TLS certificate, which can cause validation failures. You can upgrade to a newer version of Python to solve this. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#snimissingwarning.
SNIMissingWarning
/usr/local/lib/python2.7/dist-packages/requests/packages/urllib3/util/ssl_.py:122: InsecurePlatformWarning: A true SSLContext object is not available. This prevents urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. You can upgrade to a newer version of Python to solve this. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning.
InsecurePlatformWarning
Traceback (most recent call last):
 File "<stdin>", line 1, in <module>
 File "/usr/local/lib/python2.7/dist-packages/gspread/client.py", line 318, in login   client.login()
 File "/usr/local/lib/python2.7/dist-packages/gspread/client.py", line 127, in login
   "Unable to authenticate. %s" % ex.message)
<span style="font-weight: 400;">gspread.exceptions.AuthenticationError: Unable to authenticate. 404: </span><a href="https://developers.google.com/accounts/docs/AuthForInstalledApps"><span style="font-weight: 400;">https://developers.google.com/accounts/docs/AuthForInstalledApps</span></a><span style="font-weight: 400;"> [/js]

We can use OAuth token provided by google for authentication which is a better security practice as it does not expose sensitive information. Below are the steps to get OAuth token and download it as JSON file:

1. Login into the google developer console with your google credentials and we will see the following page:
   

• Click on “My Projects” highlighted above and create a new project if there is no any project, otherwise, the same project can be used:
  

• Select the project created in step 1. We would need to enable Google API for google drive. Click “Drive API” as below and enable it:
  

• Click “Credentials” on the left panel and then click “Create Credentials” as shown below:
  

• Select “Service account key” from the list:
  
• On the next page, select service account as “App Engine default service account”, key type as “JSON” and click “Create” as below and a JSON file will be downloaded on our system:
  

We would use this JSON (say credentials.json) for authentication into our google account.

1. Now, we would need to share the google sheet  with the client email(the same way we share it with others)  which could be found inside the JSON file against “client_email” field.
2. Below code will help us to authenticate into the google account using credentials.json file:

   [js]import gspread
   from oauth2client.service_account import ServiceAccountCredentials
   scope = "https://spreadsheets.google.com/feeds"
   credentials = ServiceAccountCredentials.from_json_keyfile_name(‘credential.json’, scope)
   gs = gspread.authorize(credentials)[/js]

3. We are now authenticated into google account and can use “gs” object to access any google sheet (say “Demo Sheet”) present in our account as follows:

   [js]gsheet = gs.open("Demo Sheet")[/js]

4. Select one worksheet (say “sheet1”) to work on as follows:

   [js]wsheet = gsheet.worksheet("sheet1") [/js]

Now, we got an object using which we can edit the worksheet. Below are few use cases which I used frequently.

1. Read data from one cell (say “B5” cell) as below:
   

   [js]wsheet.get_int_addr(‘B5’)[/js]

2. Read data from a cell range (say ‘B3:B13’) as below:
   

   [js]wsheet.range(‘B3:B13’)[/js]

3. Update a single cell (say ‘A4’ with 24) as below:
   

   [js]update_acell(‘A4’), ‘24’)[/js]

4.  Copy data from one cell range to another(say ‘B3:B13’ to ‘D2:D12’) as below:

   [js]range1 = wks.range(‘B3:B13’)
   range2 = wks.range(‘C3:C13’)
   for range1_cell,range2_cell in zip(range1,range2):
   range2_cell.value = range1_cell.value
   wks.update_cells(previous)[/js]

Python’s gspread becomes very handy when it comes to automating tasks which involve updating google sheets.