Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Sync Google Sheets with Excel Using Python

Move data both ways between Google Sheets and .xlsx: service-account auth with gspread, reading a tab into pandas, writing back, exporting a whole spreadsheet and scheduling it.

Google Sheets and Excel end up on both sides of the same reporting process constantly: a team maintains a sheet of manual adjustments, a system produces .xlsx extracts, and something has to reconcile them. Python does the trip in either direction — gspread for the API and pandas for the reshaping — and once a service account is in place the sync runs unattended. This guide sets up authentication, moves data both ways, exports a whole spreadsheet as a workbook, and schedules the result. It belongs to Moving Data Between Excel and Databases.

Both directions of a Sheets-to-Excel sync A worksheet's values are read into a DataFrame and written to a formatted workbook, and a computed report is pushed back to a tab that colleagues can read in the browser. One credential, two directions Google Sheet edited by people read write pandas clean, join, aggregate write read .xlsx report formatted, delivered Values move; presentation is rebuilt at each end

Prerequisites

Bash
pip install gspread google-auth pandas openpyxl xlsxwriter

You also need a Google Cloud project with the Sheets and Drive APIs enabled, and a service-account key in JSON form.

Authenticate as a service account

A service account is its own identity with its own email address. Create the key, store it outside the repository, and point the code at it through an environment variable:

Python
"""Authorise gspread with a service account."""
import os

import gspread
from google.oauth2.service_account import Credentials

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

creds = Credentials.from_service_account_file(
    os.environ["GOOGLE_APPLICATION_CREDENTIALS"], scopes=SCOPES
)
client = gspread.authorize(creds)

The step people miss is sharing: open the spreadsheet in the browser and share it with the client_email from the JSON key, exactly as you would with a colleague. Without that, every request returns a 403 on a document you can see perfectly well yourself.

Read a tab into pandas

Python
import pandas as pd

sheet = client.open_by_key("1AbCdEf...")        # the id from the URL
tab = sheet.worksheet("Adjustments")

records = tab.get_all_records()                 # first row becomes the keys
df = pd.DataFrame(records)
print(df.dtypes)

get_all_records is one API call for the whole tab, which matters for quota. Everything arrives as text or numbers according to what the sheet holds, so pin the types you care about immediately:

Python
df["adjustment"] = pd.to_numeric(df["adjustment"], errors="coerce")
df["effective"] = pd.to_datetime(df["effective"], errors="coerce")
df["reference"] = df["reference"].astype("string")     # keep leading zeros

errors="coerce" turns the inevitable stray text into NaT or NaN rather than raising — then count them, because a handful of unparsed values is a data-quality signal worth reporting.

Write a formatted workbook from the sheet

