Google Sheets [🔧Fixed]. Prevent users from deleting formulas

Hi, this is Max from CoolTables.

Ok, we fixed #REF if the formula was overwritten, and fixed broken data validations. Now let’s fix a situation when an important formula is deleted or replaced with another.

Case

You’ve made a formula, but someone deleted it. It may be annoying to restore your formula. You are not always able to protect a range with formulas, as protection will also forbid users from sorting range, adding new rows, hiding rows, etc.

Let’s create our formula protection with the help of a few formulas and app script code.

Alternative Setup

Copy my sample file:

https://docs.google.com/spreadsheets/d/10qPdlorltnAxveaPhaZDQhbpNheTcwFJMhpc6DJ_4eM

Adopt code and formulas to your needs.

Step 1/2. Formulas Map

Use the FormulasMap function to map all the formulas you need to preserve. Create another helper sheet for that.

=FORMULASMAP('My Sheet'!B3:E13)

FormulaMap is a named formula for Sheets. Find it here, or use this full definition:

=LET(r,'My Sheet'!B3:E13, chr,CHAR(987654), sn,iferror(SHEETNAME(r)&chr), _rangeCellA1,LAMBDA(c,ADDRESS(row(c),COLUMN(c),4)), formulaMap,MAP(r,LAMBDA(c,sn&_rangeCellA1(c)&chr&FORMULATEXT(c))), flat,FLATTEN(formulaMap), FILTER(split(flat,chr),not(isna(flat))))

↑ change range to yours and you’ve got it:

We need this formula map so our “smart” Apps Script code will read and parse this data. After you finish this step, you will have a sheet with a similar map:

1. Column A => Sheet Name
2. Column B => Cell
3. Column C => Formula Text

If you need to add more sheets, try VSTACK:

=VSTACK(FORMULASMAP('Sheet1'!B3:E13), FORMULASMAP('Sheet2'!B3:E13))

Important note: convert formulas map to values:

1. Copy range [Ctrl] + [C]
2. Paste as values [Ctrl] + [Shift] + [V]

After this step: you need to have a sheet with a formula map and remember its name. In my case, it is the sheet called: “_formulas-maps_”.

Step 2/2. Apps Script

Apps Script code with "onEdit" simple trigger. It will run each time the user edits a cell or cells.

function getRestoreFormulasConfig_() {
  const config = {
    sheet_formulas_map: '_formulas-maps_',        // CHANGE
    formula_check: '=FORMULASMAP(Sheet1!1:3)',   // CHANGE
    num_column_sheet: 1,
    num_column_range: 2,
    num_column_formula: 3,
  }
  return config;
}

/**
 * onEdit
 * 
 * Trigger event for Google Sheets
 * https://developers.google.com/apps-script/guides/triggers/events
 * 
 * @typedef {Object} OnEditEvent
 * @property {SpreadsheetApp.Range} range
 * @property {SpreadsheetApp.Spreadsheet} source
 * @property {*} value
 * @property {*} oldValue
 */

function onEdit(e) {
  // other functions
  restoreKilledFormulas_(e);
  // other functions
}

/**
 * onEdit
 * @param {OnEditEvent} e
 */
function restoreKilledFormulas_(e) {

  const sheet = e.range.getSheet();
  const sheetName = sheet.getName();
  const formulasMap = readFormulasMapForFormulaRestore_(e, sheetName);

  if (!formulasMap) { return; }

  const a1Notation = e.range.getA1Notation();
  const cellsCheck = convRangeA1ToCells_(a1Notation);
  
  let tasks = [], task;
  for (let i = 0; i < cellsCheck.length; i++) {
    task = getRestoreFormulaTask_(
      formulasMap, 
      cellsCheck[i], 
      sheet);
    if (task) {
      tasks.push(task);
    }
  }

  if (!tasks.length) {
    console.log('🧫range has no formulas to restore: "' + 
      sheetName + '" ' + a1Notation);
    return;
  }

  Browser.msgBox(
    "⚠️Warning", 
    "Some formulas are broken.\\n\\nDon't worry. The script will fix that",
    Browser.Buttons.OK);

  /**
   * @param {TaskSetFormula} task
   */
  function setFormula_(task) {
    task.range.setFormula(task.formula);
  }

  tasks.forEach(setFormula_);

}

