Using the Sheets API From Python to Read and Write Data

The Sheets API turns a spreadsheet into something a script can read and write with the same ease as a database table, with several important differences: values are loosely typed, calls are rate limited, and the sheet belongs to people who are editing it while you work. This article takes you from nothing to a working client, and then through the calls you will use most. It is the how-to behind Google Sheets alongside databases, and the reading side is applied end to end in importing Google Sheets data into a database.

Set up a service account

A script needs an identity that is not a person. In Google Cloud:

1. Create or choose a project and enable the Google Sheets API (and the Drive API if you will list or create files).

2. Create a service account. Note its email, which looks like [email protected].

3. Create a key for it (JSON) and store the file in a secret store, not in the repository. Where your platform supports it, prefer workload identity or short-lived credentials to a long-lived key.

4. Share the spreadsheet with the service account's email address, as viewer for reading or editor for writing.

That fourth step is the one people forget. A service account has no access to anything until a sheet is shared with it, and the error for a missing share is a 403 or a 404, not an obvious message. Give it the lowest role that does the job.

Create the client

from google.oauth2 import service_account
from googleapiclient.discovery import build

SCOPES_RO = ["https://www.googleapis.com/auth/spreadsheets.readonly"]
SCOPES_RW = ["https://www.googleapis.com/auth/spreadsheets"]

creds = service_account.Credentials.from_service_account_file("key.json", scopes=SCOPES_RO)
svc = build("sheets", "v4", credentials=creds, cache_discovery=False)
sheets = svc.spreadsheets()

cache_discovery=False avoids a harmless warning about file caching in recent versions of the library. Use the read-only scope for anything that only reads. If a bug in a reading job cannot write, you have limited what it can break.

For quick scripts, gspread wraps the same API with a friendlier surface:

import gspread

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

The raw client gives you control over batching and render options, which matters at scale.

Read a range

resp = sheets.values().get(
    spreadsheetId="SPREADSHEET_ID",
    range="Suppliers!A1:E",
    valueRenderOption="UNFORMATTED_VALUE",
    dateTimeRenderOption="SERIAL_NUMBER",
).execute()
rows = resp.get("values", [])

Ranges use A1 notation: Sheet!A1:E10, Sheet!A:A for a whole column, Sheet!A1:E for open ended. If the tab name has a space or punctuation, wrap it in single quotes: 'Q3 Suppliers'!A1:E. A tab renamed by a user breaks the range, so for important integrations look up tab names by reading the spreadsheet's metadata (sheets.get(spreadsheetId=..., fields="sheets.properties")) or protect tab names.

The render options matter. The default FORMATTED_VALUE gives you the display strings; UNFORMATTED_VALUE gives the underlying values; FORMULA gives the formulas as text. dateTimeRenderOption set to SERIAL_NUMBER returns dates as days since 1899-12-30 with fractions for time, which you convert in code; FORMATTED_STRING returns locale dependent text. The reference for values.get lists the enumerations.

Read several ranges in one call

Each API call counts against a quota, so reading ten ranges in ten calls is a poor use of it. batchGet reads many ranges in one request.

resp = sheets.values().batchGet(
    spreadsheetId="SPREADSHEET_ID",
    ranges=["Suppliers!A1:E", "Terms!A1:B", "Countries!A1:A"],
    valueRenderOption="UNFORMATTED_VALUE",
).execute()
suppliers, terms, countries = (v.get("values", []) for v in resp["valueRanges"])

The result keeps the order of the ranges you asked for. This is the single biggest improvement for scripts that touch many tabs.

Write a range

body = {"values": [["code", "name"], ["A100", "Acme"], ["B200", "Bolt Ltd"]]}
sheets.values().update(
    spreadsheetId="SPREADSHEET_ID",
    range="Output!A1",
    valueInputOption="RAW",
    body=body,
).execute()

You give the top left cell and the library fills a rectangle with the size of your list of lists. valueInputOption is the choice that matters most.

• RAW stores values exactly as given. A string "=1+1" is stored as text. A string "2026-03-04" stays text. A number stays a number.

