How to connect Python to Google Sheets

You might be using Make (formerly Integromat) – as I also do – to manage your sales & marketing automations.

It’s a great no-code solution, for sure.

But if you want to develop more sophisticated automation scenarios, you should explore Python. I started coding in Python a few months ago and I’m now using .py scripts on a daily basis, for all sorts of purposes (incl. generating SEO reports).

When you’re playing with data, you need some sort of database to act as a pivot in your automation workflows.

Google Sheets is definitely the best choice for beginners. It doesn’t offer all the bells and whistles of Airtable or Xano but it’s super easy to connect to Python. Using different worksheets in the same Google Sheet + proprietary Google Sheet formulas, you can create pretty advanced scenarios.

How do you connect Python to Google Sheets?

The first thing you should do in your favourite IDE (Pycharm, Visual Studio), Jupyter Notebook or Repl is install gspread, a Python API for Google Sheets (either via the built-in package manager of via “pip install gspread” command in Terminal (or “pip3 install gspread” for Python3+)) and import the module in your Python script.

import gspread

Before being able to actually use gspread, you need to configure some API parameters via the Google Cloud Console (after opening a free Google Cloud Developer account).

Step 1. create a PROJECT

Step 2. Enable the Google Sheets API in the “API & Services” section of your project

Step 3. Click on MANAGE, then CREDENTIALS to create a “Service Account”

Step 4. Create your Service Account. You just need to pick a name,°2 and °3 are optional.

You’ll get an email address like this one (I’ve obfuscated the section after “@” for security reasons):

[email protected]

You’ll need to share your Google Sheets in EDITOR mode with this email address, more about it later.

Step 5. On the Credentials page, click on the EDIT button on the line of your new Service account.

Step 6. Via the KEYS tab, create a new JSON file containing your Private Key.

Step 7. Download (& rename, optional) the JSON file. I suggest to rename it creds.json

For obvious security reasons, I won’t share the content of the file in this tutorial but it’s simply a JSON where you’ll find the service account email address you created earlier, as well as other encrypted elements.

Step 6. Upload this creds.json file in the folder where you’ll create your Python script.

Note: you can use the same JSON file for multiple scripts located in other folders but you’ll need to refer to the correct file path in your code.

Step 7. Create a Google Sheet and share it with EDITOR rights with the Service Account email address you’ve just created

No need to send a notification, untick the option.

For the purpose of this demo, I’ll create two columns in my brand new Google Sheet titled “Python Connect”, Name and Website and fill out both Row 2 cells.

👉 NOTE: if it’s the first time you’re using Google Cloud Services, you should also enable the Google Drive API. It will use the same credentials as the ones created for the Google Sheets API.

How to code your first Python script connected to Google Sheets

Now, let’s get back to our Python IDE and start coding a simple script.

Our goal will be to print out the values in A2 and B2, then write new values in A3 and B3.

#import library
import gspread

#connect to the service account
gc = gspread.service_account(filename="creds.json")

#connect to your sheet (between "" = the name of your G Sheet, keep it short)
sh = gc.open("Python Connect").sheet1

#get the values from cells a2 and b2
name = sh.acell("a2").value
website = sh.acell("b2").value

print(name)
print(website)

#in our demo, this should print out "Frederick" and "callmefred.com"

#write values in cells a3 and b3
sh.update("a3", "Chat GPT")
sh.update("b3", "openai.com")

Indeed, it worked like a (Py)charm!

As you may have noticed in the script above, I accessed the first sheet of my Google sheet (sheet1). If you want to access another sheet (in the same Google Sheet), simply use the get_worksheet(sheet#) command, like this (“1” is the second worksheet, “2” the third one, etc.).

sh = gc.open("Python Connect").get_worksheet(1)

You can even initialize multiple worksheets, then use them in your script to search and/or move data around.

sh1 = gc.open("Python Connect").sheet1
sh2 = gc.open("Python Connect").get_worksheet(1)

#for instance, take the value from A1 on the second sheet and copy it to A4 on the first sheet

name2 = sh2.acell("A1").value
sh1.update("A4", name2)

How to iterate through the rows of a Google Sheet using Python?

The last thing I wanted to show you is how easy it is to iterate through the rows of your Google Sheet using Python.

Let’s take our example. We have 3 rows of values (Row 1 = the headers). “Patrick”, in A4, is a name I’ve just copied from my second worksheet.

You should use the WHILE LOOP command to iterate through those 3 data rows.

We’re going to print out the value of the A column for each row, generating a dynamic “data” value where {row} will be incremented at each iteration.

#set the first row of your loop
row = 2
#set the limit
limit = 4

#create your while loop, increment by 1 at the end of each loop

while row <= limit:

 data = sh1.acell(f"a{row}").value

 print(data)

 row += 1

#it prints out the value of each cell in the A column until row = 4

Have fun with your Python scripts connected to Google Sheets!

You can leverage this new Automation Superpower in connection with a Python to WordPress script.

It’s the best way to set up an effective Programmatic SEO workflow.

I’m at your disposal if you need to develop advanced automation workflows.

👉 Don’t hesitate to contact me.

FAQ re: Python to Google Sheets