How to Write to a Google Sheet with a Python Script

My post How to Write to a Google Spreadsheet with a Perl Script that was published some time ago is still getting a lot of visitors. This is not surprising as cloud computing is a fast-growing business. Below is the chart of number of searches for phrase “Google Sheet” from Google Trends site. The usage of Google Sheet looks increasing over the years.

So now I decided to look how to use python for Google Sheet.

My interest to this topic also come from the idea to have Adsense and Google Analytics data on the Google Drive and do analysis in python. Keeping files on Google Drive provides access to data from different computers or devices and allows sharing with other people.

Setup
I found good post [2] that helped me quickly setup everything that is needed on Google Drive.

Despite of good instructions I run into the following minor issues:
The file on Google Drive should be shared with the email in secret key file. But this email was not the same email that I use for usual login to Google site. Google created some new email, that is starting with project name. Only when I shared the Google Sheet with that new email it started to work.

The sheet name in the below statement for opening file, for some reason did not take upper case. The actual sheet name was “Sheet1″ but the statement below worked with “sheet1″ only ( s in low case):
sheet = client.open(“filename”).sheet1

Reading and Updating Google Sheet
Here is the small example of python code showing how to read or update Google Sheet.

sheet.update_cell(1, 2, "text 34")

for i in range (2):
    sheet.update_cell(1, i+3, i)

# Returns a list of Cell objects from a specified range.
ar=sheet.range('A1:D1') 
for obj in ar:
    print (obj.value)

Once you get understanding how to do basic operation you can write more complicated code as you need. There are many benefits in using files in the cloud.

Full python source code

# -*- coding: utf-8 -*-
import gspread
from oauth2client.service_account import ServiceAccountCredentials


scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)

sheet = client.open("File name").sheet1
sheet.update_cell(1, 2, "text 34")

for i in range (2):
    sheet.update_cell(1, i+3, i)

### Returns a list of Cell objects from a specified range.
ar=sheet.range('A1:D1') 
for obj in ar:nprint (obj.value)

Accessing through Web
We can use the same code to access Google Sheet through web. Here is the test example with flask. Python script is navigating through 4 cells, counting the sum and displaying it on the web. To access Google Sheet we used the same code as before.
Below you can find screenshot of web page and Google Sheet.
To run this example you need:
install flask
put this python file and client_secret.json file into the same flask folder
run this python script from command line
run web browser as on the screenshot.

Below is the python computer code.

from flask import Flask
app = Flask(__name__)

@app.route('/')
def index():
  return 'Hello World!'


@app.route('/user/')
def user(name):

   import gspread
   from oauth2client.service_account import ServiceAccountCredentials
   scope = ['https://spreadsheets.google.com/feeds']
   creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
   client = gspread.authorize(creds)

   sheet = client.open("INSERT_REPORT_NAME_HERE").sheet1
   ar=sheet.range('A1:D1')
   sum=0 
   for obj in ar:
       sum = sum + int(obj.value)
   HTML_string='Hello, {}! Total = {}'
   return HTML_string.format(name , str(sum))  

if __name__ == '__main__':
   app.run(debug=True)

References
1. How to Write to a Google Spreadsheet with a Perl Script
2. Google Spreadsheets and Python