I am trying to clear the data in my sheet but preserve the formulas and functions and my script is clearing everything

Question

Here is my script:

    function clearRowPreserveFormulas() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var range = sheet.getRange("B1:n32");
    var formulaB1 = sheet.getRange("B1").getFormula();
    var formulan32 = sheet.getRange("n32").getFormula();

    // Clear the data in the specified range
    range.clearContent();

    // Set the formulas back in cells B5 and C5
    sheet.getRange("B1:n32").setFormula(formulaB1n32);
    //sheet.getRange("n32").setFormula(formulan32);

I would like to clear data only, but it is clearing the formulas too. I am new to Google scripts and I admit I could be way off base with this, I did find it on the web though, maybe even on here. I thank you in advance for any assistance you can provide.

I expected to clear the data on my sheet and preserve the formulas. It cleared everything, formulas included. I ran the code through the Extensions/Macros/clearformulaspreserveformulas.

Answer 1

Clear cells that don't have formulas:

function clearRowPreserveFormulas() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const rg = sh.getRange("B1:N32");
  const row = rg.getRow();
  const col = rg.getColumn();
  let fs = rg.getFormulas();
  fs.forEach((r, i) => {
    r.forEach((c, j) => {
      if (c == null) {
        sh.getRange(i + row, j + col).clearContent();
      }
    })
  })
}

Answer 2

You could simply.

// assuming row 1 is headers
let range = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn();
let formulas = range.getFormulas();
range.setValues(formulas);

Any cell that has a value will be blank. But formulas will be retained.