Converting Google Sheets to CSV or Excel With a Script

Sheets is where the team keeps the list, and a job needs it as a file. The conversion is one HTTP request, and the part that deserves care is who is allowed to make it. Handling credentials correctly matters more here than the format. This article covers the three routes: the export URL, the Drive API and the gspread library. It belongs alongside importing Google Sheets data into a database, which takes the same file the rest of the way, and the general advice in converting Excel to CSV for every sheet applies to what you do with it next.

Pick the identity before the method

A job that reads a sheet needs an identity. There are three options and one of them is a trap.

A service account is a non-human Google identity. You create it in a Google Cloud project, download its key (or better, use workload identity), and share the spreadsheet with its email address as a viewer. The job authenticates as the service account, and the sheet is not public. This is the right choice for scheduled work.

Your own user account via OAuth works for a script you run by hand. It acts as you, with your access, and the token expires or is revoked when you leave the team.

A public link ("anyone with the link can view") needs no credentials and means anyone who finds or is sent the URL can read the data. For a price list that is already public, fine. For anything else, do not do it as a shortcut for a job.

Share the sheet with the service account as a viewer, not an editor. A read job has no business changing the data.

Route one: the export URL

For one tab as CSV, the spreadsheet export URL is the shortest path:

https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/export?format=csv&gid=0

The gid is the number after gid= in the browser address bar when that tab is open. The first tab is often 0, but not always, so read it rather than assume. Without a gid you get the first tab.

With a service account the request carries a bearer token:

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

creds = service_account.Credentials.from_service_account_file(
    "key.json",
    scopes=["https://www.googleapis.com/auth/drive.readonly"],
)
creds.refresh(Request())

url = "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/export"
r = requests.get(url, params={"format": "csv", "gid": "0"},
                 headers={"Authorization": f"Bearer {creds.token}"}, timeout=60)
r.raise_for_status()
open("prices.csv", "wb").write(r.content)

The drive.readonly scope is the narrowest that fits. This URL is a long-standing convenience rather than a documented API contract, so for anything you depend on, prefer the Drive API route below.

Route two: the Drive API export

The Drive API's files.export method converts a Google Sheet to a given MIME type. For the whole workbook as xlsx:

from googleapiclient.discovery import build

drive = build("drive", "v3", credentials=creds)
data = drive.files().export(
    fileId="SPREADSHEET_ID",
    mimeType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
).execute()
open("workbook.xlsx", "wb").write(data)

Using text/csv as the MIME type exports only the first sheet. The API limits export size to 10 MB, which is generous for a price list and a hard stop for a large data sheet. If you hit it, read the values through the Sheets API in ranges instead (using the Sheets API from Python). The Drive API reference lists the supported export formats.

Route three: gspread

If you want values in Python and not a file, gspread reads a tab into a list of rows:

import gspread

gc = gspread.service_account(filename="key.json")
ws = gc.open_by_key("SPREADSHEET_ID").worksheet("Prices")
rows = ws.get_all_values()

get_all_values returns text exactly as displayed, which makes dates and numbers locale dependent. get_all_records uses the first row as keys. Both are convenient and neither tells you a type, so cast on purpose. For file output, write the rows with the csv module, which quotes correctly:

import csv
with open("prices.csv", "w", newline="", encoding="utf-8") as f:
    csv.writer(f).writerows(rows)

What the export contains

The export holds computed values, not formulas. A cell that shows 1,250 because of a SUM arrives as 1250 or 1,250 depending on the route and the spreadsheet's locale. The CSV export applies the display format for many cell types, so a currency cell may arrive with its symbol and thousands separators, which a numeric column cannot parse. The xlsx export is closer to the underlying values and carries the number formats separately. If you need the raw number, request it with the Sheets API's valueRenderOption=UNFORMATTED_VALUE.

Date cells are the other trap. A date in CSV is a string in the spreadsheet's locale ("3/4/2025" means different days to different readers). Set the sheet's locale deliberately in File, Settings, and use an ISO format in a helper column if a script depends on it (importing Excel dates correctly).

Check the download

An export can succeed and still be wrong: the wrong tab, a filtered view, hidden rows included, or a partial download. Check three things.

1. The HTTP status is 200 and Content-Type is what you expected. A sign-in page returned with status 200 is a common failure, and it is HTML.

2. The row count equals the count you expect, with a header row and no trailing blank rows.

3. One control total, such as the sum of a numeric column, matches the sheet.

import pandas as pd
df = pd.read_csv("prices.csv", dtype={"sku": "string"})
assert len(df) > 0 and "sku" in df.columns
assert df["sku"].is_unique

Fail the job when a check fails, and keep the previous good file in place.

The Sheets API reference documents valueRenderOption and the other read parameters, and it is the better route whenever you need values in a known format rather than a display snapshot.

Do not leak the sheet

Treat the downloaded file like the data it is. Do not commit it to a public repository, do not leave the service account key in the repository either (use a secret store), and do not log the contents. If the sheet has an owner who can change who sees it, tell them about the job so that sharing changes do not break it silently.

When exporting a sheet is the wrong approach

Exporting is the wrong approach when people edit the sheet and the job runs at the same moment, since you can read a half-edited state. It is wrong for large data, where a database should hold the records and Sheets should be a view of them (when a spreadsheet has become a database). And it is wrong when the sheet's structure is a report, with merged cells and summary rows, since a converter cannot guess which rows are data. Ask for a clean data tab, or build one with a query on the sheet.