Add a Dry Run switch before you write

TL;DR — Add an Apps Script Dry Run switch with Script Properties: preview counts and sample rows, skip writes, then flip the flag when the import looks right.

The scariest line in a bootstrapper is the first setValues against a live client tab. A Dry Run switch lets you rehearse the import: same fetch, same mapping, same counts — zero writes until you flip a Script Property. Here's a pattern we drop into almost every non-trivial sync.

TL;DR

Store DRY_RUN=true|false in Script Properties (not a cell the client can bump by accident).
Run the full pipeline: fetch → map → validate → preview.
When dry: log counts + sample rows; skip sheet/Drive/email side effects.
When live: same code path with writes enabled — no second "real" function that drifts.
Confirm destructive menu actions separately (confirmDangerousAction); Dry Run is for bulk pipelines.

Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.

Why not a checkbox on the sheet?

Sheet cells are visible, editable, and easy to leave wrong after a demo. Script Properties are project-scoped, survive sheet renames, and match how you already store API keys and watermarks. A menu item Toggle Dry Run that toasts the new value is enough UX for bootstrappers.

function isDryRun() {
  var v = PropertiesService.getScriptProperties().getProperty('DRY_RUN');
  if (v == null || v === '') return true; // safe default: dry until set
  return String(v).toLowerCase() === 'true' || v === '1';
}

function setDryRun(enabled) {
  PropertiesService.getScriptProperties().setProperty(
    'DRY_RUN',
    enabled ? 'true' : 'false'
  );
}

function menuToggleDryRun() {
  var next = !isDryRun();
  setDryRun(next);
  SpreadsheetApp.getActiveSpreadsheet().toast(
    'DRY_RUN is now ' + next,
    'Bootstrap',
    5
  );
}

Defaulting to dry (true when unset) means a fresh copy of the project won't write until someone intentionally goes live.

What should a dry run actually do?

Mirror production up to the side effects:

Step	Dry run	Live
UrlFetch / read sources	Yes	Yes
Parse + map columns	Yes	Yes
Validate required fields	Yes	Yes
`setValues` / `appendRow`	No	Yes
Drive file create	No	Yes
Email / chat notify	No	Yes (or still off)
Advance watermark	No (usually)	Yes

Skipping the watermark on dry runs matters: otherwise a rehearsal burns the cursor and the live run sees "nothing new."

How do you structure the write gate?

One helper beats sprinkling if (dry) across twenty call sites:

function maybeWrite(label, writeFn, previewFn) {
  if (isDryRun()) {
    if (previewFn) previewFn();
    Logger.log('[DRY_RUN] skipped write: ' + label);
    return { wrote: false, dryRun: true };
  }
  var result = writeFn();
  return { wrote: true, dryRun: false, result: result };
}

function syncLeads() {
  var rows = fetchLeadsFromApi(); // always
  var mapped = mapLeads(rows);    // always
  var invalid = mapped.filter(function (r) { return !r.email; });
  if (invalid.length) {
    throw new Error('Missing email on ' + invalid.length + ' rows');
  }

  maybeWrite(
    'Leads!' + mapped.length,
    function () {
      upsertRows_(mapped);
      bumpWatermark_(rows);
    },
    function () {
      var sample = mapped.slice(0, 5).map(function (r) {
        return r.email + ' / ' + r.status;
      });
      SpreadsheetApp.getActiveSpreadsheet().toast(
        mapped.length + ' rows ready. Sample: ' + sample.join('; '),
        'Dry run',
        12
      );
      logSheet_('DRY_RUN', mapped.length + ' leads', sample.join('\n'));
    }
  );
}

Keep upsertRows_ dumb and pure-ish; let the gate own policy.

How do you preview without flooding the UI?

Toasts are great for counts. For detail, append a few lines to a Log tab (or Logger.log during development):

function logSheet_(level, message, detail) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var log = ss.getSheetByName('Log') || ss.insertSheet('Log');
  if (log.getLastRow() < 1) {
    log.appendRow(['at', 'level', 'message', 'detail']);
    log.setFrozenRows(1);
  }
  log.appendRow([new Date(), level, message, detail || '']);
}

Cap samples at 5–10 rows. Nobody wants a dry run that pastes 8,000 emails into a toast.

When do you still need a Yes/No confirm?

Dry Run protects pipelines. Menu items like Clear data or Full re-import need a human gate even when dry-run is false:

function menuFullReimport() {
  if (!confirmDangerousAction(
    'Full re-import',
    'Clear the Leads tab (keep headers) and import everything from page 1?'
  )) return;

  // optional: force dry first on brand-new clients
  syncLeads();
}

Use both: Dry Run while wiring mapping; confirm when the action is irreversible.

How do you go live safely?

Run once with DRY_RUN=true — check counts vs API / source of truth.
Spot-check sample emails, IDs, dates (timezones love to lie).
Toggle DRY_RUN=false via menu; toast confirms.
Run once; verify row counts + watermark.
Leave the toggle in the menu for the next schema change.

If a client will operate the switch, label the menu Dry run: ON/OFF by reading isDryRun() when building the menu so state is visible without opening Properties.

Minimal test plan

Unset DRY_RUN → behaves dry (safe default).
Dry run against a fixture payload → Log shows counts; sheet row count unchanged.
Live run → rows appear; watermark advances.
Toggle back to dry → live data untouched on next sync.
Confirm Clear menu still prompts Yes/No regardless of dry flag.

Soft Co-Pilot note

Wiring the same gate across imports is tedious in a hurry — NitroGAS keeps Script Property helpers, confirmDangerousAction, and upsert snippets handy, and Co-Pilot can adapt the preview toast to the client's column names. Free extension; Co-Pilot optional. The Dry Run pattern above stands alone.

Closing checklist

DRY_RUN in Script Properties; unset means dry
One write gate used by all side effects
Watermark does not advance on dry runs
Preview = counts + small sample (toast and/or Log)
Destructive menus still use Yes/No confirm
Menu shows or toasts current dry/live state

Happy Coding!