Integrate Google Sheets with Python: A Step-by-Step Guide

Want to learn how to connect your Google Sheets with Python to fetch and manipulate data? By using the gspread library alongside pandas, you can easily automate tasks, analyze data, and even save it in formats like JSON. Let’s walk through the process step-by-step—perfect for beginners and experienced users alike! Step 1: Enable Google Sheets API & Get Credentials To integrate Google Sheets with Python, you first need to set up access via the Google Cloud Console: Visit the Google Cloud Console. Create a new project (e.g., "Python-Google-Sheets"). Go to "APIs & Services" → "Library". Search for Google Sheets API and click "Enable". Search for Google Drive API and enable it too (required for file access). Navigate to "APIs & Services" → "Credentials". Click "Create Credentials" and choose "Service Account". After creating the service account: Go to the "Keys" tab. Click "Add Key" → "JSON". A JSON key file (e.g., your-credentials.json) will download to your computer. Note: This JSON file contains your service account credentials. Keep it secure and don’t share it publicly! Step 2: Install Required Libraries You’ll need two Python libraries: gspreadfor interacting with Google Sheets and pandasfor handling data. Install them using pip: pip install gspread pandas Make sure you run this command in your terminal or command prompt. Step 3: Load Google Sheets Data in Python Now, let’s write some Python code to fetch data from your Google Sheet: import gspread import pandas as pd # Load credentials from the JSON file gc = gspread.service_account(filename="your-credentials.json") # Replace with your JSON file path# Open the Google Sheet by its name sh = gc.open("Your Google Sheet Name") # Replace with your sheet’s exact name# Select a worksheet (e.g., "Sheet1") worksheet = sh.worksheet("Sheet1") # Replace with your worksheet name# Fetch all data from the worksheet data = worksheet.get_all_records() # Convert the data into a pandas DataFrame df = pd.DataFrame(data) # Display the DataFrame print(df) What’s Happening Here? gspread.service_account() authenticates using your JSON credentials. gc.open() accesses your Google Sheet by its title (found in the browser tab). worksheet.get_all_records() retrieves all rows as a list of dictionaries, assuming the first row is headers. pd.DataFrame() turns the data into a table-like structure for easy manipulation. Step 4: Share Google Sheet with Service Account Your Python script won’t work yet—your service account needs permission to access the sheet: Open your Google Sheet in a browser. Click the "Share" button (top-right corner). Find the service account email in your JSON file (it looks like your-service-account@project-id.iam.gserviceaccount.com). Add this email to the share list and grant Editor access. Why? The service account acts like a user and needs explicit permission to read or edit the sheet. Other Useful Methods Once connected, gspreadoffers handy methods to interact with your sheet. Here are a few examples: Get Data from a Specific Cell value = worksheet.acell("B2").value # Fetch the value in cell B2 print(value) Get Data from a Specific Row or Column row_values = worksheet.row_values(2) # Get all values in row 2 col_values = worksheet.col_values(1) # Get all values in column 1 print(row_values, col_values) Append a New Row worksheet.append_row(["John Doe", 25, "Engineer"]) # Add a new row to the sheet These methods are great for tasks like updating records, extracting specific data, or automating reports. Save Data to a JSON File Want to save your Google Sheets data as a JSON file? Add this code after fetching the data: import json import os # Define the file path file_name = "./data.json" dir_name = os.path.dirname(file_name) # Create the directory if it doesn’t exist if not os.path.exists(dir_name): print(f"\nDirectory {dir_name} does not exist.") print(f"\nCreating the directory: {dir_name}") os.makedirs(dir_name) else: print(f"\nDirectory {dir_name} already exists.") # Check if the file exists and create or modify it if not os.path.exists(file_name): print(f"\nFile {file_name} does not exist.") print(f"\nCreating a new file: {file_name}") else: print(f"\nFile already exists, modifying the file.") # Save the data to JSON with open(file_name, "w") as json_file: json.dump(data, json_file, indent=4) What’s Happening? The script checks if the directory and file exist, creating them if needed. json.dump() writes the data to data.json with nice formatting (indent=4). The result is a JSON file you can use elsewhere, like in a web app or for backups. Conclusion You’ve now learned how to integrate Google Sheets with Python! From setting up the API and fetching data to saving it as JSON, this

Apr 3, 2025 - 11:43

Integrate Google Sheets with Python: A Step-by-Step Guide

Want to learn how to connect your Google Sheets with Python to fetch and manipulate data? By using the gspread library alongside pandas, you can easily automate tasks, analyze data, and even save it in formats like JSON. Let’s walk through the process step-by-step—perfect for beginners and experienced users alike!

