PyPIDataupdated 06 Aug 2026

gspread

Item: gspread
Rating: 3.3
Author: Keyoor

gspread wraps the Google Sheets v4 REST API in objects that look like the thing you are actually working with: a Client you authenticate once, a Spreadsheet you open by title, key, or URL, and a Worksheet you read and write in A1 notation. Instead of assembling spreadsheets.values.batchUpdate request bodies by hand, you call ws.get_all_records() to get a list of dicts keyed by the header row, ws.update(values, 'A2') to write a block, or ws.append_row([...]) to add a line. It also covers the formatting and structure calls most scripts eventually need: cell colors and bold headers, frozen rows, tab colors, adding and deleting sheets, protected ranges, and sharing the file with an email address. Authentication rides on google-auth, so a service account JSON file or an OAuth user flow both work.

gspread · v6.2.1 · 10.5M/wk · ★ 7,492 · pushed 30 Jul 2026 · burnash/gspread · docs · reviewed by Keyoor

Verdict

The fastest way to get a Python script talking to a Google Sheet, and for a cron job that reads a config tab or writes a weekly report it is still the right pick. Treat the unmaintained banner as real: pin the version, keep the API surface you use small, and have a plan to fall back to google-api-python-client.

API stability3/5The 6.x surface has not moved since May 2025 and probably will not, but the 5-to-6 jump swapped positional arguments on the most-used method, removed get_records, and changed the color format, so the project has a history of breaking scripts on majors

Docs4/5docs.gspread.org has a full API reference plus task-oriented pages for OAuth setup and common operations, and the README carries a clear v5-to-v6 migration guide; some newer parameters are documented only by their type hints

Maintenance2/5The README states outright that the project cannot currently be maintained and is looking for maintainers, the last release was 6.2.1 in May 2025, and 49 issues are open (69 counting PRs); recent pushes are housekeeping rather than fixes

Ecosystem4/5Around 10.5M weekly downloads and 7.5k stars make it the default answer for Python plus Google Sheets, with companion packages like gspread-dataframe, gspread-formatting, and gspread-asyncio filling the gaps; those companions are also small single-maintainer projects

Use it if

You are writing a script or cron job that reads a Google Sheet as a small config table or writes a report into one, and the official google-api-python-client feels like too much ceremony
Non-technical colleagues own the data and a spreadsheet is genuinely the right interface, so you need something that talks A1 ranges and header rows rather than JSON request bodies
You want a service account flow that is three lines: share the sheet with the service account email and call gspread.service_account()
You need light formatting alongside the data (bold headers, frozen top row, tab colors, column widths) without learning the batchUpdate request schema

Skip it if

You need a maintained dependency: the README opens with a 'Maintainer needed' notice saying the project cannot currently be maintained, and the last release, 6.2.1, went out in May 2025
You are moving real volume: Google enforces per-minute read and write quotas per user and per project, and gspread's convenience methods each cost an API call, so a loop over 500 rows calling update_acell hits 429s in under a minute
You need async: gspread is synchronous requests under the hood, and the only option is the separately maintained gspread-asyncio wrapper
You are using a spreadsheet as a database: there is no transaction, no concurrency control, and two writers on the same range silently overwrite each other
You are on gspread 5 and upgrading casually: v6 swapped the first two arguments of Worksheet.update, removed Worksheet.get_records, changed colors from dicts to hex strings, and turned lastUpdateTime from a property into a method, so old scripts fail in quiet ways rather than loudly
You want typed, complete API coverage: gspread exposes the parts its contributors needed, and anything past that means dropping to google-api-python-client for the raw endpoint

Setup reality

pip install gspread pulls only google-auth and google-auth-oauthlib, so there is no compiler and no heavy dependency tree. The pain is entirely Google Cloud. You have to create a project, enable both the Google Sheets API and the Google Drive API (open by title and any file listing fails without Drive), then create either a service account or an OAuth client. For a service account you download the JSON key, put it at ~/.config/gspread/service_account.json or pass filename=, and then share the actual spreadsheet with the service account's email address, which is the step everyone misses and it surfaces as a confusing 404 SpreadsheetNotFound rather than a permission error. The OAuth path opens a browser once and caches a token at ~/.config/gspread/authorized_user.json, which does not work on a headless server. Default scopes include Drive write access; pass READONLY_SCOPES or a narrower list if your security review cares. Quota errors arrive as gspread.exceptions.APIError with code 429 and no retry, unless you construct the client with BackOffHTTPClient.

Patterns

Authenticate with a service accountservice-account-auth

import gspread

# looks for ~/.config/gspread/service_account.json
gc = gspread.service_account()

# or point at the key explicitly
gc = gspread.service_account(filename="/etc/secrets/sheets-bot.json")

# or from an env var, so nothing lands on disk
import json, os
gc = gspread.service_account_from_dict(json.loads(os.environ["GOOGLE_SA_JSON"]))

Share the spreadsheet with the client_email from the key file or every open() raises SpreadsheetNotFound, which reads like a typo rather than a permissions problem. Service accounts have their own Drive, so files they create are invisible to you until shared back.

Open a sheet by URL, key, or titleopen-spreadsheet

sh = gc.open_by_url("https://docs.google.com/spreadsheets/d/1AbC.../edit")
sh = gc.open_by_key("1AbC...")
sh = gc.open("Q3 Revenue")   # needs the Drive API enabled

ws = sh.sheet1                      # first tab
ws = sh.worksheet("raw_data")       # by title
ws = sh.get_worksheet(2)            # by zero-based index

open_by_key is the only one that costs a single request and cannot break when someone renames the file. open() by title searches Drive, so it fails with an unhelpful error when the Drive API is not enabled on the project.

