Find all cells with escaped out formulas '=

I have a sheet with a lot of formulas that have been commented or escaped out (not sure which term, if either is accurate) with '=. I’m now trying to locate and remove the ' without any luck.

I can figure out the re-writing of the formula, it’s just the locating of '= where I’m getting hung up. I’m not really sure what the ... == 0) portion of these if statements mean, so that’s my first problem. But I’m sure there is probably an issue with my keyword as well.

function resetFormula(sheet, range) {
  formulas = range.getFormulas();

  for (var r = 0; r < formulas.length; r++) {
    for (var c = 0; c < formulas[r].length; c++) {
      var formula = formulas[r][c];
      var value = sheet.getRange(r + 1, c + 1).getValue();
      var value = value + "";
      // var keyword = "'="; // no results
      // var keyword = "'"; // finds all cells with '.  Not that BEGIN with '
      var keyword = "'\="; // no results

      if (formula.indexOf(keyword) == 0) {
        console.log("(formula.indexOf(keyword) == 0)");
        console.log("formula = " + formula);
        // sheet.getRange(r + 1, c + 1).setFormula(formula);
      } else {
        if (value.indexOf(keyword) == 0) {
          console.log("(value.indexOf(keyword) == 0)");
          console.log("value = " + value);
          // sheet.getRange(r + 1, c + 1).setValue(value);
        }
      }
    }
  }
}

51 thoughts on “Find all cells with escaped out formulas '=”

Leave a Comment