Pinterest Stumbleupon Whatsapp
Advertisement

Python is an excellent programming language. The syntax may seem strange and unusual, however it is easy to learn and use. It powers Minecraft Pi Edition Learn Python and Electronics with Minecraft Pi Edition Learn Python and Electronics with Minecraft Pi Edition Have you always wanted to learn to code but didn't know where to start? Learn how to control Minecraft on the Raspberry Pi using Python and some simple electronics. Read More , along with a slew of websites and academic research projects.

In this article, I’ll be showing you how to read and write to Google Sheets using Python. Make sure you read our reasons why Python programming is not useless 5 Reasons Why Python Programming Is Not Useless 5 Reasons Why Python Programming Is Not Useless Python -- You either love it or you hate it. You might even swing from one end to the other like a pendulum. Regardless, Python is a language that's hard to be ambivalent about. Read More and our five Google Sheets settings 5 Google Sheets Settings Essential for Teamwork 5 Google Sheets Settings Essential for Teamwork Google Sheets has more features to support collective data input and processing than you may think. We'll introduce you to Google Sheets functions and features that are essential for group projects. Read More that are essential for teamwork. They’ll provide some useful background knowledge.

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:

google sheets rally cars

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):

Advertisement

google developers console

Give your project a suitable name and then click create:

developers console project name

Underneath Google Apps APIs select Drive API:

google apps apis

Choose Enable:

google_api_enable

Now select Credentials on the left menu:

google api credentials

Click the little arrow on the Create credentials button:

google api create credentials

Now select Service account key:

google api service account

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

google api service account creation

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.

If you are running Microsoft Windows, you may need to download and install Python. Mac OS comes with Python already installed. As I’m using a Mac, this project will be created as such. You should be able to follow reasonably well on a Windows or Linux Machine. Make sure you read our guide Hey Windows User, Should You Switch To Linux or Mac? Hey Windows User, Should You Switch To Linux or Mac? Did you ever consider switching from Windows to Linux or Mac? The quick answer: if you're on Windows, stay on Windows—and don't worry about upgrading just yet. Here's why. Read More as to whether you should switch.

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:

python hello world

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.

Here’s the code to get you started:

import json
import gspread
from oauth2client.client import SignedJwtAssertionCredentials

json_key = json.load(open('creds.json')) # json credentials you downloaded earlier
scope = ['https://spreadsheets.google.com/feeds']

credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'].encode(), scope) # get email and key from creds

file = gspread.authorize(credentials) # authenticate with Google
sheet = file.open("MUO_Python_Sheet").sheet1 # open sheet

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:

google_sheets_python_read

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:

python google sheets all values

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:

sheet.update_acell('C2', 'Blue')

sheet.update_cell(2, 3, 'Blue')

The project page on GitHub has many more examples.

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?).

Now that you know the basics, go and make something cool! Learn how to use cron How to Schedule Tasks in Linux with Cron and Crontab How to Schedule Tasks in Linux with Cron and Crontab The ability to automate tasks is one of those futuristic technologies that is already here. Every Linux user can benefit from scheduling system and user tasks, thanks to cron, an easy-to-use background service. Read More to schedule tasks in Linux (or checkout a Windows alternative How To Run Linux Style Cron Jobs on Windows How To Run Linux Style Cron Jobs on Windows Read More ). Maybe you could power a photo frame ShowerThoughts and EarthPorn: Make an Inspiring Raspberry Pi Photo Frame ShowerThoughts and EarthPorn: Make an Inspiring Raspberry Pi Photo Frame Read More or make an office dashboard — that’s what I did!

Have you interacted with Google Sheets before? Let us know your experiences in the comments below!

Leave a Reply

Your email address will not be published. Required fields are marked *