10 Cool Python Codes to Automate Your Spreadsheet: My Journey Through Automation

Introduction:

A few months back, I found myself juggling a massive amount of data in Google Sheets for a project I was working on. It was a reporting task that required constant updates and manual data imports. Honestly, the whole process felt like an endless cycle of copying, pasting, and formatting. That’s when I decided to give Python a shot for automating some of these repetitive tasks. Fast forward to today, these automations are now a regular part of my workflow. Here’s how Python transformed the way I work with spreadsheets.

1. Automating Bulk Data Import from External Sources (APIs or Web Scraping)

• Problem: Last March, I was working on a project where I had to import daily stock market data into Google Sheets. Manually copying the data from various websites was taking up way too much time.
• Solution: I used Python to fetch data from a stock price API and automatically populate the spreadsheet.

import requests
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Authenticate and open the sheet
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', ['https://spreadsheets.google.com/feeds'])
client = gspread.authorize(creds)
sheet = client.open('Stock Prices').sheet1

# Fetch data from the stock API
response = requests.get('https://api.example.com/stock_prices')
stock_data = response.json()

# Insert data into the spreadsheet
for stock in stock_data:
    sheet.append_row([stock['symbol'], stock['price'], stock['change']])

• Why Use It: This automation saved me hours each week. Now, whenever I need up-to-date stock prices, they’re pulled straight into my Google Sheet without lifting a finger.

2. Scheduling and Automating Periodic Updates

• Problem: In August, I was tasked with updating sales performance data every day, but honestly, I would forget to do it half the time.
• Solution: I set up Python to automate the updates at regular intervals, so the data was always fresh.

import time
import gspread

def update_sheet():
    # Fetch new sales data
    sales_data = fetch_sales_data()
    # Update the Google Sheet
    sheet = client.open('Sales Performance').sheet1
    sheet.append_row(sales_data)

while True:
    update_sheet()
    time.sleep(86400)  # Sleep for a day (86400 seconds)

• Why Use It: By automating the updates, I didn’t have to worry about manually entering data at the end of each day. Everything ran on autopilot.

3. Data Cleaning and Transformation

• Problem: I ran into a major headache last November when working on cleaning up a dataset. There were so many inconsistencies and missing values, especially with names and dates. Doing this by hand was not only frustrating, but also error-prone.
• Solution: Python’s pandas library came to the rescue. I could clean, transform, and reformat the data in minutes.

import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Authenticate and load the data
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', ['https://spreadsheets.google.com/feeds'])
client = gspread.authorize(creds)
sheet = client.open('Data Cleanup').sheet1
data = pd.DataFrame(sheet.get_all_records())

# Remove duplicates and fix formatting
cleaned_data = data.drop_duplicates()
cleaned_data['date'] = pd.to_datetime(cleaned_data['date'])

# Update the sheet with cleaned data
sheet.update([cleaned_data.columns.values.tolist()] + cleaned_data.values.tolist())

• Why Use It: Instead of spending days cleaning data manually, I used Python to handle it in a fraction of the time. It transformed a grueling process into something fast and efficient.

4. Automatically Sending Email Notifications Based on Data Changes

• Problem: While working on an e-commerce project last year, I had to manually send email updates whenever a critical value in the sheet changed (like stock levels going low). It became quite a hassle to keep track of it all.
• Solution: I automated the email notifications so that I could focus on the next task instead of constantly monitoring the sheet.

import smtplib
import gspread
from oauth2client.service_account import ServiceAccountCredentials

def send_email(subject, body, to_email):
    sender_email = "your-email@example.com"
    password = "your-email-password"
    
    server = smtplib.SMTP('smtp.gmail.com', 587)
    server.starttls()
    server.login(sender_email, password)
    message = f"Subject: {subject}\n\n{body}"
    server.sendmail(sender_email, to_email, message)
    server.quit()

# Example: If a stock is running low, send an email
sheet = client.open('Product Stock').sheet1
if sheet.cell(2, 3).value == 'Low':
    send_email('Stock Alert', 'Product stock is running low!', 'recipient@example.com')

• Why Use It: This automation took a load off my shoulders. Now, I get alerts only when necessary, and I don’t have to remember to check the sheet every few hours.

5. Advanced Data Filtering and Analysis with Pandas

• Problem: Last December, I had to perform detailed analysis of website traffic, but with thousands of rows of data, Google Sheets’ built-in tools couldn’t handle the complexity. I needed advanced filtering capabilities.
• Solution: Python made it easy to filter and analyze the data using pandas before updating the Google Sheet with the results.

