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.
CodePudding user response：
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