Spreadsheets are everywhere — from financial reports to inventory tracking and data analysis. But manually updating Excel files or Google Sheets can quickly become tedious and error-prone. That’s where Python comes in. With the help of a few powerful libraries, you can automate repetitive tasks, streamline workflows, and integrate spreadsheet operations into larger data pipelines. In this tutorial, you’ll learn how to automate both Excel and Google Sheets using Python through practical, real-world examples — no prior automation experience required.

Prerequisites

Before diving into automation, make sure you have the following set up:

✅ System Requirements

Python 3.7+ installed on your system
Access to a Google Account (for Google Sheets API)

🛠 Required Python Libraries

You’ll be using a combination of well-established Python packages to interact with Excel and Google Sheets:

pandas: For easy data manipulation
openpyxl: To read/write Excel (.xlsx) files
gspread: To work with Google Sheets API
gspread_dataframe: To convert pandas DataFrames to/from Google Sheets
oauth2client: For Google service account authentication

Install all required packages with:

pip install pandas openpyxl gspread gspread_dataframe oauth2client

💡 Tip: It’s recommended to use a virtual environment for this project using venv or virtualenv.

🧰 Tools & Setup

Google Cloud Project with Sheets API enabled
Service Account with credentials.json file
A local Excel (.xlsx) file for testing

Working with Excel Files Using Python

Python makes it simple to automate Excel tasks using pandas and openpyxl. In this section, you’ll learn how to read, write, and format Excel files using just a few lines of code.

1. Installing Required Libraries

If you haven’t already, install the required packages:

pip install pandas openpyxl

2. Reading Excel Files

To read data from an Excel file:

import pandas as pd

# Read Excel file (default: first sheet)
df = pd.read_excel("sample.xlsx")

# Read specific sheet by name
df_sales = pd.read_excel("sample.xlsx", sheet_name="Sales")

# Display the first few rows
print(df.head())

You can also list all sheet names:

excel_file = pd.ExcelFile("sample.xlsx")
print(excel_file.sheet_names)

3. Writing Data to Excel

You can write a DataFrame to a new or existing Excel file:

data = {
    "Product": ["Keyboard", "Mouse", "Monitor"],
    "Price": [25, 15, 120]
}
df = pd.DataFrame(data)

# Save to Excel file
df.to_excel("products.xlsx", index=False)

To write to a specific sheet or append multiple sheets:

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Inventory", index=False)

4. Updating Existing Excel Files

To update an existing Excel file (add new sheet, for example):

from openpyxl import load_workbook

# Load the workbook
book = load_workbook("report.xlsx")

# Create a Pandas Excel writer using openpyxl engine
with pd.ExcelWriter("report.xlsx", engine="openpyxl", mode="a") as writer:
    writer.book = book
    df.to_excel(writer, sheet_name="NewData", index=False)

5. Formatting and Styling (Optional)

You can also style your Excel sheets using openpyxl:

from openpyxl.styles import Font
from openpyxl import load_workbook

wb = load_workbook("products.xlsx")
ws = wb.active

# Set bold font for header
for cell in ws[1]:
    cell.font = Font(bold=True)

wb.save("products_styled.xlsx")

Working with Google Sheets Using Python

To access and automate Google Sheets with Python, you’ll use the gspread library and authenticate via a Google Cloud service account.

1. Setting Up Google Sheets API

Follow these steps to enable and configure the Google Sheets API:

✅ Step 1: Create a Google Cloud Project

Go to Google Cloud Console
Click "New Project", give it a name (e.g., Sheets Automation)
Click "Create"

✅ Step 2: Enable Google Sheets API

From your project dashboard, click “APIs & Services > Library”
Search for “Google Sheets API” and click “Enable”

✅ Step 3: Create a Service Account

Go to “APIs & Services > Credentials”
Click “+ Create Credentials” > Service Account
Enter a name, then click Create and Continue until done
On the service account page, go to "Keys" > "Add Key" > "Create new key", select JSON
Download and save the credentials.json file in your project folder

✅ Step 4: Share Your Sheet with the Service Account

Create or open a Google Sheet in your Google Drive
Share it with the service account email (e.g., [email protected])
- Use Viewer/Editor permission as needed

2. Installing Required Libraries

Run the following in your terminal:

pip install gspread gspread_dataframe oauth2client

3. Authenticating and Connecting to Google Sheets

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Define the scopes
scope = [
    "https://spreadsheets.google.com/feeds",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive.file",
    "https://www.googleapis.com/auth/drive"
]

