Google Sheets vs Excel Files for Python Automation

Many teams now live in Google Sheets as much as in Excel, and a Python report can target either: write an .xlsx file and send or upload it, or update a shared Google Sheet in place through the Sheets API. The choice changes more than the library. It decides how people get the report, what can go wrong at 3 a.m., and how numbers survive the trip. This page compares the two for automation, with verified examples of the mistakes the Sheets route invites. It is part of Choosing a Python Excel Library.

Prerequisites

pip install gspread pandas openpyxl xlsxwriter

The Google Sheets examples use gspread 6. They run here against a local stand-in for the Sheets API, which records every request, so the exact payloads can be shown without a Google account. Against the real service you would authenticate with a service account first:

import gspread

gc = gspread.service_account(filename="service_account.json")   # a Google Cloud service account key
sh = gc.open_by_key("1AbCdEf...")                                # the ID from the sheet's URL

The service account has its own email address; share the spreadsheet with it as an editor, or it gets a permission error.

The main differences

• Access. An .xlsx needs nothing but a library; the job can run offline. A Google Sheet needs a Google Cloud project, the Sheets API enabled, a service account key stored safely, and network access at run time.
• Limits. Excel sheets hold 1,048,576 rows by 16,384 columns. A Google spreadsheet holds up to 10 million cells across all its tabs, which a 26-column export reaches at about 380,000 rows.
• Rate limits. The Sheets API has per-minute quotas on read and write requests per project and per user, documented in Google's usage limits. A script that updates cells one at a time hits them quickly; one update call per block of cells does not.
• Delivery and versions. A file is a copy, so recipients may keep working on last week's. A shared sheet is always current, with version history built in, but anyone with edit access can change it between runs.
• Formatting. openpyxl and xlsxwriter cover Excel's full model: number formats, conditional formats, charts, tables, data validation. gspread can format ranges and the API can do much of the rest, but each is a separate request, and charts via the API are verbose.

Writing a DataFrame: what breaks

Writing to Excel from pandas is one line, and types come through: dates stay dates, missing values become empty cells. The Sheets API takes JSON, and JSON has no dates and no NaN:

import numpy as np
import pandas as pd

ws = gc.open_by_key("1AbCdEf").worksheet("Report")
df = pd.DataFrame({"Week": pd.to_datetime(["2026-09-21", "2026-09-28"]),
                   "Region": ["North", "South"], "Sales": [48250.5, np.nan]})

for attempt in ([df.columns.tolist()] + df.values.tolist(),
                [["Region", "Sales"], ["South", float("nan")]]):
    try:
        ws.update(attempt, "A1")
    except Exception as e:
        print(type(e).__name__, "-", str(e)[:70])

Both fail before anything is sent, which is the good case: a job that crashes gets noticed. The same error appears for a single value taken from a DataFrame with .iloc or .at, which returns a NumPy integer rather than a Python int; tolist() converts, individual lookups do not. Convert explicitly before sending: dates to ISO strings, missing values to empty strings, NumPy numbers to Python ones. A small helper keeps it in one place:

def to_sheet_values(df):
    out = df.copy()
    for col in out.columns:
        if pd.api.types.is_datetime64_any_dtype(out[col]):
            out[col] = out[col].dt.strftime("%Y-%m-%d")
    out = out.astype(object).where(out.notna(), "")
    return [out.columns.tolist()] + out.values.tolist()

ws.update(to_sheet_values(df), "A1", value_input_option="USER_ENTERED")
print(_rs.calls[-1].request.body.decode())

With USER_ENTERED the API parses 2026-09-28 the way a person typing it would, so it becomes a real date in the sheet; with the default RAW it stays a text string.

Formulas become text by default

That default matters even more for formulas. gspread's update sends valueInputOption=RAW unless told otherwise, and in RAW mode a string starting with = is stored as text:

ws.update([["Total", "=SUM(C2:C9)"]], "A10")
print(_rs.calls[-1].request.url.split("?")[1])
ws.update([["Total", "=SUM(C2:C9)"]], "A10", value_input_option="USER_ENTERED")
print(_rs.calls[-1].request.url.split("?")[1])

The first call puts the literal text =SUM(C2:C9) in the cell. In Excel files there is no such switch: openpyxl and xlsxwriter treat a string starting with = as a formula, which is its own problem when the data comes from users (see Prevent Formula Injection in Excel Exports).