Read the whole tab as recordsread-rows-as-dicts

rows = ws.get_all_records()          # header row becomes the dict keys
# [{'id': 1, 'name': 'Ada', 'active': True}, ...]

rows = ws.get_all_records(
    head=2,                          # header is on row 2
    expected_headers=["id", "name"], # tolerate extra columns
    default_blank=None,
)

get_all_records pulls the entire sheet in one request and numericises values, so '007' comes back as 7 and '1,5' can surprise you; pass value_render_option=ValueRenderOption.unformatted or read with get_all_values if you need the raw strings. Duplicate header names raise.

Read a specific range or cellread-a-range

values = ws.get("A1:C10")            # list of lists, ragged rows are trimmed
row = ws.row_values(4)
col = ws.col_values(2)
name = ws.acell("B2").value
formula = ws.acell("B2", value_render_option="FORMULA").value

Trailing empty cells are omitted rather than padded, so row lengths differ between rows. Use gspread.utils.fill_gaps or index defensively before zipping rows against headers.

Write a 2D block in one requestwrite-a-block

from gspread.utils import ValueInputOption

ws.update(
    [["name", "score"], ["Ada", 99], ["Grace", 97]],
    "A1",
    value_input_option=ValueInputOption.user_entered,
)

ws.update_acell("D1", "=SUM(B2:B3)")

In v6 the argument order is (values, range_name); in v5 it was the reverse, and passing them the old way writes your range string into a cell. values must be a 2D list, never a flat one. user_entered makes Sheets parse dates and formulas; the default RAW stores them as text.

Update several ranges in one API callbatch-write

ws.batch_update([
    {"range": "A1:B1", "values": [["name", "score"]]},
    {"range": "A2:B3", "values": [["Ada", 99], ["Grace", 97]]},
    {"range": "D1",    "values": [["generated " + today]]},
])

This is the single most useful habit for staying inside quota: one batch_update costs one write, where a loop of update_acell costs one per cell. The same idea applies to reads via ws.batch_get([...]).

Append rows below the existing dataappend-rows

ws.append_row(["2026-08-06", "signup", 41])

ws.append_rows(
    [["2026-08-06", "signup", 41], ["2026-08-06", "churn", 3]],
    value_input_option="USER_ENTERED",
    table_range="A1",
)

Append finds the last row of the contiguous table, so a stray value far down the sheet pushes new rows there. Pass table_range to pin which block it appends to, and prefer append_rows over a loop of append_row.

Bold the header, freeze it, and color a tabformat-header

ws.format("A1:C1", {
    "textFormat": {"bold": True},
    "backgroundColor": {"red": 0.9, "green": 0.9, "blue": 0.9},
})
ws.freeze(rows=1)
ws.update_tab_color("#FF7FFF")
ws.columns_auto_resize(0, 2)

v6 takes tab colors as hex strings where v5 took a dict; gspread.utils.convert_colors_to_hex_value converts old code. Cell formatting inside format() still uses the API's 0-to-1 float color dicts, which is an inconsistency in the library, not a typo here.

Search cells and clear rangesfind-and-clear

import re

cell = ws.find("Total")               # first match, or None
matches = ws.findall(re.compile(r"^Q[1-4]"))
print(cell.row, cell.col, cell.value)

ws.batch_clear(["A2:C1000"])          # wipe data, keep the header
ws.clear()                            # wipe everything

find pulls the sheet contents to search client-side, so it is one full read per call; cache the values and search locally if you need more than a couple of lookups.

Survive Google's per-minute quotashandle-rate-limits

import gspread
from gspread.exceptions import APIError

gc = gspread.service_account(http_client=gspread.BackOffHTTPClient)

try:
    ws.update(rows, "A2")
except APIError as exc:
    if exc.code == 429:
        log.warning("sheets quota exhausted, retrying next run")
    else:
        raise

BackOffHTTPClient retries 429 and 5xx with exponential backoff; the default HTTPClient does not retry at all. Backoff does not create quota, so batch your calls first and treat this as the safety net.

Create a spreadsheet and give someone accesscreate-and-share

sh = gc.create("Weekly Report 2026-08", folder_id="1FoLdEr...")
sh.share("analytics@example.com", perm_type="user", role="writer", notify=False)
sh.share(None, perm_type="anyone", role="reader")
print(sh.url)

A file created by a service account lives in that account's Drive and counts against its storage, which is why folder_id plus an explicit share is the usual pattern. notify=False skips the email, which matters for automated runs.

Convert between A1 labels and row or column indexesa1-helpers

from gspread.utils import rowcol_to_a1, a1_to_rowcol, column_letter_to_index

rowcol_to_a1(3, 27)          # 'AA3'
a1_to_rowcol("AA3")          # (3, 27)
column_letter_to_index("AA") # 27

last = rowcol_to_a1(len(rows) + 1, len(rows[0]))
ws.update(rows, f"A2:{last}")

gspread rows and columns are 1-based while your Python lists are 0-based, and mixing the two is the classic off-by-one in this library. Computing the end label like this avoids writing a block larger than the range and getting a size mismatch error.

Alternatives

Package	Registry	Pick it when
google-api-python-client	PyPI	You need endpoints gspread does not wrap, or you already use it for Drive, Gmail, or Calendar and want one client
pygsheets	PyPI	You want a similar convenience wrapper with built-in pandas DataFrame read and write
gspread-asyncio	PyPI	You are in an asyncio service and need non-blocking calls plus built-in rate limiting
pyarrow	PyPI	The spreadsheet was only ever a data handoff and you can switch to Parquet or CSV in object storage instead