# Load credentials and authorize
creds = ServiceAccountCredentials.from_json_keyfile_name("credentials.json", scope)
client = gspread.authorize(creds)

# Open a Google Sheet by title
sheet = client.open("My Sheet").sheet1

# Print all data
data = sheet.get_all_records()
print(data)

📝 Make sure the spreadsheet name matches exactly (case-sensitive), and the file is shared with your service account.

Reading and Writing Data with Google Sheets

Once you're authenticated, interacting with Sheets is straightforward. Let’s go through some practical operations.

📖 Reading Data from Google Sheets

1. Read All Records into a List of Dicts

# Get all records (as list of dictionaries)
records = sheet.get_all_records()
for row in records:
    print(row)

2. Read All Values as a 2D List

# Get all values (as a list of lists)
values = sheet.get_all_values()
for row in values:
    print(row)

3. Get a Specific Cell Value

value = sheet.cell(2, 1).value  # Row 2, Column 1
print("Cell (2,1):", value)

4. Read Data into a pandas DataFrame

import pandas as pd
from gspread_dataframe import get_as_dataframe

# Convert to DataFrame
df = get_as_dataframe(sheet)
print(df.head())

✅ Pro Tip: Make sure your Google Sheet has headers in the first row when importing to a DataFrame.

✍️ Writing Data to Google Sheets

1. Update a Specific Cell

sheet.update_cell(2, 2, "Updated Value")  # Row 2, Column 2

2. Append a New Row

row = ["Product A", 30, "In Stock"]
sheet.append_row(row)

3. Write an Entire DataFrame to Google Sheets

from gspread_dataframe import set_with_dataframe

data = {
    "Name": ["Alice", "Bob", "Charlie"],
    "Score": [85, 92, 78]
}
df = pd.DataFrame(data)

# Write DataFrame to sheet
set_with_dataframe(sheet, df)

⚠️ Note: This will overwrite the sheet’s content starting from the top-left cell (A1).

🧹 Clear Sheet Content

sheet.clear()

You now know how to interact with Google Sheets using Python fully.

Real-World Automation Examples

These examples demonstrate how to utilize Python scripts to streamline everyday spreadsheet tasks across Excel and Google Sheets.

✅ Example 1: Convert a CSV File to Excel and Upload to Google Sheets

Step 1: Convert CSV to Excel

import pandas as pd

# Load CSV
df = pd.read_csv("sales.csv")

# Save as Excel
df.to_excel("sales.xlsx", index=False)

Step 2: Upload to Google Sheets

from gspread_dataframe import set_with_dataframe
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Auth (reuse previous auth code)
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/spreadsheets", 
         "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("credentials.json", scope)
client = gspread.authorize(creds)

# Open Google Sheet
sheet = client.open("Sales Report").sheet1

# Upload DataFrame
set_with_dataframe(sheet, df)

✅ Example 2: Daily Report Generator and Email Ready Sheet

You can schedule a script that:

Fetches data from a database or API
Generates a report in Excel
Pushes the report to Google Sheets

# Simulate generated data
df = pd.DataFrame({
    "Date": pd.date_range("2025-08-01", periods=5),
    "Visitors": [120, 135, 150, 160, 180],
    "Signups": [10, 15, 18, 20, 25]
})

# Save to Excel
df.to_excel("daily_report.xlsx", index=False)

# Push to Google Sheet
sheet = client.open("Daily Metrics").sheet1
sheet.clear()
set_with_dataframe(sheet, df)

🔁 Schedule this script with cron or Task Scheduler (covered below) to run daily.

✅ Example 3: Sync Local Excel File to Google Sheets (One-Way)

# Load Excel
df = pd.read_excel("inventory.xlsx")

# Push to Google Sheet
sheet = client.open("Inventory Tracker").sheet1
sheet.clear()
set_with_dataframe(sheet, df)

You can add logic to check for updates, compare timestamps, or sync specific sheets only.

✅ Example 4: Auto-Format and Upload Grade Sheet

# Apply calculations
df["Final Grade"] = (df["Assignment"] * 0.4 + df["Exam"] * 0.6).round(1)

# Push to Sheet
sheet = client.open("Grades 2025").sheet1
sheet.clear()
set_with_dataframe(sheet, df)

Error Handling and Logging

When working with APIs, files, or user input, things can go wrong. Adding error handling and logs helps your automation scripts run smoothly and recover from common issues.

