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);
        }
      }
    }
  }
}

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

  1. I have not yet tried @Tanaike’s solution, but this is what I came up with earlier today. I was searching for the ' which wasn’t necessary.

    var keyword = "=";

    function resetFormula(sheet, rangeValues, keyword) {
      for (var r = 0; r < rangeValues.length; r++) {
        for (var c = 0; c < rangeValues[r].length; c++) {
          var value = sheet.getRange(r + 1, c + 1).getValue();
          var value = value + "";
          if (value.indexOf(keyword) !== -1) {
            sheet.getRange(r + 1, c + 1).setValue(value);
          }
        }
      }
    }
    
    Reply
  2. I believe your goal as follows.

    • You want to find the formulas which have ' at the top of formula. It’s like '=.
    • You want to replcace '= with = by removing '.
    • You want to achieve this using Google Apps Script.

    In this answer, as the other method, I would like to propose to achieve your goal using TextFinder. Sample script is as follows.

    Sample script:

    function myFunction() {
      const sheetName = "Sheet1";  // Please set the sheet name.
    
      SpreadsheetApp
        .getActiveSpreadsheet()
        .getSheetByName(sheetName)
        .createTextFinder("\'\=")
        .matchFormulaText(true)
        .useRegularExpression(true)
        .replaceAllWith("=");
    }
    
    • In this case, the value of '= in all formulas of "Sheet1" is replaced with =.

    • If you want to usee this for all sheets in the Spreadsheet, you can also use the following script.

        SpreadsheetApp
          .getActiveSpreadsheet()
          .createTextFinder("\'\=")
          .matchFormulaText(true)
          .useRegularExpression(true)
          .replaceAllWith("=");
      

    Reference:

    Reply

Leave a Comment