How to connect Python to Google Sheets

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.

Screenshot 2023 01 10 at 10.49.21
pip install gspread
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

Create a new project in google developer console
Create a project

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

enable the google sheets api
Enable the Google Sheets API

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

Create a Service Account email address.
Create Credentials
Create Service Account
Create a Service Account

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

service account details
Give a name to your service account, skip 2° and 3°. Click DONE.

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.

edit service account
Edit Service Account

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

Keys Tab of Service Account
Keys Tab of Service Account
Create JSON containing Private Key
Create a 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.

Share Google Sheets with Service Account
Share your Google Sheet with the Service Account email address

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.

Python Google Sheets Demo
Google Sheet

👉 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.

do this
#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!

New values have been added to the sheet
New values have been added to the sheet

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.

Iterate through rows
Iterate through rows

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
Printing out the values
Printing out the values

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

What should I do if I get the error “gspread.exceptions.SpreadsheetNotFound”

As the name implies, it means that your Python IDE hasn’t found the spreadsheet you want to connect to.

If you have followed all the instructions above (connect to your creds.json credentials + share the spreadsheet with your Google Developer Service email (not your personal email), it could be that there’s some form of lag / caching which prevents the API to find the spreadsheet by name.

In that case, a quick fix is to connect using the ID of the spreadsheet instead of its name (which also comes with a bonus = you can rename your spreadsheet at a later stage without breaking the connection).

In order to connect via the spreadsheet ID (which you’ll find in the URL of the spreadsheet after /d/), use sh = gc.open_by_key(“your spreadsheet ID”).sheet 1 instead of gc.open(“your spreadsheet name”).sheet 1. It tested this fix and it worked like a (py)charm!

What should I do if I get “gspread.exceptions.APIError: {‘code’: 503, ‘message’: ‘The service is currently unavailable.’, ‘status’: ‘UNAVAILABLE’}”?

The 503 error simply means that the Google Sheets API server isn’t available to process your request. Be patient, grab a coffee and try again later. If the error persists, contact Google Cloud support for seek help.

Is there some rate limiting in the Google Sheets API?

Yes there is, you shouldn’t go too crazy in terms of request frequency when using the Google Sheets API. Calls are limited to 300 read requests per minute per project, even further limited to 60 read requests per user per minute per project. The same limit applies to write requests (300 per minute per project, 60 per user per minute per project). So if you’re mixing read & write requests, you’ll have basically a maximum of 1 request per second. Hence the need to use time.sleep( ) commands in your Python code.

☕️ If you found this piece helpful, you can buy me coffee

AI, SEO, Marketing & Coding Tips

Sign up to my FREE newsletter and you’ll receive my latest articles every Friday. I mainly blog about generative AI, programmatic SEO and smart automations. I only share actionable tips.