Excel Script ReplaceAll ignore formulas

Greetings, I'm currently running an excel script where I am replacing all single quote characters with two single quote characters consecutively so that I can push the data in a table over to an ...

Excel for web

Office Scripts

SergeiBaklan

Diamond Contributor

Feb 22, 2024

Lorenzo

That doesn't work such way. Selecting special cells we split entire range on range areas, replaceAll can't be applied to RangeAreas object.

However, idea is great. We may iterate each area and make replacements within it

function main(workbook: ExcelScript.Workbook) {
    workbook
    .getActiveWorksheet()
    .getUsedRange()
    .getSpecialCells(ExcelScript.SpecialCellType.constants)
    .getAreas()
    .map(a => a.getUsedRange().replaceAll("'", "''", {}))
}

Iterating cell by cell in initial range is less preferable from performance point of view.