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

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

  1. Explanation:

    • It is really difficult for me to debug your code without being able to see the file itself, but I don’t think it is worth debugging it but rather create a new solution because your current approach does not follow the best practices, namely iteratively using getRange,getValue and setValue is going to cost you a lot of performance and therefore you will have to redesign your logic.

    Your goal is to remove '= from the "formulas".

    • Keep in mind that a formula like that is actually (like you said) commented out, meaning it is a simple string: '=sum(B1). Therefore, using getFormulas is not going to work because the values you are interesting in are strings and not formulas. You need to use getValues instead.

    Solution:

    Assuming the following sheet with random data:

    enter image description here

    the following script will remove ' from every cell that contains it:

    function myFunction1() {
      const ss = SpreadsheetApp.getActive();
      const sh = ss.getSheetByName("Sheet1");
      const charToDel = "'=";
      const range = sh.getDataRange();
      const values = range.getValues();
      const new_vals = values.map(r=>r.map(c=>c.toString().includes(charToDel)?c.replace(charToDel,''):c))
      range.setValues(new_vals)
    }
    

    and the result after you execute the script will be formulas in the relevant cells:

    enter image description here

    Side note:

    I think it is very straighforward to change my code to adapt your logic. Instead of sh use sheet and delete range as it is passed by the function. Of course add the relevant parameters to the function.

    References:

    Reply
  2. 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
  3. 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