Publishing Database Results to a Sheet Automatically

Managers like to see numbers in a sheet. They can filter, add a column, paste into a slide and share a link, all without opening a BI tool. If the numbers come from a database, someone has to put them there, and the manual way involves a weekly export and a paste that eventually gets skipped. Automating it is a small job that pays for itself in the first month, provided you do it so that the sheet is never wrong and never empty. This article shows how, with a schedule, a safe replace, a timestamp and a failure path. It is the publishing half of Google Sheets alongside databases, and it follows the same checks as building Excel reports from SQL queries people trust.

Design the sheet before the job

Give the job its own tab, named for what it holds, such as Orders (generated). People will want to add notes, formulas and charts next to the data. Put those in other tabs that read from the generated one, so that the job can replace its tab wholesale without trampling anything.

Decide the layout and keep it stable:

• Row 1 holds headers, written by the job.

• Data begins in row 2, column A.

• A status cell sits somewhere visible, for example Meta!B1, holding the last update time.

• No merged cells, and no formatting that the job does not set.

Tell the readers in the tab itself: a note in the header cell or a frozen first row saying "Generated hourly from the orders database. Do not edit; changes are overwritten." Then protect the tab, with only the service account allowed to edit. In the Sheets UI that is Data, Protect sheets and ranges; through the API it is an addProtectedRange request. A protected generated tab prevents the most common support question, "I fixed a number and it went back".

Run the query with a safe role

The job connects to the database with a role that has SELECT on the needed tables and nothing else, ideally on a replica.

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg://[email protected]/shop")
with engine.connect() as conn:
    df = pd.read_sql_query(text(open("sql/open_orders.sql").read()), conn)

Keep the SQL in a file in version control (version controlling the query behind an Excel report). Return only the columns that the sheet's readers need. Do not publish identifiers, emails or notes that a wide audience should not see, since a shared sheet usually has a wider audience than the database does.

Check before you write

The riskiest moment is the one where a bad result overwrites a good one. Add checks between the query and the write.

if df.empty:
    raise SystemExit("query returned no rows; keeping existing data")
if len(df) < 0.5 * previous_count:
    raise SystemExit(f"row count fell from {previous_count} to {len(df)}; keeping existing data")
if df["order_id"].duplicated().any():
    raise SystemExit("duplicate order ids")

previous_count can be read from the status tab or from a small table where the job logs its runs. The thresholds are judgment calls, and they exist to stop the common disasters: an empty result from a broken filter, or a half-loaded table. When a check fails, alert a person and leave the sheet alone. A slightly stale sheet is better than a wrong one.

Convert to cells safely

The Sheets API takes a list of lists of JSON values. Turn missing values into empty strings, dates into ISO strings and decimals into floats or strings.

def cell(v):
    if v is None or (isinstance(v, float) and v != v):
        return ""
    if hasattr(v, "isoformat"):
        return v.isoformat(sep=" ")[:19]
    return float(v) if isinstance(v, __import__("decimal").Decimal) else v

values = [list(df.columns)] + [[cell(v) for v in row] for row in df.itertuples(index=False)]

Write with valueInputOption="RAW" so that nothing is parsed. Dates written as ISO strings stay as text under RAW; if you want real date cells for filtering and sorting, write the serial number and set a date number format on the column with a repeatCell request, or use USER_ENTERED for a column of dates only, writing that column from values you generated. Text coming from the database goes in as RAW always (formula injection in Excel exports explains why).

Replace the range in one step

Clearing the tab and then writing leaves a window where the sheet is empty, and if the write fails, it stays empty. Prefer to write the new data first and clear only the leftover rows.

rows, cols = len(values), len(values[0])
end_col = chr(ord("A") + cols - 1)
rng = f"'Orders (generated)'!A1:{end_col}{rows}"

sheets.values().update(spreadsheetId=SID, range=rng, valueInputOption="RAW",
                       body={"values": values}).execute()

# remove rows left over from a longer previous run
sheets.values().clear(spreadsheetId=SID,
                      range=f"'Orders (generated)'!A{rows + 1}:{end_col}").execute()

The update replaces the whole block in a single request, so readers see either the old data or the new data. Clearing below it removes stale rows if the result shrank. For more than 26 columns the letter arithmetic needs a proper column-letter function; use gspread.utils.rowcol_to_a1 or write a small helper.

If readers use formulas pointing at fixed ranges, keep the header and column order identical between runs. A column added in the middle shifts every reference to its right.

Stamp the update and the counts

After a successful write, update the status cell.

from datetime import datetime, timezone

now = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M UTC")
sheets.values().update(
    spreadsheetId=SID, range="Meta!A1:B3", valueInputOption="RAW",
    body={"values": [["Last updated", now], ["Rows", len(df)], ["Source", "orders database, open_orders.sql"]]},
).execute()

Make that timestamp visible on the report itself, with a formula such as =Meta!B1 in a header, or conditional formatting that turns it red if it is more than a day old: =NOW() - DATEVALUE(LEFT(Meta!B1, 10)) > 1. A reader who sees "Last updated 2026-10-21" knows not to trust it, and a team that sees a red banner starts asking why before the quarter closes.

Schedule it

Run the job from whatever scheduler you already trust: cron, a CI pipeline on a schedule, a workflow service. Stagger it to avoid the busy hour on the database and the top of the hour for the API. Choose a frequency that matches how fast the readers need the data; every hour is almost always enough, and every minute is an invitation to quota errors and a sheet that flickers as people read it.

# every hour at minute 7
7 * * * * /opt/jobs/publish_orders.sh >> /var/log/jobs/publish_orders.log 2>&1

Log each run: start, duration, rows, outcome. Alert on failure through email or chat, and alert once, not every hour, or the alerts get muted. Include a heartbeat check on the receiving side: an independent job that reads the timestamp cell and complains if it is older than twice the schedule, which catches the case where the scheduler itself died.

Handle large results

Sheets is not a place for very large results. The API handles tens of thousands of rows in one request, though a response of several megabytes is slow to render for readers, and the spreadsheet has a total cell limit. If the result is big, aggregate in SQL and publish the aggregate, split the output over tabs by month or region, or publish a CSV elsewhere and link it. Think about what a person can do with 200,000 rows in a sheet: usually filter to a handful. A parameterized view in a BI tool or a download link may serve better.

Permissions and exposure

The sheet's sharing is the access control. Share the generated sheet with named people or a group, not "anyone with the link", and check the list periodically. The service account needs edit access to write. Avoid giving the account ownership of the file; the owner should be a person or a shared drive. Keep the key in a secret store, rotate it and use one account per publishing job.

Test the job

Test against a copy of the sheet in a test project. Check that a normal run produces the expected rows and timestamp. Check that an empty result is refused. Check that a row count drop of more than half is refused. Check that a shrinking result clears the extra rows. Run it twice in a row and confirm nothing changes except the timestamp. Then revoke the account's access and confirm the job fails with a clear message and leaves the sheet alone.

When publishing to a sheet is the wrong delivery

Publishing to a sheet is wrong for sensitive data without strong sharing controls, for very large results and for anything where readers need to write back: edits to a generated tab are lost. If people need an interactive view, a BI dashboard with row-level permissions is the better home. If a system needs the data, give it an API or a file drop, not a sheet. And if the real goal is for finance to get a formatted file by email, send a workbook (emailing a scheduled Excel report from a database). The Sheets API values.update reference documents the write call used here, and the protected ranges guide shows how to request protection.