USER_ENTERED has its own trap, shown in the last row: values that look like numbers are parsed as numbers, so an ID such as 00123 loses its zeros. Send such columns in a separate RAW update, or format the column as plain text first.

Reading back: numericised values

Reading has the mirror image of the same problem. The API returns formatted strings, and get_all_records converts anything that looks numeric:

print(ws.get_all_records())
print(ws.get_all_records(numericise_ignore=["all"]))

00123 becomes 123 and the code 1e3 becomes 1000.0. Pass numericise_ignore=["all"] and convert the columns you know are numeric yourself, the same discipline as dtype=str with pd.read_excel; see Keep Leading Zeros When Reading Excel with pandas.

The Excel file route, for comparison

The same report as a file is written locally, with types and formulas intact, and can then be emailed or uploaded to shared storage:

with pd.ExcelWriter("weekly_sales.xlsx", engine="xlsxwriter") as xw:
    df.to_excel(xw, sheet_name="Report", index=False)
    ws_x = xw.sheets["Report"]
    ws_x.write("B10", "Total")
    ws_x.write_formula("C10", "=SUM(C2:C9)")
    ws_x.set_column("A:A", 12, xw.book.add_format({"num_format": "yyyy-mm-dd"}))
print(pd.read_excel("weekly_sales.xlsx").dtypes.astype(str).to_dict())

The dates come back as dates and the sales as numbers, with no conversion step, because the file format has types. The missing sales figure is an empty cell rather than an error. The only thing to remember is to keep labels such as "Total" out of typed columns: a text cell in the date column would make pandas read the whole column back as text.

Uploading the file to Google Drive is a third option: Drive can convert an .xlsx to a Google Sheet on upload, keeping most formatting, which gives Sheets users a native file without going cell by cell through the Sheets API.

Which to choose

• Write Excel files when recipients use Excel, when the report needs rich formatting or charts, when the job must run without network access or credentials, or when each run should produce an archived, immutable copy.
• Update a Google Sheet when the audience already works in Sheets, when many people need the same live numbers at one link, when other sheets pull from it with IMPORTRANGE, or when the data is small and refreshed often.
• Do both when audiences are mixed: build the DataFrame once, write the .xlsx, and push the summary table to a Sheet.

Common pitfalls

• Default RAW input. Formulas and dates arrive as text; pass value_input_option="USER_ENTERED" where you want them parsed.
• NaN and Timestamps. The API needs plain JSON; convert dates to strings and missing values to "".
• Numericised IDs. get_all_records turns 00123 into 123; use numericise_ignore=["all"].
• Cell-by-cell updates. Each update is a request against the quota; send whole ranges.
• Forgetting to share with the service account. The API returns a permission error until the sheet is shared with its email.
• Committed keys. A service account JSON key is a credential; store it like a password, as in Store Credentials for Excel Report Scripts Safely.

Conclusion

Excel files are the simpler, more capable output: no credentials, no quotas, full formatting, types preserved. Google Sheets win when the audience lives there and needs one live, shared version. If you write to Sheets with gspread, convert dates and missing values before sending, choose USER_ENTERED or RAW deliberately per range, batch updates to stay within quotas, and read with numericising turned off.

Frequently asked questions

Can Python write to Google Sheets like it writes Excel files? Yes, with the Sheets API; gspread is the most common library. You need a service account, the spreadsheet shared with it, and values converted to JSON-friendly types.

Why do my formulas show as text in Google Sheets? gspread's update sends values with valueInputOption RAW by default, which stores them exactly as sent. Pass value_input_option="USER_ENTERED" so the sheet parses formulas and dates.

How many rows can a Google Sheet hold? A spreadsheet can hold up to 10 million cells across all its tabs, so the row limit depends on the number of columns. An Excel sheet holds 1,048,576 rows regardless.

Should a scheduled report write to Google Sheets or an Excel file? Write an Excel file when recipients use Excel or need formatting and archived copies; update a Google Sheet when the audience works in Sheets and wants one live link.

Related

• Up one level: Choosing a Python Excel Library — picking the right tool for each job.
• Excel vs CSV vs Parquet for Python Data Pipelines — other output formats.
• Upload an Excel Report to Google Cloud Storage — delivering the file route.
• Store Credentials for Excel Report Scripts Safely — keeping the service account key out of code.
• Prevent Formula Injection in Excel Exports — the other side of strings that start with =.