Protect every formula in Google Sheets, free

Google Sheets has no "protect all formulas" button. This free script adds Google's own protection to every cell that holds a formula, in one click, and removes it again in one click.

1. Pick your settings

Every tab (untick = only the tab you are on)
Warning only (editors see "are you sure?" but can still edit). Untick = only you can edit formulas.

2. Copy the script

Copy script or open it as plain text

/**
 * Protect all formulas - free script from formulalock.com
 * Adds Google Sheets' own protection to every cell that holds a formula.
 * Paste into Extensions > Apps Script, save, reload the sheet, use the "Protect formulas" menu.
 * @OnlyCurrentDoc
 */
const ALL_TABS = true;      // true = every tab, false = only the tab you are on
const WARNING_ONLY = true;  // true = editors see a warning but can still edit; false = only you can edit
const TAG = 'Formula (auto-protected)';

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Protect formulas')
    .addItem('Protect all formula cells', 'protectAllFormulas')
    .addItem('Remove these protections', 'removeFormulaProtections')
    .addToUi();
}

function protectAllFormulas() {
  const ss = SpreadsheetApp.getActive();
  const sheets = ALL_TABS ? ss.getSheets() : [ss.getActiveSheet()];
  const me = Session.getEffectiveUser();
  let n = 0;
  sheets.forEach(function (sh) {
    const r = sh.getDataRange();
    const f = r.getFormulas();
    for (let i = 0; i < f.length; i++) {
      let j = 0;
      while (j < f[i].length) {
        if (!f[i][j]) { j++; continue; }
        let k = j;
        while (k + 1 < f[i].length && f[i][k + 1]) k++;
        const p = sh.getRange(r.getRow() + i, r.getColumn() + j, 1, k - j + 1).protect().setDescription(TAG);
        if (WARNING_ONLY) {
          p.setWarningOnly(true);
        } else {
          p.addEditor(me);
          p.removeEditors(p.getEditors().filter(function (e) { return e.getEmail() !== me.getEmail(); }));
          if (p.canDomainEdit()) p.setDomainEdit(false);
        }
        n++;
        j = k + 1;
      }
    }
  });
  SpreadsheetApp.getUi().alert('Protected ' + n + ' formula range(s). New formulas you add later are not covered: run this again.');
}

function removeFormulaProtections() {
  let n = 0;
  SpreadsheetApp.getActive().getSheets().forEach(function (sh) {
    sh.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(function (p) {
      if (p.getDescription() === TAG && p.canEdit()) { p.remove(); n++; }
    });
  });
  SpreadsheetApp.getUi().alert('Removed ' + n + ' protection(s).');
}

3. Run it

1. In your sheet: Extensions > Apps Script, delete what is there, paste, click save.
2. Reload the sheet. A Protect formulas menu appears. Click Protect all formula cells.
3. Google asks you to authorise it the first time (on "Google hasn't verified this app": Advanced > Go to project (unsafe) > Allow). You can read every line above first. It only touches this spreadsheet.

What it does not do

• Formulas you add later are not covered until you run it again.
• Warning-only protection is a speed bump: a collaborator who clicks OK still overwrites the formula, and nothing puts it back.
• "Only you" protection stops collaborators editing those cells at all, including cells next to inputs they need, and editors can still remove it in Data > Protect sheets and ranges if they own the file.
• It does not hide formulas. Anyone who can view the sheet can read them.

Want overwritten formulas put back automatically?

That is what FormulaLock does: collaborators can still type in your sheet, but if someone pastes over a locked formula it is restored in a second and logged. Free on one tab, $19 once for every tab. Honest limits.

Try FormulaLock free