import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Load and filter the data
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', ['https://spreadsheets.google.com/feeds'])
client = gspread.authorize(creds)
sheet = client.open('Website Traffic').sheet1
data = pd.DataFrame(sheet.get_all_records())

# Filter traffic data where views > 1000
filtered_data = data[data['views'] > 1000]

# Update the sheet with filtered data
sheet.update([filtered_data.columns.values.tolist()] + filtered_data.values.tolist())

• Why Use It: This code helped me perform complex data analysis in a snap, avoiding the lag and limitations of Google Sheets. It made filtering and summarizing data much faster and more efficient.

6. Integrating Google Sheets with External APIs

• Problem: A project I worked on last September required me to pull in real-time customer data from a CRM platform. The manual copy-paste approach was time-consuming and error-prone.
• Solution: I used Python to connect to the CRM API and automatically sync the data with Google Sheets.

import requests
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Pull customer data from the CRM API
response = requests.get("https://api.crmplatform.com/customers")
customer_data = response.json()

# Update Google Sheets
sheet = client.open('Customer Data').sheet1
for customer in customer_data:
    sheet.append_row([customer['name'], customer['email'], customer['status']])

• Why Use It: Automating the integration with external APIs saved me hours of manual work. Now, my customer data is always up to date without any extra effort on my part.

7. Automating Data Validation and Error Checking

• Problem: During a project last year, I noticed that people were entering invalid data into the Google Sheet—wrong email formats, missing values, and so on. Manually going through it to fix those errors wasn’t sustainable.
• Solution: I created a Python script that automatically checks for invalid data and alerts me when something goes wrong.

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Load data from sheet
sheet = client.open('User Data').sheet1
data = sheet.get_all_records()

# Check for invalid data
for row in data:
    if not row['Email'] or '@' not in row['Email']:
        sheet.update_cell(row['Row'], 4, 'Invalid Email')

• Why Use It: Automating error checking ensured that the data stayed clean, and I didn’t have to spend time manually fixing mistakes.

8. Creating and Exporting Reports

• Problem: Last July, I had to generate weekly reports from a raw dataset, but doing it manually was tiring and often led to formatting issues.
• Solution: I automated the generation of weekly reports using Python, saving me time and ensuring consistency.

import pandas as pd

# Generate a summary report
data = pd.DataFrame(sheet.get_all_records())
summary = data.describe()

# Export report as CSV
summary.to_csv('weekly_report.csv')

• Why Use It: Now, my reports are automatically generated and exported to CSV with minimal effort, making it easier to analyze data and share insights.

9. Automatically Updating Google Sheets Based on New Data

• Problem: In a project last January, I needed to ensure that my Google Sheets were always synced with the latest data from a source file.
• Solution: I automated the process of checking for updates and syncing the sheet with fresh data using Python.

import os
import gspread
from oauth2client.service_account import ServiceAccountCredentials

if os.path.getmtime('source_data.csv') > last_update_time:
    sheet = client.open('Updated Data').sheet1
    new_data = pd.read_csv('source_data.csv')
    sheet.update([new_data.columns.values.tolist()] + new_data.values.tolist())

• Why Use It: This automation kept my Google Sheets up-to-date with the latest data from the source file without any manual intervention.

10. Automating Complex Calculations and Reports

• Problem: During a project last October, I had to perform complex calculations on sales data, which Google Sheets couldn’t handle efficiently.
• Solution: I wrote a Python script to handle the calculations and automatically update the sheet with the results.

import numpy as np
import pandas as pd

# Calculate average sales
data = pd.read_csv('sales_data.csv')
avg_sales = np.mean(data['sales'])

# Update Google Sheets
sheet = client.open('Sales Report').sheet1
sheet.update_cell(2, 2, avg_sales)

• Why Use It: Python made the complex calculations simple and automated, ensuring I didn’t miss anything or waste time doing repetitive math in Google Sheets.

Conclusion:

• Summary: From automating data imports to generating detailed reports, Python has made my work with Google Sheets much more efficient. Whether you’re handling large datasets, integrating APIs, or automating data cleaning, Python can save you hours and reduce human error.
• Call to Action: If you want to get started with Python automation in Google Sheets, check out the full code samples I’ve shared above. Let me know how Python has helped you in your workflow! Share your experiences in the comments below.