Excel Script ReplaceAll ignore formulas

djmitz6 

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:

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

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.

1. Replace single quotes: Apply replaceAll to the filtered range:

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

1. Optionally, replace back in the original range:

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

Option 2: Using a custom function:

1. Define a custom function:

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

1. Apply the function:

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.