In one of my recent django projects, I had to read some data from database and write it to a google spreadsheet. This needs to be done on regular intervals using a cron job.

Gspread

Gspread saved me from struggling with google docs API. It is much easier to read and write in google sheets from you code, if you are using Gspread.

You can install Gspread using the command

pip install gspread

Or if you want to install the latest code, you can install it from the github repo using the command

pip install https://github.com/burnash/gspread/archive/master.zip

This is going to be a much longer blog. Be patient to read it and try it.

1 - Authentication

You can authenticate using your google username and password or using OAuth2.

Method 1.1: Using username and password

If you going to use your google username and password, then it is a fairly straightforward method. You can use the following code to do that

import gspread
gc = gspread.login('youremail@gmail.com', 'yourpassword')

If you are using google's 2-factor authentication, you can generate an app-specific password and use it as password for authentication. Eventhough this is a very simple method, you have to expose your account password in code which is not recommended.

Method 1.2: Using google oauth token

You can also login using Google oauth token which will not reveal sensitive information such as your password. But this will involve some work. Here the steps are explained clearly.

Sign up at google developer console and create a project. Create Project

You can give a name of your choice and project ID will provided by google. Project Name

Go to Enable an API section to enable APIs of your choice Enable API

In our case, we should enable Drive API and it provides 10,000,000 requests per day for free.

Now go to Credentials under APIs & auth. Click Create new Client ID. Create Client ID

Then select your email and give an product name to proceed. Rest of the fields are optional. Create Client ID form

In the next screen, select your application type and provide authorized URLs. In our case, application type can be Web application and authorized and redirect uri can be http://localhost:8080/. Select App Type

In the next screen, you will be able to see your Client ID and Client Secret. Client ID and Secret

Then click Create Client ID and select Service account option and click create. Create server app

This will prompt you to download a file with file extension *.p12. Download and save this file which will be required for further authentication.

After you download the file, a secret key will be shown to you. Please copy and keep it safe. This will not be shown again. App secret

We are done with google API site. The follwing steps needs to be followed in your system. Now install dependent python libraries using the following command

pip install python-gflags oauth2client

Now copy the following code as a python script or you can download the file get_oauth2_token.py here.

from oauth2client.client import OAuth2WebServerFlow
from oauth2client.tools import run
from oauth2client.file import Storage

CLIENT_ID = '<Client ID from Google API Console>'
CLIENT_SECRET = '<Client secret from Google API Console>'

flow = OAuth2WebServerFlow(
          client_id = CLIENT_ID,
          client_secret = CLIENT_SECRET,
          scope = 'https://spreadsheets.google.com/feeds https://docs.google.com/feeds',
          redirect_uri = 'http://example.com/auth_return'
       )

storage = Storage('creds.data')
credentials = run(flow, storage)
print "access_token: %s" % credentials.access_token

In the above script, replace CLIENT_ID and CLIENT_SECRET from the details from your Google developer console account.

After replacing the details, run the python script using the command

python get_oauth2_token.py

Running this command will open your default browser and if you are not logged into google account already, you will be prompted to login. Then you need to grant access to your google account in the next screen.

App access permission

After you accept access permission you will see the message The authentication flow has completed. in your browser. A file named creds.data will be stored in the same location as your python script. Open the file and find refresh_token in that. The content of the file will be in python dictionary format. Make sure that you copy the refresh token during first time itself. If not you have to repeat the whole process again.

Now we need to write a function for google authentication using the credentials we obtained upto this point.

import requests, gspread
from oauth2client.client import SignedJwtAssertionCredentials

def authenticate_google_docs():
    f = file(os.path.join('your-key-file.p12'), 'rb')
    SIGNED_KEY = f.read()
    f.close()
    scope = ['https://spreadsheets.google.com/feeds', 'https://docs.google.com/feeds']
    credentials = SignedJwtAssertionCredentials('username@gmail.com', SIGNED_KEY, scope)

    data = {
        'refresh_token' : '<refresh-token-copied>',
        'client_id' : '<client-id-copied>',
        'client_secret' : '<client-secret-copied>',
        'grant_type' : 'refresh_token',
    }

    r = requests.post('https://accounts.google.com/o/oauth2/token', data = data)
    credentials.access_token = ast.literal_eval(r.text)['access_token']

    gc = gspread.authorize(credentials)
    return gc

You can get google authectication credentials in a variable like this

gc = authenticate_google_docs()

Now we are at the same point both in method 1.1 and method 1.2. Either you use method 1.1 or method 1.2 the google authentication is stored in the variable gc.

2 - Open Google Spreadsheet

You can open google spreadsheet by supplying spreadsheet Title or URL or Key as given below.

sh = gc.open("My Expenses") # Open by name

sh = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE') # Open by key which can be extracted from spreadsheet's url

sh = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl') # Open by URL

3 - Select Worksheet

You can get a list of all available worsheets or open a worksheet by Name or Index or Sheet order.

worksheet = sh.get_worksheet(0) # Select worksheet by index

worksheet = sh.worksheet("January") # By title

worksheet = sh.sheet1 # By sheet order

worksheet_list = sh.worksheets() # Get list of worksheets

4 - Create Worksheet

You can create a new worksheet in the selected spreadsheet using the following command.

worksheet = sh.add_worksheet(title="A worksheet", rows="100", cols="20")

5 - Delete Worksheet

You can delete a worksheet using the below given command and passing worksheet retrieved in "Select Worksheet".

sh.del_worksheet(worksheet)

6 - Get Cell Value

You can get call value either using cell label or using cell coordinates with the commands given below

val = worksheet.acell('B1').value # With label

val = worksheet.cell(1, 2).value # With coords

7 - Get all values from row or column

If you want to values from an entire row or entire column you can use the following commands

values_list = worksheet.row_values(1)

values_list = worksheet.col_values(1)

8 - Get all values from worksheet as list of lists

You can get all the values from the selected worksheet as list of lists using the following command

list_of_lists = worksheet.get_all_values()

9 - Find cell with given value

You can search for a value in the selected worksheet.

cell = worksheet.find("Mail") # Find a cell with exact string

mail_re = re.compile(r'(Google|Yahoo) Mail') # Use regex to search
cell = worksheet.find(mail_re)

10 - Find all matched cells

You can get all matched cells with given value.

cell_list = worksheet.findall("Mail")

mail_re = re.compile(r'(Google|Yahoo) Mail')
cell_list = worksheet.findall(mail_re)

11 - Get cell properties

You can get properties of the selected cell. Will help you use search function.

value = cell.value # Get cell values
row_number = cell.row # Get row number
column_number = cell.col # Get column number

12 - Update cell

You can update cell(s) using the following commands

worksheet.update_acell('B1', 'Gspread!') # Update using cell label

worksheet.update_cell(1, 2, 'Gspread!') # Update using cell coordinates

# Update cell range
cell_list = worksheet.range('A1:C7')
for cell in cell_list:
    cell.value = 'O_o'

worksheet.update_cells(cell_list) # Update in batch

Now we have learnt about using Gspread to access Google spreadsheets in Python. Feel free to post your comments.