Named ranges as config knobs ops can edit

TL;DR — Give ops a few safe knobs (batch size, cutoff date, notify email) by naming single cells and reading them by name in Apps Script. Parse and validate each value, and fail loudly if a name gets deleted or a cell goes blank.

“Can you change the cutoff to the 25th this month?” If the answer means opening the script editor, you’ve made yourself the bottleneck for a one-cell change.

Most jobs have a handful of values ops genuinely should own: a batch size, a cutoff date, who gets the summary email, whether the job is paused. Put each one in a named cell, read it by name, and check it hard. Ops edit a cell they can see, and you stop getting pinged.

TL;DR

Name single cells (Data → Named ranges): cfg_BatchSize, cfg_CutoffDate, cfg_NotifyEmail
Read by name with ss.getRangeByName(), which survives inserted rows, moved cells and renamed tabs
Parse each value as the type you expect (number, date, email, yes/no) and validate it
Fail loudly if the name was deleted or the cell is blank/invalid; never fall back silently
Keep secrets out. Tokens still belong in Script Properties

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.

Where this fits

Picking which shelf a value belongs on (cache, properties, or a sheet) is its own decision, covered in CacheService vs Script Properties vs a Config sheet. This guide assumes you’ve decided “ops should edit this, and it’s a single value,” and shows how to make that cell safe to read from code.

For secrets and ids, keep using Script Properties for bootstrapper config.

Why named cells beat `Settings!B4`

getRange('Settings!B4') breaks the day someone inserts a row above it. The code keeps running and quietly reads the wrong knob. A named range follows its cell: insert rows, move the block, rename the tab, and cfg_BatchSize still points at the right place.

There’s one catch: named ranges can be deleted, and then getRangeByName just returns null. That’s why the reader below throws.

What not to do

Don’t || 50 a missing knob; a deleted name should stop the job, not run it with a default nobody chose
Don’t name whole columns or big blocks for single values; one cell per knob
Don’t put API keys in a named cell; anyone with edit access can read it
Don’t read knobs in ten places; read them once at the top of the run into one object

Workflow

1. Make a small "Settings" tab: label in column A, value in column B
2. Name each value cell with a cfg_ prefix (Data → Named ranges)
3. Add a note on each cell: what it does, allowed values
4. Read all knobs once at the start of the job with a strict reader
5. Protect the tab except column B, so labels and names don't get mangled

A strict reader

/**
 * Read a single named cell and validate it.
 * @param {string} name  e.g. 'cfg_BatchSize'
 * @param {'number'|'date'|'email'|'boolean'|'text'} type
 * @param {{min?: number, max?: number, allowBlank?: boolean}=} opts
 */
function readKnob_(name, type, opts) {
  opts = opts || {};
  var ss = SpreadsheetApp.getActive();
  var range = ss.getRangeByName(name);
  if (!range) {
    throw new Error('Config knob "' + name + '" is missing. Was the named range deleted? (Data → Named ranges)');
  }
  var v = range.getCell(1, 1).getValue();
  var where = name + ' (' + range.getSheet().getName() + '!' + range.getA1Notation() + ')';

  if (v === '' || v === null) {
    if (opts.allowBlank) return null;
    throw new Error('Config knob ' + where + ' is blank.');
  }

  switch (type) {
    case 'number':
      var n = Number(v);
      if (isNaN(n)) throw new Error(where + ' must be a number, got "' + v + '"');
      if (opts.min != null && n < opts.min) throw new Error(where + ' must be ≥ ' + opts.min);
      if (opts.max != null && n > opts.max) throw new Error(where + ' must be ≤ ' + opts.max);
      return n;
    case 'date':
      if (!(v instanceof Date)) throw new Error(where + ' must be a date cell, got "' + v + '"');
      return v;
    case 'email':
      var e = String(v).trim().toLowerCase();
      if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(e)) throw new Error(where + ' is not an email: "' + v + '"');
      return e;
    case 'boolean':
      // Checkboxes give true/false; typed cells often say Yes/No
      if (v === true || /^(yes|y|true|on)$/i.test(String(v))) return true;
      if (v === false || /^(no|n|false|off)$/i.test(String(v))) return false;
      throw new Error(where + ' must be Yes/No or a checkbox, got "' + v + '"');
    default:
      return String(v).trim();
  }
}

Read everything once

function loadConfig_() {
  return {
    batchSize: readKnob_('cfg_BatchSize', 'number', { min: 1, max: 500 }),
    cutoff: readKnob_('cfg_CutoffDate', 'date'),
    notify: readKnob_('cfg_NotifyEmail', 'email'),
    paused: readKnob_('cfg_Paused', 'boolean')
  };
}

function nightlyJob() {
  var cfg = loadConfig_(); // throws before any work if a knob is broken
  if (cfg.paused) {
    console.log('Paused via cfg_Paused');
    return;
  }
  runBatch_(cfg.batchSize, cfg.cutoff);
  sendSummary_(cfg.notify);
}

Reading up front means a broken knob fails before the job writes anything. The error names the knob and the cell, so whoever reads the failure email knows exactly what to fix.

Check the knobs on demand

Give ops a menu item that validates without running the job:

function checkSettings() {
  try {
    var cfg = loadConfig_();
    SpreadsheetApp.getActive().toast('Settings OK (batch ' + cfg.batchSize + ', notify ' + cfg.notify + ')', 'Settings', 6);
  } catch (err) {
    SpreadsheetApp.getUi().alert('Settings problem:\n\n' + err.message);
  }
}

Make the cells hard to break

Data validation on each value cell (number between 1 and 500, a date, a checkbox). Sheets stops most bad input before your script sees it.
Protect the Settings tab except the value column. Labels and named ranges survive.
Cell notes describing each knob (“Rows per run. Lower if the job times out.”).
A cfg_ prefix, so the Named ranges panel stays readable.

Failure modes worth naming

Named range deleted: the reader throws with the exact name. Recreate it in Data → Named ranges.
Name points at a block, not a cell: the reader uses the top-left cell. Fix the range so nobody gets confused.
Tab copied into a new file: named ranges may not come along, or may get renamed. Run checkSettings after any copy.
Date typed as text (25/10 in a US-locale sheet): the date check catches it. Add date validation to the cell.
Two people editing mid-run: you read once at the start, so the run uses one consistent set of values.

Soft Co-Pilot note

NitroGAS keeps config helpers a search away in the editor. Paste a job full of hard-coded numbers and ask Co-Pilot which ones should become cfg_ knobs, and to draft loadConfig_ for them. You still decide what ops get to change. Free core; Co-Pilot is a pass for when you want the draft.

Closing checklist

One named cell per knob, cfg_ prefix
Strict reader: missing name, blank, and wrong type all throw with the cell address
All knobs read once at the start of the run
Data validation + protection on the Settings tab
No secrets in named cells

Happy Coding!