How to deactivate formulas in Google Sheets using Apps Script?

Other Questions Google Sheets
1

I’m trying to find a way to turn off all formulas in every cell of a Google Sheet using Apps Script. Right now, I’ve got complex formulas in different cells. For example:

A1: =FILTER(SheetX!A2:D100, SheetX!C2:C100 > 0)
F3: =IF(E8="", "", "Total:"&ROUND(COUNTIF(E8:E200, "*x*")/COUNTA(E8:E200)*100, 2)&"%")

I know I could manually add a single quote before each equals sign to disable the formulas, but I want to do this automatically with a script. The problem is, when I try to get the cell content with Apps Script, it gives me the calculated result, not the actual formula text.

Is there a method to access and modify the raw formula text in cells using Apps Script? Any help would be appreciated!

2

hey there, i’ve got a quick tip for ya. instead of messin with the formulas directly, try using the getFormulas() method and then setValues() to replace em. somethin like this:

function turnOffFormulas() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var formulas = range.getFormulas();
range.setValues(formulas);
}

this’ll basicaly convert all ur formulas to their text equivalents. hope it helps!

3

I’ve actually faced a similar challenge before, and I found a workaround that might help you. Instead of trying to access the formula directly, you can use the getFormula() method on each cell. Here’s a script I’ve used successfully:

function deactivateFormulas() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var formulas = range.getFormulas();
  
  for (var i = 0; i < formulas.length; i++) {
    for (var j = 0; j < formulas[i].length; j++) {
      if (formulas[i][j] !== '') {
        sheet.getRange(i+1, j+1).setValue("'" + formulas[i][j]);
      }
    }
  }
}

This script iterates through all cells, checks if there’s a formula, and if so, it prepends a single quote to deactivate it. It’s worked well for me on sheets with complex formulas. Just be aware that this will change all your formulas to text, so make sure you have a backup of your original sheet before running it.

4

I’ve encountered this issue before, and there’s a reliable method to accomplish what you’re after. The key is using the getFormulas() method, which retrieves the actual formula text rather than the calculated results. Here’s a concise script that should do the trick:

function disableAllFormulas() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var formulas = range.getFormulas();
  var values = range.getValues();

  for (var i = 0; i < formulas.length; i++) {
    for (var j = 0; j < formulas[i].length; j++) {
      if (formulas[i][j] !== '') {
        values[i][j] = "'" + formulas[i][j];
      }
    }
  }

  range.setValues(values);
}

This script efficiently processes all cells, prepending a single quote to any formula it finds. It’s a straightforward solution, but remember to back up your data before running it, as the formulas will be converted to text.