How to Import Google Sheets Data to Pandas

You can import an anonymously accessible Google Sheet into pandas without OAuth by reading its CSV export. This is suitable only for data the owner intends to expose to unauthenticated readers. A restricted sheet needs an authorized access method; changing its sharing settings is a data-publication decision, not a harmless installation step.

This tutorial was originally presented by skills.ai. The revised examples below use synthetic records. They were tested with Python 3.12.14, Requests 2.34.2, pandas 2.2.3 with Copy-on-Write and pandas 3.0.6. CSV parsing and mocked HTTP responses were verified; no Google account, private sheet, or live Google export was accessed during that validation.

Choose public export or authorized private access

For public data, Google offers publishing options under File → Share → Publish to web, with sheet and format choices. Organizations may restrict publication. Sharing with “Anyone with the link” is a separate permission setting: anyone who obtains that link may be able to read the file without signing in, even if it is not listed publicly. Review the actual audience and selected worksheet before using either option.

For private data, keep the restriction and use the Sheets API with appropriate authorization, such as a read-only scope and an identity granted access to the spreadsheet. A service account is still an authenticated identity; it is not an anonymous workaround.

Construct a worksheet export URL explicitly

For an anonymously readable spreadsheet with ID spreadsheet_id and worksheet gid sheet_gid, the direct export pattern is https://docs.google.com/spreadsheets/d/spreadsheet_id/export?format=csv&gid=sheet_gid. The gid identifies a worksheet tab, not its position. Published-to-web links may instead contain a publication ID and a /pub path; use the CSV link Google supplies for that publication rather than rewriting it as an ordinary file ID.

import re
from urllib.parse import urlencode

def sheet_export_url(spreadsheet_id, sheet_gid):
    if not isinstance(spreadsheet_id, str) or not re.fullmatch(r'[A-Za-z0-9_-]+', spreadsheet_id):
        raise ValueError('spreadsheet ID must contain only letters, digits, underscore or hyphen')
    if not isinstance(sheet_gid, str) or not re.fullmatch(r'[0-9]+', sheet_gid):
        raise ValueError('worksheet gid must be a non-negative integer string')
    query = urlencode({'format': 'csv', 'gid': sheet_gid})
    return f'https://docs.google.com/spreadsheets/d/{spreadsheet_id}/export?{query}'

# This is a synthetic identifier, not a live spreadsheet.
print(sheet_export_url('teaching_sheet_id', '123'))

Expected output:

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

Install pandas and Requests in your chosen Python environment. Verify a real export anonymously before putting it on a schedule: a browser login can conceal the fact that the URL is unavailable to the script. The direct export route is a website endpoint rather than a substitute for the versioned Sheets API.

Test parsing before making a network call

This fixture expects unique nonempty item IDs and non-negative integer units. ID 001 must remain text. An empty units cell is invalid for this task, so the parser raises instead of silently filling it with zero.

from io import BytesIO
import pandas as pd

def parse_inventory_csv(data):
    if not isinstance(data, bytes):
        raise TypeError('CSV input must be bytes')
    if len(data) > 1_000_000:
        raise ValueError('CSV exceeds the byte limit')
    frame = pd.read_csv(
        BytesIO(data), encoding='utf-8-sig',
        dtype={'item_id': 'string', 'units': 'Int64'},
        keep_default_na=False, na_values={'units': ['']}, nrows=10001,
    )
    if frame.columns.tolist() != ['item_id', 'units']:
        raise ValueError('expected item_id and units columns in that order')
    if frame.empty or len(frame) > 10000:
        raise ValueError('expected between 1 and 10000 rows')
    if frame['item_id'].str.strip().eq('').any() or not frame['item_id'].is_unique:
        raise ValueError('item IDs must be nonempty and unique')
    if frame['units'].isna().any() or frame['units'].lt(0).any():
        raise ValueError('units must be present and non-negative')
    return frame

sample_csv = b'item_id,units\n001,2\n002,3\n'
frame = parse_inventory_csv(sample_csv)
print(frame['item_id'].tolist())
print(frame['units'].tolist())
print(str(frame['units'].dtype))

Expected output:

['001', '002']
[2, 3]
Int64

Fetch public CSV with status, type, timeout and size checks

The function below accepts only a spreadsheet ID and gid, builds the Google URL itself, and follows at most three redirects. It uses no credentials or cookies from a logged-in browser. Ambient proxy and netrc settings are disabled in this standalone example; configure those deliberately if your environment requires them.

import requests

def load_public_sheet(spreadsheet_id, sheet_gid):
    url = sheet_export_url(spreadsheet_id, sheet_gid)
    with requests.Session() as session:
        session.trust_env = False
        session.max_redirects = 3
        with session.get(url, timeout=(5, 15), stream=True,
                         headers={'Accept-Encoding': 'identity'}) as response:
            response.raise_for_status()
            if response.status_code != 200:
                raise ValueError('expected a complete HTTP 200 CSV response')
            media_type = response.headers.get('Content-Type', '').split(';', 1)[0].lower()
            if media_type not in {'text/csv', 'application/csv'}:
                raise ValueError('expected CSV, not an HTML login or error page')
            if response.headers.get('Content-Encoding', 'identity').lower() != 'identity':
                raise ValueError('unexpected compressed response')
            data = bytearray()
            for chunk in response.iter_content(chunk_size=16384):
                if len(data) + len(chunk) > 1_000_000:
                    raise ValueError('CSV exceeds the byte limit')
                data.extend(chunk)
    return parse_inventory_csv(bytes(data))

Call load_public_sheet(your_spreadsheet_id, your_sheet_gid) only for your approved public data. Network errors, HTTP failures, unexpected content and schema violations propagate to the caller. The read timeout limits socket inactivity, not the total duration of a slow trickling transfer. There are no automatic retries; a scheduled job should record failure and apply an explicit bounded retry policy.

Diagnose the response before cleaning the data

A 403 can indicate permission or policy restrictions; a 404 can mean a wrong identifier or an unavailable resource. A 200 HTML response is not a successful CSV import. If the schema changes, investigate the sheet rather than dropping unexpected columns blindly. CSV exports carry values, not the workbook's formatting, formulas as executable logic, or every worksheet at once.

Keep a known synthetic fixture as a regression test, then separately verify the actual Google delivery path and permissions. For downstream file contracts, see pandas file I/O. Primary references: Google publication controls, Sheets API scopes, and read_csv.