Python Automation for Office Work: Save 3 Hours Every Day (With Code Examples)

Q: Can Python automate Google Sheets too?

Yes. Use the `gspread` library. Create a Google Service Account, share your spreadsheet with it, and use the same pandas logic. The API is free for reasonable usage.

If you still manually copy data between Excel files, build monthly reports by hand, or send the same emails every week, Python can eliminate all of that and give you 2–3 hours of your day back.

Python is the world's most popular language for automation, and you do not need a computer science degree to use it. If you can write an Excel VLOOKUP, you already have the logical thinking to write Python automation scripts.

This guide includes ready-to-run code examples for the most common office automation tasks.

What Can Python Automate in an Office?

Here are the tasks most commonly automated by office workers using Python:

Merge multiple Excel files into one master sheet in seconds
Generate formatted reports automatically from raw data
Send bulk personalised emails with attachments on a schedule
Organise folders — automatically sort files by type, date, or project
Scrape data from websites into a spreadsheet without manual copy-paste
Clean data — fix inconsistent formats, remove duplicates, standardise values

Even automating one task saves you measurable time every week.

Getting Started: Installation

Install Python 3.11+ from python.org. During installation on Windows, check "Add Python to PATH".

Open Command Prompt (Windows) or Terminal (Mac) and install the required libraries:

pip install pandas openpyxl xlwings schedule

What each does:

pandas — The most powerful Python library for spreadsheet data manipulation
openpyxl — Read and write Excel (.xlsx) files directly
xlwings — Interact with Excel while it is open
schedule — Run Python scripts automatically at set times

Example 1: Merge All Excel Files in a Folder

You receive 15 regional sales reports every month and manually paste them into one master file. This script does it in under 5 seconds:

import pandas as pd
import glob
import os

# Target folder containing all Excel files
folder = r'C:\Reports\Monthly'  # Change this to your folder path

# Find all Excel files
files = glob.glob(os.path.join(folder, '*.xlsx'))

if not files:
    print("No Excel files found in the folder.")
else:
    all_data = []
    for file in files:
        df = pd.read_excel(file)
        df['Source'] = os.path.basename(file)  # Track file origin
        all_data.append(df)

    combined = pd.concat(all_data, ignore_index=True)
    output_path = os.path.join(folder, 'MASTER_REPORT.xlsx')
    combined.to_excel(output_path, index=False)

    print(f"Done. Merged {len(files)} files → {output_path}")
    print(f"Total rows: {len(combined):,}")

Save as merge_reports.py and run it with python merge_reports.py. Done.

Example 2: Auto-Generate a Monthly Summary Report

This script reads raw sales data and creates a formatted summary grouped by product:

import pandas as pd
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment

# Load raw data
df = pd.read_excel('sales_raw.xlsx')
df['Date'] = pd.to_datetime(df['Date'])

# Filter current month
now = datetime.now()
monthly = df[(df['Date'].dt.month == now.month) & (df['Date'].dt.year == now.year)]

# Summarise
summary = monthly.groupby('Product').agg(
    Quantity=('Quantity', 'sum'),
    Revenue=('Revenue', 'sum')
).reset_index().sort_values('Revenue', ascending=False)

summary['Revenue'] = summary['Revenue'].apply(lambda x: f'PKR {x:,.0f}')

# Save
output = f'report_{now.strftime("%Y_%m")}.xlsx'
summary.to_excel(output, index=False)
print(f'Report saved: {output}')

Example 3: Send Automated Email Reports

Send a weekly email with an Excel attachment every Monday at 8 AM:

import smtplib
import schedule
import time
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email import encoders
from datetime import datetime

SENDER_EMAIL    = 'your.email@gmail.com'
SENDER_PASSWORD = 'your_app_password'  # Gmail App Password (not your main password)
RECIPIENT_EMAIL = 'manager@company.com'

