
By going through this article, you should be able to:
- Learn more about what Google Sheets is
- Understand the importance of Python in spreadsheet applications
- How to set up and run Python in Google Sheets
Introduction
Google Sheets is a cloud-based web application that offers spreadsheet functionalities. It was developed by Google and initially released on May 9, 2006. It is part of the Google Docs Editors suite, which combines several similar applications into a single productivity package, including Google Sheets, Google Docs, Google Slides, Google Drawings, Google Forms, Google Sites, and Google Keep.
Aside from its spreadsheet functionality — similar to what Microsoft Excel offers — Google Sheets has gained massive adoption and usage in recent times because it is free for personal use and also has great collaborative features for team members working from across the world, as changes and updates in files can be seen by other collaborators in real-time.
Integrating Python into Google Sheets has brought about tremendous benefits as the capabilities of Python are utilized therein by providing advanced functionalities like machine learning, data analysis and task automation, amongst many other benefits.
This article will teach you how to use Python in Google Sheets.
Before we get started, this article is slightly more technical than beginner-friendly. You should have a good working knowledge of Google Sheets, Python, VSCode, and the Google Cloud online platform for proper utilization.
Running Python in Google Sheets
Integrating Python into Google Sheets requires a series of steps. I’ll guide you through the procedure below:
Step 1: Create a Google Account
A Google account gives you authenticated access to the Google Cloud Platform, and this is where your cloud configurations and setup are done. Create a Google account here if you don’t have one already.
Step 2: Create a New Google Cloud Platform Project
Visit the Google Cloud Platform and begin your initial configuration. Once on the homepage, click on console on the navbar menu at the upper right side of the screen.

Create a new project by clicking on Select a Project at the upper left part of the screen. This displays a window for creating a project. Click on NEW PROJECT at the top right corner of the displayed window.

Ensure that you fill in the name for your project and create it by clicking on the CREATE button, as shown in the picture below.

Step 3: Enable the Necessary APIs
Choose the project you created earlier, as this is what you will be working on. Click on the button with the arrow pointing downward on the navigation bar on the screen, which is where you can select any of your previously created projects to work with. I have selected the Tutorial Project, which is why you can see it in the button on your screen.

Click on your project from the window, as shown below. I’m selecting the Tutorial Project since that’s what I’ve created for this article.

After clicking on your project, you will be redirected to a new page. That is where we will enable the necessary APIs for the project. Type into the search bar visibly positioned at the top of the screen, which has a placeholder text “products and resources,” search for Google Drive API, select it from the search result, and enable it.


Do the same for the Google Sheets API, enabling it just as you did with the Google Drive API.
Step 4: Create a Service Account
After enabling the Google Sheets API, you should be redirected to a page where you must click on credentials on the left side of the screen under library.

After this, click on Manage service accounts on the lower right side of the screen.

Next, click on CREATE SERVICE ACCOUNT with the plus icon on the top of your screen.

Fill out the form and click on CREATE AND CONTINUE. This creates your service account. On the same page, click DONE.

After creating your service account, copy the email for the service, as shown below. You will use it to connect to your Google Sheets account later.

Step 5: Link Your Service Email to Google Sheets
Open the Google Sheets workbook that you intend to integrate with Python and add the copied service email account by clicking the share button at the upper right corner of the screen.

Add your copied service email and then submit.
Step 6: Create and Download JSON Key File
Return to the service account page, click on the three dots on your account, and select manage keys.
Select ADD KEY and click Create New Key. Choose JSON and click CREATE. A JSON file will be automatically downloaded to your computer. Save it in a secure location for future reference.
That’s all for your Google Cloud setup and connection to Google Sheets.
Step 7: Setup Your Virtual Environment on VSCode
Open your VSCode, choose a directory for your project, and set up a Python virtual environment. If you are using a Linux OS, then run these on your command prompt:
python3 -m venv myvenv source myvenv/bin/activate
If you are on Windows:
python -m venv myvenv myenv\Scripts\activate
Step 8: Install the Necessary Gspread Library
For this step, you will install Gspread. This is a Python library that simplifies working with Google Sheets by providing an interface to interact with them with code. Gspread allows developers to access, create, update, and delete data in Google Sheets, making it a great tool for automation, data analysis, and reporting workflows.
pip install gspread
Step 9: Provide Access to Your JSON Key File
Move the private key file you generated in step 6 to a location where Gspread can access it by running the commands below on Linux from your command prompt.
mkdir -p ~/.config/gspread mv path_to_your_key_file.json ~/.config/gspread/service_account.json
Replace path_to_your_key_file.json with the actual path to your JSON key file. You can drag and drop the file in your project directory if you work with Windows.
Step 10: Create a script.py File in Your Project Directory
Inside your project directory, create a file called script.py. This is where all your Python code and configurations will be. Inside your script.py file, import Gspread, perform your configuration, and run your Python code. Replace the quoted strings in the below script with the names of your sheet and worksheet, respectively.
import gspread
service_account = gspread.service_account()
sheet = service_account.open("name of your sheet")
work_sheet = sheet.worksheet("name of your worksheet")
If you are able to run this code without error, that means it is working well.
At this point you can go ahead and write some Python code in the script.py file to perform some tasks specific to your workflow. Here is an example of something you might choose to do:
import gspread
from oauth2client.service_account import ServiceAccountCredentials
# Set up the credentials using OAuth2
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(creds)
# Open a Google Sheet by its name
sheet = client.open('Your Google Sheet Name').sheet1
# Read data from the sheet
data = sheet.get_all_records()
# Write data to the sheet
sheet.update_cell(1, 1, 'Hello, World!')
The code above is explained below:
- Import libraries:
- The gspread library is imported to interact with Google Sheets
- ServiceAccountCredentials from the oauth2client library is imported to handle authentication using a service account
- Define the scope for API access: A scope list specifies the API endpoints the script will access:
- https://spreadsheets.google.com/feeds for accessing Google Sheets data
- https://www.googleapis.com/auth/drive for managing Google Drive files
- Authenticate using a service account:
- Credentials are loaded from a credentials.json file (a key file for the service account)
- The ServiceAccountCredentials.from_json_keyfile_name() method initializes the credentials with the specified scope
- These credentials are then used to authorize the gspread client
- Access a Google Sheet:
- The authorized gspread client opens a Google Sheet by name using client.open(‘Your Google Sheet Name’)
- .sheet1 accesses the first sheet (tab) in the Google Sheet
- Read data from the sheet:
- The sheet.get_all_records() method retrieves all data from the sheet in the form of a list of dictionaries, where each dictionary represents a row, with keys as column headers
- Write Data to the Sheet:
- The sheet.update_cell(1, 1, ‘Hello, World!’) code updates the content of the cell at row 1, column 1 to the string ‘Hello, World!’
And with that, you should be well-equipped to perform your own tasks in Google Sheets using Python.
