Point your script at staging so you never write prod by accident

TL;DR — Use an Apps Script ACTIVE_SPREADSHEET_ID (or ENV) Script Property as a staging/prod pointer: one codebase, flip environments, refuse to run if the pointer is missing.

Dry Run previews writes. This guide is the other half of safety: which spreadsheet those writes would hit. Keep a Script Property pointer (ACTIVE_SPREADSHEET_ID or ENV=staging|prod) and open the sheet through that pointer so one codebase serves both environments.

TL;DR

Store ACTIVE_SPREADSHEET_ID (preferred) or ENV + a small map of ids.
Open with SpreadsheetApp.openById(requireScriptProperty('ACTIVE_SPREADSHEET_ID')) — never hard-code the prod id in source.
Refuse to run if the property is missing (fail loud).
Menu Use staging / Use prod that swaps the property and toasts the new title.
Different from Dry Run: Dry Run skips writes; the pointer chooses where live writes go.

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 a property, not a tab name?

Tab names drift. Bound scripts tempt getActiveSpreadsheet() against whichever file you happen to have open. A property makes the target explicit, survives “I duplicated the sheet to test,” and matches how you already store API keys.

function requireScriptProperty(key) {
  var v = PropertiesService.getScriptProperties().getProperty(key);
  if (v == null || String(v).trim() === '') {
    throw new Error('Missing Script Property: ' + key);
  }
  return v;
}

function openConfiguredSpreadsheet() {
  var id = requireScriptProperty('ACTIVE_SPREADSHEET_ID');
  return SpreadsheetApp.openById(id);
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Env')
    .addItem('Show active spreadsheet', 'toastActiveSpreadsheet')
    .addItem('Point at staging…', 'menuPointStaging')
    .addItem('Point at prod…', 'menuPointProd')
    .addToUi();
}

function toastActiveSpreadsheet() {
  var ss = openConfiguredSpreadsheet();
  SpreadsheetApp.getActive().toast(ss.getName() + ' (' + ss.getId() + ')', 'ACTIVE_SPREADSHEET_ID', 8);
}

Flipping environments

Keep staging and prod ids in a second property or a tiny config object — still not in the write path as literals scattered through files:

var ENV_SHEETS = {
  staging: 'STAGING_SPREADSHEET_ID_HERE',
  prod: 'PROD_SPREADSHEET_ID_HERE'
};

function setActiveEnv(env) {
  var id = ENV_SHEETS[env];
  if (!id) throw new Error('Unknown env: ' + env);
  PropertiesService.getScriptProperties().setProperty('ACTIVE_SPREADSHEET_ID', id);
  PropertiesService.getScriptProperties().setProperty('ENV', env);
  toastActiveSpreadsheet();
}

For stricter ops: don’t embed prod id in source at all — set properties once in each deployed project (staging project vs prod project) and omit the menu from prod.

Refuse to run if unset

function nightlySync() {
  var ss = openConfiguredSpreadsheet(); // throws if missing
  if (isDryRun()) {
    console.log('Dry run against', ss.getName());
    return;
  }
  // … writes only after pointer + dry-run gates …
}

Checklist

Staging sheet is a real copy (or thin replica), not a filter view on prod
ACTIVE_SPREADSHEET_ID set in Script Properties before first nightly
requireScriptProperty (or equivalent) at the top of every writer
Toast/menu so humans can see the target without opening Project Settings
Dry Run still on until staging looks right

Soft CTA

NitroGAS already has Script Properties helpers — drop requireScriptProperty in, then ask Co-Pilot to comment the “why” for whoever inherits the project. Happy Coding.