Python
with pd.ExcelWriter("adjustments.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, index=False, sheet_name="Adjustments")
    book, ws = writer.book, writer.sheets["Adjustments"]
    money = book.add_format({"num_format": "#,##0.00"})
    ws.set_column("C:C", 14, money)
    ws.freeze_panes(1, 0)
    ws.autofilter(0, 0, len(df), len(df.columns) - 1)

Formatting does not travel with the values, so it is rebuilt here — which is an advantage as often as it is a cost, since the workbook can follow your own report conventions rather than whatever the sheet's authors chose.

Push a report back to a tab

The other direction replaces a tab's contents in one call:

Python
import gspread

summary = (df.groupby("region", as_index=False)["adjustment"].sum()
             .sort_values("adjustment", ascending=False))

try:
    out = sheet.worksheet("Summary")
    out.clear()
except gspread.WorksheetNotFound:
    out = sheet.add_worksheet(title="Summary", rows=200, cols=10)

out.update(
    [summary.columns.tolist()] + summary.astype(object).where(summary.notna(), "").values.tolist(),
    value_input_option="USER_ENTERED",
)
out.format("A1:B1", {"textFormat": {"bold": True}})

Two details matter. value_input_option="USER_ENTERED" makes Sheets parse dates and numbers as a person typing them would; RAW stores everything as text. And NaN must become "" before the update — the API rejects it, since it is not valid JSON.

Batching keeps a sync inside the API quota Reading cell by cell and writing row by row costs one API call each and exhausts the per-minute quota, while one bulk read and one bulk update cost two calls in total. per-cell access batched access tab.cell(r, c) — one call per cell 500 rows = thousands of calls 429 rate-limit errors get_all_records() — one call update(values) — one call comfortably inside quota

Export the whole spreadsheet as a workbook

When you want every tab and no transformation, ask Drive to do the conversion:

Python
"""Download a Google spreadsheet as a real .xlsx."""
import os

import requests
from google.auth.transport.requests import Request
from google.oauth2.service_account import Credentials

creds = Credentials.from_service_account_file(
    os.environ["GOOGLE_APPLICATION_CREDENTIALS"],
    scopes=["https://www.googleapis.com/auth/drive.readonly"],
)
creds.refresh(Request())

file_id = "1AbCdEf..."
url = f"https://www.googleapis.com/drive/v3/files/{file_id}/export"
resp = requests.get(url, params={"mimeType":
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"},
    headers={"Authorization": f"Bearer {creds.token}"}, timeout=60)
resp.raise_for_status()

with open("spreadsheet.xlsx", "wb") as fh:
    fh.write(resp.content)

This preserves tabs, values and much of the formatting, and it is a single request. Read it back with any normal reader afterwards.

Reconcile the two sides

The point of a sync is usually a comparison — which manual adjustments have no matching order, and which orders were adjusted twice:

Python
import pandas as pd

orders = pd.read_excel("orders.xlsx", dtype={"reference": "string"})
adjustments = df                                   # from the sheet, above

merged = orders.merge(adjustments, on="reference", how="left", indicator=True)
unmatched = adjustments[~adjustments["reference"].isin(orders["reference"])]
duplicated = adjustments[adjustments.duplicated("reference", keep=False)]

print(f"{len(unmatched)} adjustment(s) match no order")
print(f"{len(duplicated)} reference(s) adjusted more than once")

Writing those two frames back to the sheet as their own tabs closes the loop, because the people who created the discrepancies are the ones already looking at that document. The join techniques are covered in Find rows in one Excel file missing from another.

Schedule the sync

Nothing about this needs to be interactive. A cron entry, a systemd timer or a CI schedule runs it, provided the credentials reach the job:

Bash
GOOGLE_APPLICATION_CREDENTIALS=/etc/secrets/reporting-sa.json \
  /opt/reporting/venv/bin/python /opt/reporting/sync_sheets.py >> /var/log/sync.log 2>&1

Keep the key file readable only by the job's user, rotate it on the same schedule as any other credential, and never commit it. The scheduling patterns are in Scheduling Python Excel Scripts with Cron and Run a Python Excel report in GitHub Actions.

Decide which side owns the data

A two-way sync without an owner produces conflicts nobody can resolve: the sheet says one thing, the workbook another, and both were edited since the last run. Settle the question before writing any code.

Three ownership models for a sheet-and-workbook pair Either the sheet is the source and the workbook is generated, or the system is the source and the sheet is a read-only view, or each side owns distinct columns. Pick one and enforce it in code sheet is the source people type there workbook is generated never write back system is the source sheet is a live view overwritten each run protect it from edits split by column system writes facts people write notes update ranges, not tabs

The third model is the most common in practice and the one that needs the most care: update named ranges rather than clearing the tab, or a run will wipe the column of human notes it was never meant to touch. Protect the machine-owned columns in the sheet's own settings so an accidental edit is prevented rather than silently overwritten on the next run.

Common pitfalls and gotchas

  • Forgetting to share the document with the service account's email — the cause of nearly every 403.
  • Per-cell reads and writes. Batch with get_all_records and a single update; anything else exhausts the quota.
  • NaN in an update payload. Replace with "" before sending; the API rejects non-JSON values.
  • Duplicate headers in the sheet. get_all_records raises on them, because the result is a dictionary per row.
  • Assuming formatting travels. It does not; rebuild it on the Excel side.

Performance and scale notes

The API, not pandas, is the constraint. Google enforces per-minute read and write quotas, so a sync should make a small, fixed number of calls regardless of row count: one read per tab, one update per tab. Above a few tens of thousands of rows a spreadsheet is the wrong store entirely — move the data to a database and let Sheets hold only the manual layer, which is the pattern described in Load an Excel file into a SQL database with pandas. Add retry with backoff on 429 responses so a busy minute delays the job rather than failing it.

Conclusion

A service account plus gspread turns Google Sheets into just another data source: authenticate once, read a tab in a single call, reshape in pandas, and write a formatted workbook. The reverse trip is one update call, and the Drive export endpoint hands you the whole spreadsheet as .xlsx when no transformation is needed. Batch every access, replace missing values before sending, and keep the key file out of the repository — then schedule it and let the reconciliation run itself.

Frequently asked questions

Do I need OAuth or a service account? A service account for anything unattended. It authenticates as itself with a JSON key, needs no browser consent, and you simply share the spreadsheet with its email address. OAuth is for tools acting on behalf of a signed-in person.

Why does my script get a 403 on a sheet I can open? You are opening it as yourself; the script is a different identity. Share the spreadsheet with the service account's client_email, exactly as you would with a colleague.

How do I keep formatting when copying to Excel? You do not — gspread reads values. Read the values with gspread, then apply your own formatting with xlsxwriter or openpyxl on the way into the workbook.

What are the API limits? Google enforces per-minute read and write quotas per project and per user. Batch operations — one get_all_records instead of many cell reads, one update instead of many — keep a sync comfortably inside them.

Can I just download the spreadsheet as xlsx? Yes. The Drive export endpoint returns the whole spreadsheet as a workbook, which is the simplest route when you want every tab and do not need to transform anything.