Protect the sheet so people can’t wreck formulas (while the script still writes)

TL;DR — Protect a Sheets tab so editors can’t trash formulas, leave an unprotected input range for humans, and let the owner-run script keep writing — plus a soft NitroGAS note for protectSheetKeepInputRange.

Someone tabs into a formula column, types over =VLOOKUP(…), and tonight’s digest is garbage. You don’t need to lock people out of the workbook — you need the calc grid protected and a clear input range left open. The script, running as owner (or with edit rights the editors don’t have on protected ranges), still writes.

TL;DR

Protect the sheet (or formula ranges); warn or strictly block editors
Leave an unprotected input range (status column, intake block, checklist)
Script writes as the owner / with rights that bypass the protection editors hit
Document the open range in a header note so humans know where to type
Soft helper name: protectSheetKeepInputRange (NitroGAS catalog / Co-Pilot)

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 “just be careful” fails

Editors are not malicious — they’re fast. Autocomplete, paste-from-Excel, and “I only changed one cell” all collide with fragile formula columns. Sheet protection is the spreadsheet equivalent of a seatbelt: boring until the first near miss.

Apps Script running as you (owner, or installable trigger under your account) can still edit protected ranges. Editors with edit access to the file but not listed as editors on the protection cannot. That split is the whole pattern.

What not to do

Don’t protect the entire workbook and wonder why the intake form sheet rejects human edits
Don’t rely on “hide the formula columns” — hides are not permissions
Don’t run the writer as a random editor account that is also blocked by the protection
Don’t leave protection with “show warning only” if pastes keep nuking formulas — escalate to strict once the workflow is proven

Workflow

1. Decide the human input rectangle (e.g. A2:C last-row or a named Status column)
2. Protect the sheet (or protect everything except that range)
3. Confirm editors can edit the input range and cannot edit formula cells
4. Run / trigger the script as owner; verify setValue on protected cells still works
5. Add a one-line note: “Type only in columns A–C”

Protect sheet, keep an input range open

/**
 * Protect sheet; leave inputRangeA1 editable by editors.
 * Script owner can still write protected cells.
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
 * @param {string} inputRangeA1 e.g. 'A2:C'
 * @param {string=} description
 */
function protectSheetKeepInputRange(sheet, inputRangeA1, description) {
  // Remove prior protections on this sheet so re-runs don’t stack
  sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET).forEach(function (p) {
    p.remove();
  });

  var protection = sheet.protect().setDescription(description || 'Formulas locked; input range open');
  // Only owner (and anyone you explicitly add) may edit protected areas
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }

  var input = sheet.getRange(inputRangeA1);
  protection.setUnprotectedRanges([input]);
  return protection;
}

function setupOpsProtection() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Ops');
  protectSheetKeepInputRange(sheet, 'A2:C', 'Ops — edit A–C only');
}

Editors type in A2:C. Columns D:Z with formulas stay put. Your nightly job still does:

function writeDigestColumns_() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Ops');
  // Owner-run: allowed on protected cells
  sheet.getRange('D2:F').setValues(buildDigestRows_());
}

Range-level alternative

If only a few formula blocks matter, protect those ranges instead of the whole sheet — and skip unprotected-range bookkeeping. Whole-sheet + one open input block is usually clearer for operators (“type here”).

function protectFormulaBlock_(sheet, a1) {
  var p = sheet.getRange(a1).protect().setDescription('Formula block');
  p.removeEditors(p.getEditors());
  if (p.canDomainEdit()) p.setDomainEdit(false);
  return p;
}

Failure modes worth naming

Installable trigger under the wrong account — a trigger owned by an editor may hit “you can’t edit this protected cell.” Keep writers on the owner’s trigger.
Stacked protections — re-running setup without removing old protections leaves ghosts. Clear sheet protections first.
Named ranges that drift — if the input block grows, update setUnprotectedRanges (or protect by column design from day one).
Warning-only mode — fine for training week; switch to strict when paste accidents continue.
Filter views / pivots — protection doesn’t stop people from creating confusion elsewhere; still worth locking the formula grid.

Soft Co-Pilot note

NitroGAS can surface a protectSheetKeepInputRange-style helper from the catalog; Co-Pilot can draft the setup menu item from “protect Ops, leave A2:C open.” You still verify with a non-owner editor account. Free core; Co-Pilot optional when you want the draft.

Closing checklist

Input range documented for humans (note / header)
Editors cannot overwrite formula cells (tested as editor)
Owner/script can still setValue on protected cells
Trigger runs as the account that owns the protection
Re-run of setup doesn’t stack duplicate protections

Happy Coding!