Programming

How to Read and Write to Google Sheets With Python

Joe Coburn 14-12-2016

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.

Advertisement

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.

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

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 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 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 How to Import Excel Data Into Python Scripts Using Pandas For advanced data analysis, Python is better than Excel. Here's how to import your Excel data into a Python script using Pandas! Read More .

Explore more about: Google Drive, Google Sheets, Python.

Whatsapp Pinterest

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

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

  1. JimboJones
    February 3, 2019 at 3:57 pm

    Hi, this tutorial is a bit outdated but I did some research and this is the code I finally got to work:

    import gspread
    from oauth2client.service_account import ServiceAccountCredentials
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']credentials = ServiceAccountCredentials.from_json_keyfile_name('creds.json', scope)
    gss_client = gspread.authorize(credentials)
    gss = gss_client.open('MUO_Python_Sheet')
    sheet = gss.sheet1
    all_cells = sheet.range('A1:C6')
    print (all_cells)
    for cell in all_cells:
    print (cell.value)

    • Jayson
      July 3, 2019 at 9:55 am

      Thank you!

  2. will teach
    August 18, 2018 at 4:35 pm

    SignedJwtAssertionCredentials has been removed and replaced. I found some info here: https://github.com/google/oauth2client/issues/401

    but as a beginner it's way over my head and I'm breaking out in a sweat just reading what they're saying..

  3. asdf
    June 14, 2018 at 7:02 am

    this article is incomplete and now also irrelevant, do not waist your time

  4. J Tolkien
    March 30, 2017 at 6:43 am

    Hi

    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 ?

    Many thanks

    • Litmon
      August 27, 2018 at 5:39 pm

      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.

      It keeps insisting that the spreadsheet has no attribute "sheet1".