How to Read and Write to Google Sheets With Python
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, 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. If you’re just beginning your Python journey, these 5 best websites to learn Python are a good starting point.
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:
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.
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 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
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:
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:
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.
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.
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 to schedule tasks in Linux (or checkout a Windows alternative). Maybe you could power a photo frame or make an office dashboard — that’s what I did!
If you also use Excel, learn how to import Excel data into Python scripts .