Upload Excel to Google Sheets: automate the process

When you upload Excel to Google Sheets manually, you're spending 5-10 minutes on a task that code can finish in seconds. A 2024 survey by Zapier found that knowledge workers spend an average of 4.5 hours per week on manual data transfer between tools. CodeWords handles this with serverless Python microservices that watch for new Excel files, convert them, and push the data to Google Sheets — with built-in validation and Slack notifications when something goes wrong.

How do you upload Excel to Google Sheets with Python?

import gspread
import pandas as pd
from google.oauth2.service_account import Credentials

SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive.file",
]

creds = Credentials.from_service_account_file("service-account.json", scopes=SCOPES)
gc = gspread.authorize(creds)

def read_excel(file_path: str) -> pd.DataFrame:
    df = pd.read_excel(file_path, engine="openpyxl")
    df = df.dropna(how="all")
    df = df.fillna("")
    return df

def upload_to_sheets(gc, spreadsheet_id: str, df: pd.DataFrame, worksheet_name: str = "Sheet1"):
    spreadsheet = gc.open_by_key(spreadsheet_id)
    worksheet = spreadsheet.worksheet(worksheet_name)
    worksheet.clear()
    headers = df.columns.tolist()
    rows = df.astype(str).values.tolist()
    worksheet.update(range_name="A1", values=[headers] + rows)
    print(f"Uploaded {len(rows)} rows")

How do you build an automated pipeline with CodeWords?

Monitor a Google Drive folder or email inbox for incoming Excel files. When a new file arrives, CodeWords downloads it, reads with pandas, validates the schema (required columns, data types), and pushes to the target spreadsheet. Failed validations send a Slack notification before the import reaches the sheet.

FAQs

Can I upload Excel to Google Sheets without losing formatting?
The Google Drive API's conversion preserves most formatting — cell colors, borders, and simple formulas. VBA macros don't transfer.

What's the maximum file size?
Google Sheets supports up to 10 million cells. Files larger than this need to be split or stored in BigQuery.