Duplicate the spreadsheet for a throwaway dry-run of tonight’s job

TL;DR — File → Make a copy, point the run at the copy via spreadsheet id / Script Property, compare results, then delete — a full-file sandbox when formulas and sibling tabs matter (different from a Dry Run boolean or a staging-tab pointer).

Tonight’s job rewrites three tabs, refreshes pivot inputs, and emails a digest. A DRY_RUN = true flag skips the email — but it still mutates this workbook’s formulas and named ranges. When the spreadsheet is the system, the safest rehearsal is a throwaway copy.

TL;DR

File → Make a copy (or DriveApp / File.makeCopy)
Point the script at the copy (Spreadsheet id in a Script Property)
Run the job against the copy
Compare copy vs production; delete the copy when done
This is a full-file sandbox — not the same as a Dry Run boolean or a staging-tab pointer

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.

How this differs from other “don’t touch prod” patterns

You’ve probably seen (or queued) siblings:

Pattern	What it protects	When it falls short
Dry Run boolean	External side effects (email, UrlFetch writes)	Still writes cells / recalcs in the live file
Staging tab / Script Property pointer	One inbound stream before Master	Other tabs, formulas, and cross-sheet refs still live
Duplicate spreadsheet	Whole workbook: formulas, pivots, appscript-bound menus	Heavier; you must aim the run at the copy

Use the copy when formula graphs, Apps Script containers, or “every tab matters” make in-place dry runs dishonest.

What not to do

Don’t dry-run in production and “undo” with version history as the plan
Don’t hard-code the production id next to a comment that says // change me
Don’t leave orphaned copies named Copy of Ops forever
Don’t assume a bound script on the production file automatically runs against the copy — open by id

Workflow

1. Make a copy → note its spreadsheet id
2. Set Script Property dryRunSpreadsheetId = <copy id>
3. Runner opens that id (fallback: active / production id)
4. Execute tonight’s job
5. Diff key tabs / spot-check formulas
6. Clear the property; trash the copy

Point the run at the copy

var PROP_DRY_ID = 'dryRunSpreadsheetId';
var PROP_PROD_ID = 'productionSpreadsheetId'; // optional explicit prod

function getTargetSpreadsheet_() {
  var props = PropertiesService.getScriptProperties();
  var dryId = props.getProperty(PROP_DRY_ID);
  if (dryId) {
    console.log('Using DRY-RUN copy', dryId);
    return SpreadsheetApp.openById(dryId);
  }
  var prodId = props.getProperty(PROP_PROD_ID);
  if (prodId) return SpreadsheetApp.openById(prodId);
  return SpreadsheetApp.getActiveSpreadsheet();
}

function runTonightJob() {
  var ss = getTargetSpreadsheet_();
  rebuildOpsTabs_(ss);
  refreshDigestInputs_(ss);
  // Only send email when not pointed at a copy — or always send to yourself from dry-run
  if (!PropertiesService.getScriptProperties().getProperty(PROP_DRY_ID)) {
    sendDigest_(ss);
  } else {
    console.log('dry-run: skipped external email');
  }
}

Bound scripts can still openById another spreadsheet if scopes allow. If your authorize screen is already scary, prefer keeping the dry-run copy in the same Drive folder and documenting the Drive/Sheets scope you need — or duplicate the entire Apps Script project with the copy when you want zero chance of touching prod.

Make the copy from a menu (optional)

function makeThrowawayCopy() {
  var ui = SpreadsheetApp.getUi();
  var file = DriveApp.getFileById(SpreadsheetApp.getActive().getId());
  var stamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd-HHmm');
  var copy = file.makeCopy('DRYRUN ' + stamp + ' — ' + file.getName());
  PropertiesService.getScriptProperties().setProperty(PROP_DRY_ID, copy.getId());
  ui.alert('Dry-run copy ready:\n' + copy.getUrl() + '\n\nProperty ' + PROP_DRY_ID + ' set. Run the job, then Clear dry-run target.');
}

function clearDryRunTarget() {
  PropertiesService.getScriptProperties().deleteProperty(PROP_DRY_ID);
  SpreadsheetApp.getUi().alert('dryRunSpreadsheetId cleared — runs will use production/active.');
}

Name copies with DRYRUN + timestamp so last week’s rehearsal doesn’t look like the real Ops file in search.

Compare, then delete

Spot-check the tabs the job owns; don’t re-audit the whole company wiki
If formulas diverge, fix the script against the copy first
Trash the copy when the property is cleared — Drive junk is how “which Ops is real?” starts

function trashDryRunCopy() {
  var props = PropertiesService.getScriptProperties();
  var dryId = props.getProperty(PROP_DRY_ID);
  if (!dryId) {
    SpreadsheetApp.getUi().alert('No dryRunSpreadsheetId set');
    return;
  }
  DriveApp.getFileById(dryId).setTrashed(true);
  props.deleteProperty(PROP_DRY_ID);
  SpreadsheetApp.getUi().alert('Copy trashed and property cleared.');
}

Failure modes worth naming

Forgot to clear dryRunSpreadsheetId → tomorrow’s trigger still hits the copy (or a trashed id). Clear in the same sitting; add a startup log line that prints which id you opened.
Copy without the script — “Make a copy” of a Sheet includes the bound project; copying only tabs via sheet.copyTo does not. Know which you did.
Installable triggers stay on the production project — they won’t magically fire on the copy unless you set them up there. For throwaway dry-runs, manual Run is usually enough.
External systems (Webhooks, Forms linked to the original) still point at prod — the copy sandboxes sheet side effects, not the whole company.

Soft Co-Pilot note

NitroGAS can pull helpers like Script Property get/set; Co-Pilot can sketch the getTargetSpreadsheet_ switch from a sentence like “run against copy id in properties” — you still make the copy, compare, and trash. Free core; Co-Pilot optional when you want the draft.

Closing checklist

Copy named DRYRUN … with a timestamp
dryRunSpreadsheetId set before the run
Job opens by id (logs which spreadsheet)
External sends skipped or redirected on dry-run
Property cleared + copy trashed after compare

Happy Coding!