Google Sheets: tables, ranges and reliability

Choose the read model

Use list_google_sheets_rows for tables with unique column names in row 1. Use read_google_sheets_range for banners, duplicate labels, merged cells, section markers and repeating row groups. Existing row helpers and return shapes remain unchanged. Grid reads never guess headers or rename columns.

Access

Every helper authenticates with the Octocom service account: [email protected]. There is no anonymous fallback. Share with Viewer for reads and Editor for writes.

An authenticated read can also succeed on a publicly shared workbook. Success therefore does not prove that this account was explicitly added. Check the workbook's Share dialog and add the account before disabling link-sharing. Google may report an inaccessible workbook as 404; that cannot always be separated from a nonexistent spreadsheet. Missing-tab errors list available tabs.

Discover tabs

tabs = list_google_sheets_tabs("SHEET_ID")["tabs"]
visible_tabs = [tab for tab in tabs if not tab["hidden"]]

Each tab returns sheetId, title, index, hidden, rowCount and columnCount. Dimensions are allocated grid size, not the last populated cell. Reads are fresh; select the intended tab explicitly when several are visible.

Read by position

page = read_google_sheets_range("SHEET_ID", "B2:D100", sheet_name="October")
for offset, row in enumerate(page["rows"]):
    sheet_row = page["startRow"] + offset
    label = row[0]["displayText"]  # column B
    urls = row[1]["hyperlinks"]    # column C
    print(sheet_row, label, urls)

The response has sheetId, sheetName, range, one-based startRow and startColumn, rectangular rows, and merges. Each cell has displayText and hyperlinks, plus formula and note when present. Duplicate URLs in a cell are removed. Cell notes are included; threaded comments, images and smart chip metadata are not returned.

Blank cells, including trailing blanks, keep their positions. Merged values stay in the top-left cell; other cells are not filled automatically. merges contains intersecting Google grid ranges with zero-based, end-exclusive indices. A merge beginning outside the requested rectangle may have its value outside it.

Supply a tab-local cell or bounded rectangle, such as A1 or A1:D100. Whole columns, whole rows, named ranges and Tab!A1 are rejected. The limit is 10,000 cells per call, within the tab's current dimensions. For large sheets, read successive row windows; there is no cursor or automatic whole-tab download. Each window is a fresh read, not a snapshot across calls.

Write or correct a report

write_google_sheets_range(
    "SHEET_ID", "A1:C2",
    [["Report", "Date", "Public or DM?"], ["Weekly", "2026-10-13", "DM"]],
    sheet_name="Reports",
)

The values matrix must match the entire bounded rectangle. Strings, numbers and booleans are accepted; write request bodies are limited to 2 MB; use "" to clear a cell. The default value_input_option="RAW" stores literal values, including strings starting with =. Pass value_input_option="USER_ENTERED" to interpret formulas and dates. Writes do not insert rows or resize tabs. They overwrite the selected values; use a dedicated report area and verify it before writing.

clear_google_sheets_range("SHEET_ID", "A2:C100", sheet_name="Reports")

Clearing removes values and formulas while preserving formatting, notes, validation and row positions. It is not row deletion. Clearing and writing are separate operations, not a transaction. To replace a report in one operation, write its full rectangle, padding leftover rows with empty strings.

Append without replacing headers

add_google_sheets_row(
    "SHEET_ID", {"Report": "Weekly", "Public or DM?": "DM"},
    sheet_name="Reports", preserve_headers=True,
)

The opt-in retains existing columns and appends missing headers on the right. Empty sheets get their initial headers. Existing headers must be unique; the operation reads the header before writing and is not atomic with concurrent schema edits. Without this option, legacy append behavior replaces row-1 headers with the supplied dictionary keys. Existing callers keep that behavior.

Paging, caching and failures

Structured row reads return at most 100 rows; start with 50, or 100 for bulk processing. Filtered calls scan at most 5,000 rows and continue from their scan position. Follow nextCursor even when a filtered page is empty. Row numbers and cursors can become stale when people sort or insert rows.

Structured reads are cached for up to 60 seconds. Writes through these helpers invalidate cached rows across backend processes; edits made directly in Google may take up to 60 seconds to appear. Range and tab reads bypass result caching.

Duplicate/blank headers return a 400 explaining the layout problem. On an empty first page, a bounded probe checks for data outside the interpreted header columns and reports a 400 when detected. This probe is not full-sheet layout validation: use positional ranges for irregular sheets, and validate expected record counts before publishing a sync.

Helpers use a 30-second HTTP timeout. Safe reads retry Google 429 and transient 5xx responses twice with short delays. Writes only retry an explicit 429 refusal; they do not automatically retry ambiguous failures. Exhausted quota responses use HTTP 429 with Retry-After: 60; upstream 5xx responses use HTTP 503.

A requests.HTTPError includes the backend's explanation and retains .response. A write timeout or transient failure does not prove whether the write landed. For appends, store a unique operation/reference ID in the row, search for it before retrying, and repeat verification after a short interval if necessary. Verification reduces duplicate risk but is not exactly-once delivery. Range writes also require verification when their outcome is unknown; concurrent edits can make a blind retry overwrite someone else's change.

REST endpoints

All require the organization API key in X-API-Key.

Existing row endpoints remain available. Append accepts the optional REST flag preserveHeaders: true.