def send_weekly_report():
    msg = MIMEMultipart()
    msg['From']    = SENDER_EMAIL
    msg['To']      = RECIPIENT_EMAIL
    msg['Subject'] = f'Weekly Report — {datetime.now().strftime("%d %B %Y")}'

    body = (
        f"Hi,\n\n"
        f"Please find the weekly report attached.\n\n"
        f"This email was generated automatically.\n\n"
        f"Regards"
    )
    msg.attach(MIMEText(body, 'plain'))

    with open('weekly_report.xlsx', 'rb') as f:
        part = MIMEBase('application', 'octet-stream')
        part.set_payload(f.read())
        encoders.encode_base64(part)
        part.add_header('Content-Disposition', 'attachment; filename=weekly_report.xlsx')
        msg.attach(part)

    with smtplib.SMTP_SSL('smtp.gmail.com', 465) as server:
        server.login(SENDER_EMAIL, SENDER_PASSWORD)
        server.send_message(msg)

    print(f'Report sent at {datetime.now().strftime("%H:%M")}')

# Schedule for every Monday at 08:00
schedule.every().monday.at("08:00").do(send_weekly_report)

print("Scheduler running. Press Ctrl+C to stop.")
while True:
    schedule.run_pending()
    time.sleep(60)

Important: For Gmail, create an "App Password" in your Google Account security settings. Never use your main Gmail password in scripts.

Example 4: Auto-Organise Your Downloads Folder

This script sorts every file in your Downloads folder into categorised subfolders:

import shutil
from pathlib import Path

downloads = Path.home() / 'Downloads'

FOLDERS = {
    'Images':     ['.jpg', '.jpeg', '.png', '.gif', '.webp', '.svg', '.heic'],
    'Documents':  ['.pdf', '.docx', '.doc', '.txt', '.xlsx', '.pptx', '.csv'],
    'Videos':     ['.mp4', '.avi', '.mov', '.mkv', '.wmv'],
    'Archives':   ['.zip', '.rar', '.7z', '.tar', '.gz'],
    'Code':       ['.py', '.js', '.ts', '.html', '.css', '.json', '.sql'],
    'Audio':      ['.mp3', '.wav', '.flac', '.aac'],
}

organised = 0
for file in downloads.iterdir():
    if not file.is_file():
        continue
    ext = file.suffix.lower()
    dest_folder = None
    for folder_name, extensions in FOLDERS.items():
        if ext in extensions:
            dest_folder = downloads / folder_name
            break
    if not dest_folder:
        dest_folder = downloads / 'Other'
    dest_folder.mkdir(exist_ok=True)
    shutil.move(str(file), str(dest_folder / file.name))
    organised += 1

print(f'Done. Organised {organised} files.')

Run this once, or schedule it to run every day at 6 PM.

Your 4-Week Learning Path

Week 1 — Python Basics

Variables, strings, lists, dictionaries
If/else conditions and for loops
Functions and modules
Resource: Official Python Tutorial at docs.python.org

Week 2 — File Handling and Excel

Read and write text files
Install and use openpyxl
Practice: Read an Excel file, filter rows, save the result

Week 3 — pandas for Data Work

DataFrames (like Excel sheets in Python)
Filtering, grouping, merging datasets
Practice: Build the monthly report script above

Week 4 — Scheduling and Automation

Run scripts automatically with schedule
Handle errors gracefully with try/except
Goal: Build one automation that saves you real time at work

FAQ

How long does it take to learn Python for office automation? With 1–2 hours of daily practice, you can build practical automation scripts within 4–6 weeks. You do not need to master all of Python — just the parts relevant to files, Excel, and emails.

Does Python work on both Windows and Mac for Excel automation? Yes. All the examples in this article work on both operating systems. The only difference is file paths: use Path.home() (as shown above) instead of hardcoded C:\Users\... paths for cross-platform compatibility.

Is it safe to run Python scripts on my work computer? Yes, but always test on sample data first. Keep backups of important files before running automation on them. The scripts above do not delete any files unless you explicitly add deletion code.

Can Python automate Google Sheets too? Yes. Use the gspread library. Create a Google Service Account, share your spreadsheet with it, and use the same pandas logic. The API is free for reasonable usage.

What Python editor should I use as a beginner? VS Code with the Python extension is the best free option — excellent autocomplete, integrated terminal, and a built-in debugger. Download it from code.visualstudio.com.