• USER_ENTERED parses values as though a person typed them. "=1+1" becomes a formula, "2026-03-04" becomes a date, "1/2" may become a date or a fraction, depending on the sheet locale.

For machine written data, use RAW, then set cell formats separately if you need dates displayed. Use USER_ENTERED only when you want formulas to be created, and then write those formulas from code you control, never from user text, because a formula can reach out to other services (formula injection in Excel exports describes the attack, which applies to Sheets too).

To add rows after the existing data instead of overwriting, use append:

sheets.values().append(
    spreadsheetId="SPREADSHEET_ID", range="Log!A:D",
    valueInputOption="RAW", insertDataOption="INSERT_ROWS",
    body={"values": [[ts, user, action, detail]]},
).execute()

append finds the last row of the table in the range and writes below it. Two jobs appending at the same time are both handled correctly, which is useful for logs.

Write many ranges at once

batchUpdate on values sends several writes in one request.

sheets.values().batchUpdate(
    spreadsheetId="SPREADSHEET_ID",
    body={
        "valueInputOption": "RAW",
        "data": [
            {"range": "Summary!B2", "values": [[148]]},
            {"range": "Summary!B3", "values": [["2026-10-29 02:00 UTC"]]},
            {"range": "Detail!A1", "values": detail_rows},
        ],
    },
).execute()

Separate from values, the spreadsheet-level batchUpdate changes structure and formatting: adding tabs, setting column widths, applying number formats, protecting ranges, adding data validation. It takes a list of request objects.

sheets.batchUpdate(spreadsheetId="SPREADSHEET_ID", body={"requests": [
    {"repeatCell": {
        "range": {"sheetId": 0, "startRowIndex": 1, "startColumnIndex": 3, "endColumnIndex": 4},
        "cell": {"userEnteredFormat": {"numberFormat": {"type": "CURRENCY", "pattern": "#,##0.00"}}},
        "fields": "userEnteredFormat.numberFormat",
    }},
]}).execute()

The fields mask tells the API which properties to change, which prevents accidentally resetting others. These requests use the numeric sheetId of a tab, not its name.

Handle errors and quotas

The API has per-minute quotas per project and per user, listed on the usage limits page. A job that exceeds them gets a 429 response. Retry with exponential backoff and a cap.

import random, time
from googleapiclient.errors import HttpError

def call(req, tries=6):
    for n in range(tries):
        try:
            return req.execute()
        except HttpError as e:
            status = e.resp.status
            if status in (429, 500, 502, 503) and n < tries - 1:
                time.sleep(min(60, 2 ** n) + random.random())
                continue
            raise

Other responses mean something specific:

Translate these into messages a person can act on, naming the sheet and the likely fix. And do not retry a 403 or a 400, since they will not get better.

Keep credentials safe

The key file is a password. Keep it out of the repository, restrict who can read it on the machine, rotate it on a schedule and when people leave. Use a separate service account per integration so revoking one does not break the rest. Never print the key or the token in logs. Consider the sheet itself part of the attack surface: anyone who can edit it can influence what your job writes elsewhere, so validate everything you read (importing Google Sheets data into a database shows a contract based loader).

Pagination, size and speed

For large sheets, avoid reading far more than you need. Ask for the columns you need, not the entire grid. If the sheet has hundreds of thousands of cells, consider whether the sheet should be that large at all. Reads of tens of thousands of rows work in one call, but the response is a large JSON document, and parsing it takes memory. For repeated work, cache what rarely changes and check the file's modification time through the Drive API before re-reading.

When the Sheets API is the wrong interface

The API is the wrong interface for large, frequent or transactional data. It is built for people-sized spreadsheets, and it enforces quotas that a busy system will hit. If thousands of rows change each minute or several processes must update the same records safely, use a database. For a one-off export, converting Google Sheets to CSV or Excel with a script is simpler. And if the integration is fragile because users keep changing the layout, fix that with a template and validation rather than more defensive code. The gspread documentation is the best guide for quick scripts that do not need batching.