Forum Discussion
Excel Script ReplaceAll ignore formulas
Hi djmitz6
Post deleted: misunderstand on how this works - refer to 1st post from SergeiBaklan below please
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.
Selecting special cells we split entire range on range areas, replaceAll can't be applied to RangeAreas object
OK, I didn't read enough. Thanks for fixing my bad + sharing the appropriate code
I'm surprized you generated such solution without Automate on the board.
Just in case. Since that will be used with Power Automate which has no idea about active worksheet, we need to define the worksheet explicitly; or iterate all worksheets if replacements shall be done in each of them. Like
function main(workbook: ExcelScript.Workbook) { workbook .getWorksheets() .forEach(sheet => ( sheet.getUsedRange() !== undefined ) ? sheet .getUsedRange() .getSpecialCells(ExcelScript.SpecialCellType.constants) .getAreas() .map(a => a.getUsedRange().replaceAll("'", "''", {})) : '' ) }In addition we check if the sheet is empty and skip it, otherwise script stops on it.
... without Automate on the board (very frustrating). Thanks again for sharing, very educative even if I can't play with it. Let's see if I understood... could line 12 be something like?
: console.log('${sheet} has no UsedRange')and, is : <something> mandatory (otherwise error msg)?
Was initially confused by
but got it. Super powerful
I'm gonna delete my initial post - makes no sense to keep in it this discussion
