DEV Community

Ujjwal Dubey
Ujjwal Dubey

Posted on

Test Agency-Built Automations Against a Staging Copy of Your Spreadsheet (Python)

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)