/**
 * @typedef {Object} TaskSetFormula
 * @property {SpreadsheetApp.Range} range
 * @property {String} formula
 * @property {String} cellA1
 */

/**
 * @param {Object} formulasMap
 * @param {String} cellA1
 * @param {SpreadsheetApp.Sheet} sheet
 * 
 * @returns {TaskSetFormula}
 */
function getRestoreFormulaTask_(formulasMap, cellA1, sheet) {
  const formula = formulasMap[cellA1];
  if (!formula) {
    return null;
  }
  const range = sheet.getRange(cellA1);
  var originalFormula = range.getFormula();
  if (originalFormula !== formula) {
    /** @type TaskSetFormula */
    const task = {
      formula, range, cellA1
    }
    return task;
  }
  return null;
}

/**
 * @param {OnEditEvent} e
 * @param {String} sheetName
 * 
 * @returns {Object}
 */
function readFormulasMapForFormulaRestore_(e, sheetName) {
  const ss = e.source;
  const config = getRestoreFormulasConfig_()
  const dataFormulasSheet = ss.getSheetByName(config.sheet_formulas_map);
  const dataFormulas = dataFormulasSheet.getDataRange().getValues();

  let objectFormulasRestore = {}, node = {}, configSheetName, row;
  for (let i = 0; i < dataFormulas.length; i++) {
    row = dataFormulas[i]
    configSheetName = row[config.num_column_sheet - 1];
    node = objectFormulasRestore[configSheetName] || {};
    node[row[config.num_column_range - 1]] = 
      row[config.num_column_formula - 1];
    objectFormulasRestore[configSheetName] = node;
  }

  const formulasMap = objectFormulasRestore[sheetName]; 
  if (!formulasMap) {
    console.log('🧫do not check sheet ' + sheetName);
  }

  return formulasMap;
}


////////////////////////////////// Library Code //////////////////////////////
/**
 * @typedef {Object} Grid
 * @property {Number} startRowIndex
 * @property {Number} endRowIndex
 * @property {Number} startColumnIndex
 * @property {Number} endColumnIndex
 * @property {Number} [width] - Optional: The width of the grid.
 * @property {Number} [height] - Optional: The height of the grid.
 */
/**
 * @param {String} a1Notation
 * 
 * @returns {Array<String>}
 */
function convRangeA1ToCells_(a1Notation) {
  let result = [];
  const grid = convA1NotationToGridRange_(a1Notation);
  for (let i = grid.startRowIndex; i < grid.endRowIndex; i++) {
    for (let ii = grid.startColumnIndex; ii < grid.endColumnIndex; ii++) {
      result.push(convGridRangeToA1Notation_({
        startRowIndex: i,
        startColumnIndex: ii
      }));
    }
  }
  return result;
}
/**
 * Converting a1Notation to gridrange.
 * Ref: https://gist.github.com/tanaikech/95c7cd650837f33a564babcaf013cae0
 * @param {String} a1Notation
 * @param {String} [sheetName]
 * @returns {Grid} Gridrange
 */
function convA1NotationToGridRange_(a1Notation, sheetName = "") {
  const { col, row } = a1Notation
    .toUpperCase()
    .split("!")
    .map((f) => f.split(":"))
    .pop()
    .reduce(
      (o, g) => {
        var [r1, r2] = ["[A-Z]+", "[0-9]+"].map((h) => g.match(new RegExp(h)));
        o.col.push(r1 && columnLetterToIndex_(r1[0]));
        o.row.push(r2 && Number(r2[0]));
        return o;
      },
      { col: [], row: [] }
    );
  col.sort((a, b) => (a > b ? 1 : -1));
  row.sort((a, b) => (a > b ? 1 : -1));
  const [start, end] = col.map((e, i) => ({ col: e, row: row[i] }));
  const gridrange = {
    startRowIndex: start?.row && start.row - 1,
    endRowIndex: end?.row ? end.row : start.row,
    startColumnIndex: start && start.col,
    endColumnIndex: end ? end.col + 1 :start.col+1,
  };
  if (gridrange.startRowIndex === null) {
    gridrange.startRowIndex = 0;
    delete gridrange.endRowIndex;
  }
  if (gridrange.startColumnIndex === null) {
    gridrange.startColumnIndex = 0;
    delete gridrange.endColumnIndex;
  }
  if (sheetName !== "") {
    gridrange.sheetName = sheetName;
  }
  return gridrange;
}