✅ Basic Try-Except Error Handling

Use Python’s try-except blocks to catch and log potential issues:

try:
    df = pd.read_excel("data.xlsx")
    print("Excel file loaded successfully.")
except FileNotFoundError:
    print("❌ Error: Excel file not found.")
except Exception as e:
    print(f"❌ Unexpected error: {e}")

✅ Handling Google Sheets Errors

import gspread
from oauth2client.service_account import ServiceAccountCredentials

try:
    creds = ServiceAccountCredentials.from_json_keyfile_name("credentials.json", scope)
    client = gspread.authorize(creds)
    sheet = client.open("My Sheet").sheet1
    print("✅ Connected to Google Sheet.")
except gspread.exceptions.SpreadsheetNotFound:
    print("❌ Google Sheet not found or not shared with service account.")
except Exception as e:
    print(f"❌ Google Sheets error: {e}")

✅ Adding Logging to File

For production-grade scripts, use Python’s logging module:

import logging

# Configure logging
logging.basicConfig(
    filename="automation.log",
    level=logging.INFO,
    format="%(asctime)s - %(levelname)s - %(message)s"
)

logging.info("Script started.")

try:
    df = pd.read_excel("report.xlsx")
    logging.info("Loaded Excel file.")
except Exception as e:
    logging.error(f"Error loading Excel: {e}")

🛡️ Log errors instead of printing them, especially for automated or scheduled jobs.

✅ Validate Before Uploading to Google Sheets

if not df.empty and "Date" in df.columns:
    set_with_dataframe(sheet, df)
    logging.info("Uploaded data to Google Sheet.")
else:
    logging.warning("DataFrame is empty or missing required columns.")

This improves the visibility and resilience of your automation workflow.

Bonus: Schedule Your Scripts

Once your Python script is ready and tested, you can schedule it to run automatically — daily, hourly, or on any custom interval.

⏰ Option 1: Schedule with `cron` (Linux/macOS)

Step 1: Open the crontab editor

crontab -e

Step 2: Add a job to run your script

0 8 * * * /usr/bin/python3 /path/to/your/script.py >> /path/to/log.txt 2>&1

This runs your script every day at 8:00 AM.

🔹 Make sure the full path to python3 and your script is correct.

Example:

0 7 * * 1-5 /usr/bin/python3 /home/user/reports/daily_report.py

This runs Monday to Friday at 7:00 AM.

🪟 Option 2: Schedule with Task Scheduler (Windows)

Step 1: Open Task Scheduler

Press Win + R, type taskschd.msc, and press Enter

Step 2: Create a New Task

Go to Action > Create Basic Task
Name your task (e.g., “Daily Report Automation”)
Choose Trigger (e.g., Daily at 8:00 AM)
Choose Action > Start a Program
- Program/script: python
- Add arguments: C:\path\to\your\script.py

Step 3: Finish and Run

Click Finish, and test it manually from the Task Scheduler.

⚠️ Use absolute paths in your script for files, logs, and credentials.

✅ Optional: Make Your Script Executable (Linux/macOS)

Add a shebang and make the script executable:

#!/usr/bin/env python3
# your_script.py

Then:

chmod +x your_script.py

Now you can run it directly from cron like:

/path/to/your_script.py

Conclusion

Automating Excel and Google Sheets tasks with Python can save hours of manual work, eliminate errors, and streamline your data workflows. In this tutorial, you’ve learned how to:

Read, write, and update Excel files using pandas and openpyxl
Authenticate with and interact with Google Sheets via gspread
Upload and sync data between Excel and Google Sheets
Handle errors gracefully and log activity for easier debugging
Schedule your scripts to run automatically using cron or Task Scheduler

Whether you're generating reports, syncing data across formats, or building custom automation workflows, these tools give you a powerful foundation for managing spreadsheet data programmatically.

🔗 Next Steps: Expand your automation by integrating Gmail (for email reports), Google Drive (for file uploads), or scheduling pipelines with tools like Airflow or Prefect.

You can find the full source code on our GitHub.

=====

That's just the basics. If you need more deep learning about Python and the frameworks, you can take the following cheap course:

Thanks!

Automate Excel and Google Sheets Tasks with Python: Practical Examples

Learn how to automate Excel and Google Sheets tasks using Python with practical examples. Read, write, sync, and schedule spreadsheets effortlessly.

Table of Contents: