Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formula

Disable Google Sheets formula

Is there a way to disable all code located in every cell of a sheet by using a function in Google App Script?

For example in A1 I have:

=query(d10Thur0721!B2:I1275, "select B, C, D, E where D > 0", 0)

In H2 I have:

=if($G$9="", "", "men:"&roundup(sum(countif($G$9:$G$551, "*m*")-countif(G9:G600, "*mef*"))/countif($G$9:$G$551, "**")*100))&"%"

In theory I'd like to just replace the = with '= thus disabling all the code however I'm not sure how to get the script grab the actual code in the cell. If I reference the cell A1 Google Apps Script will return whatever value is in the cell and not the actual code residing in A1.

Solution

  • To get the cell formula use SpreadsheetApp.Range.getFormula(), to get the formulas of all cells in a range use SpreadsheetApp.Range.getFormulas().

    To set the cell formula use SpreadsheetApp.Range.setFormula(formula) where formula is a string, to get the formulas of all cells in a range use SpreadsheetApp.Range.setFormulas(formulas) where formulas is string[][] (an 2D Array, outer Array elements are Arrays of strings).

    To set the cell values use SpreadsheetApp.Range.setValue(value) where value is a string, to set the values of all cells in a range use SpreadsheetApp.Range.setValues(values) where values is string[][] (an 2D Array, outer Array elements are Arrays of strings).

    Below is a "creative" script. It disable the formulas in the active range keeping the values of cells not having formulas.

    Instead of using getFormula and getFormulas / setFormula and setFormulas, it only use getValues and getFormulas and instead of setFormula/ setFormulas it uses setValues(values)

    /**
 * Disable formulas in the active range. 
 * https://stackoverflow.com/a/74329523/1595451
 *
 * @author Rubén https://stackoverflow.com/users/1595451/rubén
 */
function disableFormulas(){
  const range = SpreadsheetApp.getActiveRange();
  const formulas = range.getFormulas();
  const values = range.getValues();
  range.setValues(formulas
    .map((rowFormulas, row) => rowFormulas
      .map((formula, column) => formula
         ? formula.replace(/^=/,`'=`)
         : values[row][column];
      )
    )
  )
}

    Reference