Google Setup

Before you jump into the code, there is some initial setup needed on the Google Sheets.

First, create yourself a new sheet. You can skip this step if you have one already set up. I’m using a list of rally cars for this example:

Now you need to set up your sharing options. You will need to generate Signed Credentials, something that sounds more difficult than it is. Navigate to the Google Developers Console and create a new project (or use an existing one):

Give your project a suitable name and then click create:

Underneath Google Apps APIs select Drive API:

Choose Enable:

Now select Credentials on the left menu:

Click the little arrow on the Create credentials button:

Now select Service account key:

Choose App Engine default service account under Service account and JSON as the format:

Click create, and you should get a .json file download. Move this into your project directory and rename it creds.json. Finally, open the file and look for client_email. This should be the name of your project at appspot.gserviceaccount.com. Share your Google Sheet with this email address (Top Right > Share > Enter Email).

That’s it for the Google Drive side.

Python Setup

There are two main Python versions: 2.7 and 3.x. I’ll be using 2.7, but it does not really matter what you use. The Python wiki does break down the differences if you are interested. You may wish to install a Virtual Environment. That is outside the scope of this article, but good practice.

First, open a new terminal. You will need to use pip to install some Python packages. This is a recommend tool that makes it very easy to manage packages. It comes with Python.

You need to install an oauth2client. Oauth is a web authorization framework. I’ll not be discussing the details of it, but it’s necessary for everything to work correctly and securely. It’s easy to install using pip:

pip install oauth2client

You may need to install PyOpenSSL as well, depending on your setup:

pip install PyOpenSSL

Now you need to install Gspread by Anton Burnashev on GitHub. This is an excellent library written to make it easy to access Google Sheets in Python. Again, this is easy to install using pip:

pip install gspread

Now open your favorite text editor (I’m using Sublime Text 3). Create a new Python file and save it in your project directory as google_io.py. Here’s the test code:

print 'Hello, World!'

Go back to your Terminal and navigate to your project directory. You can do this through the cd command. You can use ls to list files, and pwd to show your working directory.

Once in your project directory, you can execute your Python script like this:

python google_io.py

You should now see Hello, World! in your command line:

Now that Python is working correctly, lets go ahead and setup the libraries. Delete the hello world code. Now import Gspread and execute the code again:

import gspread

If things are working correctly, nothing will happen. If you get an error, perhaps saying no module named X where X is the name of the module you typed (Gspread) go and double check pip installed the module correctly, and that you have not made a typographical error.

This simply retrieves your details from the .json file, and then uses them to authenticate with Google. It then opens a sheet called MUO_Python_Sheet. You may need to change this to the name of your sheet (providing you have shared it correctly). Python is case sensitive, so make sure you enter this code correctly.

Reading

Now that everything is setup, it’s trivial to read or write data. Here’s how you select a range of cells (in this case, all of the car cells):

all_cells = sheet.range('A1:C6')
print all_cells

Here’s what that looks like:

Not very nice is it? Python has dumped the contents of the object, with no regard for formatting. Because this is stored in the all_cells variable, it can be accessed like any other Python object. Here’s how you print all the cell values in a nicer format:

for cell in all_cells:
print cell.value

And that looks like this:

It’s possible to access cells individually (although this is slow if you do it lots of times):

A1 = sheet.acell('A2').value # this cell contains "Ford"

Or you can use the cell coordinates:

coord = sheet.cell(3, 0).value

It’s easy to get all the values for a row:

row = sheet.row_values(1) # first row

Or you can get a whole column. This gets the Model row:

col = sheet.col_values(2) # models

Keep in mind that these two methods do not know how much data you have. If you only have three rows, multiple extra empty cells will be returned. It’s nearly always better to access a predefined block of cells.

Writing

It’s just as easy to write back into the sheet, and you can use cell names or coordinates, just like when reading:

If you are writing to an important sheet, you may wish to consider a safety cell. Store a value in a certain cell (I use “Don’t delete this”) and then read that cell first. If the contents have changed, then columns have been added or removed in your sheet, so don’t proceed writing! Here’s how that could be achieved:

if sheet.acell('B3') != 'SAFETY':
# something has changed in the sheet, DO NOT PROCEED
raise Exception("Oh My, I'm not ready for this.")
else:
# continue with your writing
sheet.update_acell('C2','Blue')

This is good practice. It ensures your script cannot accidentally write into the wrong column. It’s not a substitute for proper backups (you do have backups, right?).

This appears to be a good step-by-step guide - which as a novice I am trying to follow - however you write:

Finally, open the file and look for client_email. This should be the name of your project at appspot.gserviceaccount.com. Share your Google Sheet with this email address (Top Right > Share > Enter Email).

I have a couple of questions:
1) Where can I see 'the name of your project at appspot.gserviceaccount.com' ?

2) Does this creds.json have to be edited - or just copy the "client_email": "##############-compute@developer.gserviceaccount.com" to share with the sheet ?

I followed everything up through authorizing the credentials. I did have to add the drive API to the scope and that helped. I shared the client_email in Sheets, and everything works up until I try to file.open("filename").sheet1.

Joe is a graduate in Computer Science from the University of Lincoln, UK. He's a professional software developer, and when he's not flying drones or writing music, he can often be found taking photos or producing videos.