A common first mistake when someone builds automations for a small business: the test run writes to the live sheet the owner uses every morning. Here is a small pattern that keeps tests away from real data, whether you build in-house or review testing work from an AI automation agency safely.
The idea
Every automation reads its target from one config value. In staging, that value points at a copy. A guard refuses to run destructive steps against the production ID unless an explicit flag is set.
import os
SHEETS = {
"prod": os.environ["PROD_SHEET_ID"],
"staging": os.environ["STAGING_SHEET_ID"],
}
ENV = os.environ.get("AUTOMATION_ENV", "staging")
SHEET_ID = SHEETS[ENV]
def guard_write(action: str):
if ENV == "prod" and os.environ.get("ALLOW_PROD_WRITES") != "yes":
raise RuntimeError(f"Refusing {action} on prod without ALLOW_PROD_WRITES=yes")
def append_row(client, row):
guard_write("append_row")
client.open_by_key(SHEET_ID).sheet1.append_row(row)
Refreshing the staging copy
Copy the production sheet on a schedule, then scrub columns you do not need for testing (phone numbers, emails) by replacing them with fake values.
def scrub(rows, cols=("phone", "email")):
header = rows[0]
idx = [header.index(c) for c in cols if c in header]
out = [header]
for i, r in enumerate(rows[1:], 1):
r = list(r)
for j in idx:
r[j] = f"test-{i}"
out.append(r)
return out
What to check before switching to prod
- Every write path has gone through
guard_write. - Running twice does not create duplicates.
- A failed run leaves a log line, not a half-written row.
We use a similar setup when we review builds; the broader thinking is in our post on when custom AI beats off-the-shelf bots.
Disclosure: I co-founded NxFlowAI, an automation agency.
Top comments (0)