Importing Google Sheets Data Into a Database Reliably

A sheet is a convenient place for people to maintain a list, and a database is where the list has to end up. The path between them has more potholes than it looks: rows of unequal length, numbers that are really text, a date typed in three styles, a column header someone renamed on Friday afternoon. This article builds a loader that survives all of that. It is the load half of Google Sheets alongside databases, and it follows the same staging discipline as staging Excel data in a table first.

Write the contract first

Before any code, write down what a valid sheet looks like. For a supplier list:

Share it with the people who edit the sheet. Put the same rules into the sheet as data validation where you can: dropdowns for status and terms, a plain text format on the code column. The loader enforces the contract regardless, because validation in a sheet can be bypassed by pasting.

Read with unformatted values

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

creds = service_account.Credentials.from_service_account_file(
    "key.json", scopes=["https://www.googleapis.com/auth/spreadsheets.readonly"]
)
svc = build("sheets", "v4", credentials=creds, cache_discovery=False)

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

UNFORMATTED_VALUE returns 30 rather than "30 days" for a cell with a custom number format, and 1234.5 rather than "$1,234.50". SERIAL_NUMBER returns dates as day counts, which avoids the locale ambiguity of a formatted date. A text cell stays text, including a code like 00417, provided the column was text when typed.

The range A1:E with no end row reads to the last row with data. Use the account's narrowest scope, spreadsheets.readonly, and share the sheet with the service account as a viewer.

Pad the rows and check the header

The API trims trailing empty cells in each row, so a row with no status has four values, not five. Pad before you build a frame.

import pandas as pd

EXPECTED = ["code", "name", "country", "terms_days", "status"]

header, *rows = values
header = [str(h).strip().lower() for h in header]
if header != EXPECTED:
    raise SystemExit(f"Header mismatch. Expected {EXPECTED}, found {header}")

width = len(EXPECTED)
rows = [r + [None] * (width - len(r)) for r in rows]
df = pd.DataFrame(rows, columns=EXPECTED)
df.index = df.index + 2   # sheet row numbers: header is row 1

Failing the whole run on a header mismatch is deliberate. If someone inserted a column, guessing which one is which loads the wrong data under the wrong name. Index the frame so that error messages can quote the real row number in the sheet.

Rows that are entirely empty should be dropped before validation: df = df.dropna(how="all"). Blank cells arrive as None (missing) or as an empty string; normalize both: df = df.replace({"": None}).

Validate and collect every error

Check each rule and gather all failures, not just the first, so the owner can fix the sheet in one pass.

ISO = {"US", "GB", "DE", "FR", "AU", "JP"}   # in practice, load the full ISO 3166 list

errors = []
def bad(idx, col, why):
    errors.append({"row": int(idx), "column": col, "reason": why})

for idx, r in df.iterrows():
    code = r["code"]
    if code is None or not str(code).strip():
        bad(idx, "code", "required")
    elif not (3 <= len(str(code).strip()) <= 10):
        bad(idx, "code", "must be 3 to 10 characters")
    if r["name"] is None:
        bad(idx, "name", "required")
    if r["country"] not in ISO:
        bad(idx, "country", f"unknown country {r['country']!r}")
    if r["terms_days"] not in (0, 30, 45, 60):
        bad(idx, "terms_days", f"must be 0, 30, 45 or 60, got {r['terms_days']!r}")
    if r["status"] not in ("active", "inactive"):
        bad(idx, "status", "must be active or inactive")

dupes = df[df["code"].duplicated(keep=False) & df["code"].notna()]
for idx, r in dupes.iterrows():
    bad(idx, "code", f"duplicate code {r['code']}")

A code typed as a number arrives as an integer, and 00417 as a number is 417. If your codes have meaningful leading zeros and the sheet was not formatted as text, the information is already lost; the validation should require a text type (isinstance(code, str)) and report the offenders, so that the sheet is fixed at the source. Never "repair" by padding to a guessed width unless the width is part of the contract (keeping leading zeros in Excel describes the same trap).

Load through staging

If there are errors, load nothing and send the report. If there are none, load into a staging table and merge in one transaction.

import psycopg

with psycopg.connect("dbname=purchasing") as conn:
    with conn.cursor() as cur:
        cur.execute("CREATE TEMP TABLE stg_suppliers (LIKE suppliers INCLUDING DEFAULTS) ON COMMIT DROP")
        with cur.copy("COPY stg_suppliers (code, name, country, terms_days, status) FROM STDIN") as cp:
            for r in df.itertuples(index=False):
                cp.write_row([str(r.code).strip(), r.name, r.country, int(r.terms_days), r.status])
        cur.execute("""
            INSERT INTO suppliers AS s (code, name, country, terms_days, status)
            SELECT code, name, country, terms_days, status FROM stg_suppliers
            ON CONFLICT (code) DO UPDATE
              SET name = EXCLUDED.name, country = EXCLUDED.country,
                  terms_days = EXCLUDED.terms_days, status = EXCLUDED.status
        """)
        cur.execute("SELECT count(*) FROM stg_suppliers")
        loaded = cur.fetchone()[0]

The temp table exists for the transaction and then vanishes. COPY is far faster than row inserts for large sheets, and the upsert makes reruns safe. Decide what happens to suppliers that are in the database and no longer in the sheet: deleting them silently is dangerous; marking them inactive or reporting them for review is safer. The PostgreSQL INSERT ... ON CONFLICT reference covers the upsert syntax.

Report back to the owners

The rejection list is a product. Make it useful: a table of sheet row numbers, columns and reasons, sent to the sheet owners by email or written into a Load report tab, so they never need a developer to read a log.

report = pd.DataFrame(errors).sort_values(["row", "column"])
report.to_csv("rejections.csv", index=False)

For a weekly job, also write a one-line summary back to the sheet: "Last load 2026-10-29 02:00 UTC: 412 rows read, 0 rejected, 412 loaded." That cell tells the owners that the integration is alive, and its absence tells them to ask. The write needs a broader scope (spreadsheets), so use a separate account or a separate sheet for status messages if you want the read path to stay read-only.

Log, retry and leave data alone on failure

Record each run: start time, rows read, rows rejected, rows loaded, duration and the spreadsheet's revision if you can get it. On a transient API error (a 429 or a 5xx), retry with exponential backoff, and give up after a few attempts with an alert. On any validation or load failure, leave the target as it was and say so. A target half-updated from the sheet is a nightmare to reason about, which is why the merge sits in one transaction.

Schedule the job when edits are rare, and avoid the hours when the sheet's owners are working. If a person is mid-edit when the job reads, you may load a half-finished state. A "ready" checkbox cell that the owner ticks when the sheet is complete is a simple fix: the job loads only when it is ticked, then unticks it.

Testing

Keep a test sheet with the awkward rows: a short row, a blank row in the middle, a code with a leading zero as text, a number where text is expected, a duplicate, an unknown country. Run the loader against it in CI and assert the exact rejection list. A second test confirms that a clean sheet loads the expected counts, and that running it twice changes nothing. A third removes a column to confirm the header check stops the run. These three tests catch most regressions.

When a sheet import is the wrong design

A sheet import is the wrong design when the data changes by the minute, when several people must edit the same record at the same time or when the sheet has grown to tens of thousands of rows that the business treats as a system of record (signs an Excel file needs to become a database lists the symptoms, and they hold for Sheets). It is also wrong when no one can own the contract; an import that nobody maintains will fail on the first renamed column. In those cases build a small form or application over the table, and keep the sheet for analysis. The Sheets API values reference lists every render option used above.