Excel Script ReplaceAll ignore formulas

djmitz6 

When it could be

function main(workbook: ExcelScript.Workbook) {

    // will be used as Power Automate parameters
    const WorkSheet_Name = "Sheet1 (2)"
    const DeleteTopRows = 11

    let selectedSheet: ExcelScript.Worksheet;
    if (WorkSheet_Name == "") {
        selectedSheet = workbook.getWorksheets()[4];
    } else {
        selectedSheet = workbook.getWorksheet(WorkSheet_Name);
    }
    if (DeleteTopRows > 0) {
        //Delete Top N Rows
        selectedSheet.getRange("1:" + DeleteTopRows).delete(ExcelScript.DeleteShiftDirection.up);
    } else {
    }
    // Clear Any Filters on Selected Sheet
    selectedSheet.getAutoFilter().remove();

    // Get used Range
    let usedRange = selectedSheet.getUsedRange();
    //usedRange.copyFrom(usedRange, ExcelScript.RangeCopyType.values, false, false)

    usedRange.getSpecialCells(ExcelScript.SpecialCellType.constants)
        .getAreas()
        .map(a => a.getUsedRange().replaceAll("'", "''", {}))


    // Add a new table
    let newTable = workbook.addTable(usedRange, true);
    // Replace SQL Reserved Characters

/*   
    selectedSheet.replaceAll("'", "''", {
        completeMatch: false,
        matchCase: false
    })
*/
    // Create ID column for Power Automate ODATA Filter
    newTable.addColumn(-1, Number[0], "ID");
    let ID = newTable.getColumnByName("ID").getRangeBetweenHeaderAndTotal();
    ID.setNumberFormatLocal("0");
    ID.setFormula("=ROUNDUP((ROW()-1)/5000,0)")
    // Return Table ID to Power Automate
    return newTable.getId();
}