Here's how you can modify your Excel Script to replace all single quotes while ignoring characters inside formulas:

Option 1: Using FilterBy and ReplaceAll:

Filter formulas: Use filterBy to filter out rows containing formulas:

JavaScript

const filteredRange = selectedSheet.getRange("A1:B10").filterBy(row => !row.getRange("A1").hasFormula());

Replace A1:B10 with the actual range you want to filter and A1 with the cell containing the formula you want to check.

Replace single quotes: Apply replaceAll to the filtered range:

JavaScript

filteredRange.replaceAll("'", "''", {
  completeMatch: false,
  matchCase: false
});

Optionally, replace back in the original range:

JavaScript

// If needed, replace back in the original range after modification
selectedSheet.getRange("A1:B10").values = filteredRange.getValues();

Option 2: Using a custom function:

Define a custom function:

JavaScript

function replaceExceptInFormulas(text, searchValue, replaceValue) {
  // Split the text into parts based on formulas
  const parts = text.split(/={1,2}[^=]+={0,2}/);

  // Replace in non-formula parts
  for (let i = 0; i < parts.length; i++) {
    if (!parts[i].startsWith("=")) {
      parts[i] = parts[i].replaceAll(searchValue, replaceValue);
    }
  }

  // Join the parts back together
  return parts.join("");
}

Apply the function:

JavaScript

selectedSheet.getRange("A1:B10").values = selectedSheet.getRange("A1:B10").values.map(row => row.map(cell => replaceExceptInFormulas(cell, "'", "''")));

Explanation:

Option 1: This approach filters out rows containing formulas using filterBy before applying replaceAll to avoid modifying formulas.
Option 2: This option defines a custom function replaceExceptInFormulas that splits the text into parts based on formulas and replaces characters only in non-formula parts.

Both options achieve the desired result of replacing single quotes while preserving formulas. Choose the option that best suits your preference and coding style.

Additional notes:

Ensure you replace A1:B10 with the actual range you want to modify.
Adjust the formula checking logic in Option 1 if your formulas have different patterns.
Consider testing the script on a small sample of data before applying it to your entire dataset.

Forum Discussion

Excel Script ReplaceAll ignore formulas