/**
 * Converting gridrange to a1Notation.
 * Ref: https://gist.github.com/tanaikech/95c7cd650837f33a564babcaf013cae0
 * @param {Object} gridrange Gridrange of range.
 * @param {Boolean} isAbsolute - true to convert A1 to $A$1
 * @returns {String} A1Notation.
 */
function convGridRangeToA1Notation_(gridrange, isAbsolute = false) {
  if (!gridrange) {
    throw "gridrange is required parameter";
  }
  let { startRowIndex, endRowIndex, startColumnIndex, endColumnIndex } =
    gridrange;

  // start and end row indesex by default - 1 cell
  if (typeof endRowIndex === "undefined") {
    endRowIndex = startRowIndex + 1;
  }
  if (typeof endColumnIndex === "undefined") {
    endColumnIndex = startColumnIndex + 1;
  }

  var dollar = "";
  if (isAbsolute) dollar = "$";
  const start = {};
  const end = {};
  if (!(typeof startColumnIndex === "undefined")) {
    start.col = columnIndexToLetter_(startColumnIndex);
  } else if (isUndefined(startColumnIndex) && !isUndefined(endColumnIndex)) {
    start.col = "A";
  }
  if (!(typeof startRowIndex === "undefined")) {
    start.row = startRowIndex + 1;
  } else if (isUndefined(startRowIndex) && !isUndefined(endRowIndex)) {
    start.row = endRowIndex;
  }

  end.col = columnIndexToLetter_(endColumnIndex - 1);

  end.row = endRowIndex;
  start.col = dollar + start.col;
  start.row = dollar + start.row;
  end.col = dollar + end.col;
  end.row = dollar + end.row;

  const k = ["col", "row"];
  const st = k.map((e) => start[e]).join("");
  const en = k.map((e) => end[e]).join("");
  const a1Notation = st == en ? st : `${st}:${en}`;
  return a1Notation;
}

/**
 * Converting colum letter to column index. Start of column index is 0.
 * @param {String} letter Column letter.
 * @return {Number} Column index.
 */
function columnLetterToIndex_(letter = null) {
  letter = letter.toUpperCase();
  return [...letter].reduce(
    (c, e, i, a) =>
      (c += (e.charCodeAt(0) - 64) * Math.pow(26, a.length - i - 1)),
    -1
  );
}

/**
 * Converting colum index to column letter. Start of column index is 0.
 * Ref: https://stackoverflow.com/a/53678158
 * @param {Number} index Column index.
 * @return {String} Column letter.
 */
function columnIndexToLetter_(index = null) {
  var a;
  return (a = Math.floor(index / 26)) >= 0
    ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26))
    : "";
}

Copy the code above to your script editor, and change the config to yours. Note the “onEdit” function. It works automatically as a trigger when you edit your Spreadsheet.

Bonus Step. Update formula map

Of course, your formulas may change, you may need to add rows or columns, and the map of your formulas will be outdated.

Here is the function and simple “onOpen” trigger for you. Add this to your script, and it will update the formula map when you open your Spreadsheet:

function onOpen() {
  // other functions
  restoreFormulasMapForProtection_()
  // other functions
}


/**
 * Use with onOpen
 * 
 * Also may be used with onChange (installable trigger)
 * Also may be used with onEdit, to protect the data integrity
 */
function restoreFormulasMapForProtection_() {
  const ss = SpreadsheetApp.getActive();
  const config = getRestoreFormulasConfig_();
  const mapSheetName = config.sheet_formulas_map;
  const mapSheet = ss.getSheetByName(mapSheetName);
  mapSheet.getDataRange().clearContent();
  mapSheet.getRange('A1').setFormula(config.formula_check);
  SpreadsheetApp.flush();
  const range = mapSheet.getDataRange();
  range.copyTo(range, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

Other protecting strategies:

• use the installable “onChange” trigger to restore the formula map. This will ensure if new rows or columns are added, the formula structure will be updated. Drawback: you need to install the trigger for each Spreadsheet copy, which requires more work.
• add the “Restore Formula Map” option to the spreadsheet custom menu. This will allow Spreadsheet admins to dynamically update configs.
• add special markers to the formula map, like the number of columns expected in a particular Sheet. Adjust “onEdit” trigger to also check these markers, and update the formula map if needed.

Please write in the comments about your suggestions and protection strategies.