Python’s syntax may seem strange and unusual at first. But it’s easy to learn and use once you get the hang of it. Python powers coding games like Minecraft Pi Edition, many machine learning algorithms, and a slew of websites.
But in this article, you’ll learn how to read and write to Google Sheets using Python.
Google Setup
Before you jump into the code, there’s some initial setup to get out of the way on Google Sheets.
First, create yourself a new sheet. You can skip this step if you have one already set up. We’re using a list of rally cars for this example. But you can follow this tutorial with your own data:
Now you need to set up your sharing options. But first, you need to generate Signed Credentials from Google Developers Console. It’s easy; navigate to the Google Developers Console and follow these steps:
- Click CREATE PROJECT to create a new project (or use an existing one):
- Give your project a suitable name and then click CREATE:
- From the notification that pops up, click SELECT PROJECT below the project you just created to hop into it.
- Slide out the side menu and hover your cursor over APIs and services, then select Dashboard.
- Click ENABLE APIS AND SERVICES at the top of the page. From the options, select Google Sheets API (use the search bar if you can’t find it):
- Choose ENABLE:
- Click CREATE CREDENTIALS and select Credentials from the left menu:
- Click the CREATE CREDENTIALS button at the top of the page:
- Then select Service account:
- Fill in the service account name field and click CREATE, followed by DONE:
- You’ll see the service account now listed in the Service Accounts table at the bottom of the next page. Click the edit icon next to it:
- Choose KEYS. Then click the ADD KEY button and select Create new key:
- Choose JSON as the format:
- Click CREATE, and a JSON file should download to your PC. Move this into your project directory and give it your preferred name with an appended .json file format.
- Finally, open the file and look for client_email. This should be something like: id.gserviceaccount.com. Copy this address.
- Open Google Sheets and share it with this email address (Top Right > Share > Enter Email). Click the email address once it appears, then hit Send to grant access.
That’s it for the Google Sheets side.
Python Setup
If you’re running Windows OS, you may need to download and install Python. You don’t need to bother with that if you’re on macOS as it comes with Python already installed.
First, open a new terminal and create a Python virtual environment.
You’ll need to install a web authorization framework called oauth2client. 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 also need to install a Google Sheets communication package called gspread. Again, this is easy to install using pip:
pip install gspread
Now open your favorite text editor. Then create a new Python file with an appended .py file extension, and save it in your project directory.
Back in your terminal, cd into your project directory. You can use dir to list the files in that directory. You can show your working directory as well if you like.
Once in your project directory, you can always execute your Python script by calling it via the command line like this:
python [file_name].py
The output of your code then appears in your command line.
Now that Python is working, let’s go ahead and set up the libraries. Open the Python file you created earlier and import the following libraries:
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import json
Then run the code.
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 any of the imported modules, first ensure that you’ve activated your virtual environment.
You can also display the list of all modules you’ve installed in that environment by running pip freeze via the command line. If the missing module isn’t there, then run pip install [module] again. Ensure that you avoid typos.
Here’s the code to get you started with reading and writing your Google Sheets:
from oauth2client.service_account import ServiceAccountCredentials
import gspread
import json
scopes = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name("[JSON_key_name].json", scopes) #access the json key you downloaded earlier
file = gspread.authorize(credentials) # authenticate the JSON key with gspread
sheet = file.open("Python_MUO_Google_Sheet") #open sheet
sheet = sheet.sheet_name #replace sheet_name with the name that corresponds to yours, e.g, it can be sheet1
That block of code retrieves your detail from the .json file containing your auth key. Then it uses it to authenticate with Google using the gspread module. It then opens a sheet called Python_MUO_Google_Sheet. You may need to change this to the name of your sheet (provided you’ve shared it correctly). Python is case sensitive, so make sure you enter this code correctly.
Reading Your Google Sheets With Python
Now that everything is set up, it’s a breeze to read or write data into Google Sheets with Python. 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:
The output above doesn’t look nice because Python has dumped the content with no regard for formatting.
So here’s how you print all the cell values in a nicer format using Python’s for loop and the built-in value function:
for cell in all_cells:
print(cell.value)
And that looks like this:
It’s possible to access cells individually:
A1 = sheet.acell('A2').value
print(A1)
Output: Ford
Or you can use the cell coordinates. This happens in a row-by-column fashion. For instance, the code below gets the data on the fifth row and third column:
coord = sheet.cell(5, 3).value
It’s easy to get all the values for a row as well:
row = sheet.row_values(1) #first row
print(row)
Or you can get a whole column. The example below gets the second column:
col = sheet.col_values(2)
print(col)
Writing to Your Google Sheets
It’s just as easy to write back into the sheet, and you can use cell names or coordinates just like while reading:
sheet.update_acell('C2', 'Blue')
sheet.update_cell(2, 3, 'Blue') #updates row 2 on column 3
Updating a range of cells is easy as well:
sheet.update('A2:B3', [["Not Ford", "Not Lancia"], ["Nothing", "Not"]])
Note: You can append your sheet by updating the empty cells you want to add data to using the update() method as well.
Format the headers of your Google Sheets into bold text if you want:
sheet.format('A1:C1', {'textFormat': {'bold': True}})
You can also use gspread along with pandas and numpy. Have a look at the gspread docs if you wish to know more about the various twists and tweaks around this.
If you’re writing to an important sheet you manage with other people, you may wish to consider a safety cell. Store a value in a certain cell and then read that cell first. If the contents have changed, then others have changed or added columns to the sheet, so you may not proceed with writing. Here’s how you can achieve that:
if sheet.acell('B3') != 'SAFETY':
# something has changed in the sheet, DO NOT PROCEED
print("Sheet already updated.")
else:
# continue with your writing
sheet.update_acell('C2','Blue')
That’s good practice. It ensures that your script cannot accidentally write into an already updated column. It’s not a substitute for proper backups, though.
Automate Your Spreadsheet Tasks With Python
Now that you know the basics, go ahead and make something cool! Instead of reinventing the wheel, you can automate tasks by creating dedicated and callable functions that read and write to your Google Sheets.
Additionally, if you also use Microsoft Excel, you can import Excel data into Python scripts and manipulate your Excel spreadsheet as you like.