Step 1: Enable Google Sheets API & Get Credentials

To integrate Google Sheets with Python, you first need to set up access via the Google Cloud Console:

Visit the Google Cloud Console.
Create a new project (e.g., "Python-Google-Sheets").
Go to "APIs & Services" → "Library".
Search for Google Sheets API and click "Enable".
Search for Google Drive API and enable it too (required for file access).
Navigate to "APIs & Services" → "Credentials".
Click "Create Credentials" and choose "Service Account".
After creating the service account:
- Go to the "Keys" tab.
- Click "Add Key" → "JSON".
- A JSON key file (e.g., your-credentials.json) will download to your computer. Note: This JSON file contains your service account credentials. Keep it secure and don’t share it publicly!

Step 2: Install Required Libraries

You’ll need two Python libraries: gspreadfor interacting with Google Sheets and pandasfor handling data. Install them using pip:

pip install gspread pandas

Make sure you run this command in your terminal or command prompt.

Step 3: Load Google Sheets Data in Python

Now, let’s write some Python code to fetch data from your Google Sheet:

import gspread
import pandas as pd

*# Load credentials from the JSON file*
gc = gspread.service_account(filename="your-credentials.json")  *# Replace with your JSON file path# Open the Google Sheet by its name*
sh = gc.open("Your Google Sheet Name")  *# Replace with your sheet’s exact name# Select a worksheet (e.g., "Sheet1")*
worksheet = sh.worksheet("Sheet1")  *# Replace with your worksheet name# Fetch all data from the worksheet*
data = worksheet.get_all_records()

*# Convert the data into a pandas DataFrame*
df = pd.DataFrame(data)

*# Display the DataFrame*
print(df)

What’s Happening Here?

gspread.service_account() authenticates using your JSON credentials.
gc.open() accesses your Google Sheet by its title (found in the browser tab).
worksheet.get_all_records() retrieves all rows as a list of dictionaries, assuming the first row is headers.
pd.DataFrame() turns the data into a table-like structure for easy manipulation.

Step 4: Share Google Sheet with Service Account

Your Python script won’t work yet—your service account needs permission to access the sheet:

Open your Google Sheet in a browser.
Click the "Share" button (top-right corner).
Find the service account email in your JSON file (it looks like your-service-account@project-id.iam.gserviceaccount.com).
Add this email to the share list and grant Editor access.

Why? The service account acts like a user and needs explicit permission to read or edit the sheet.

Other Useful Methods

Once connected, gspreadoffers handy methods to interact with your sheet. Here are a few examples:

Get Data from a Specific Cell

value = worksheet.acell("B2").value  *# Fetch the value in cell B2*
print(value)

Get Data from a Specific Row or Column

row_values = worksheet.row_values(2)  *# Get all values in row 2*
col_values = worksheet.col_values(1)  *# Get all values in column 1*
print(row_values, col_values)

Append a New Row

worksheet.append_row(["John Doe", 25, "Engineer"])  *# Add a new row to the sheet*

These methods are great for tasks like updating records, extracting specific data, or automating reports.

Save Data to a JSON File

Want to save your Google Sheets data as a JSON file? Add this code after fetching the data:

import json
import os

*# Define the file path*
file_name = "./data.json"
dir_name = os.path.dirname(file_name)

*# Create the directory if it doesn’t exist*
if not os.path.exists(dir_name):
    print(f"\nDirectory {dir_name} does not exist.")
    print(f"\nCreating the directory: {dir_name}")
    os.makedirs(dir_name)
else:
    print(f"\nDirectory {dir_name} already exists.")

*# Check if the file exists and create or modify it*
if not os.path.exists(file_name):
    print(f"\nFile {file_name} does not exist.")
    print(f"\nCreating a new file: {file_name}")
else:
    print(f"\nFile already exists, modifying the file.")

*# Save the data to JSON*
with open(file_name, "w") as json_file:
    json.dump(data, json_file, indent=4)

What’s Happening?

The script checks if the directory and file exist, creating them if needed.
json.dump() writes the data to data.json with nice formatting (indent=4).
The result is a JSON file you can use elsewhere, like in a web app or for backups.

Conclusion

You’ve now learned how to integrate Google Sheets with Python! From setting up the API and fetching data to saving it as JSON, this setup opens up endless possibilities for automation and analysis. Try it out with your own Google Sheet—what will you build with this power?

Feel free to experiment with the code and share your ideas!