Excel Script ReplaceAll ignore formulas

djmitz6 IMHO this is the kind of thing that could help other users. When you have a minute feel free to click the Mark as answer link (at the bottom of each reply you get here) on the post that put you on track/solved the issue - Thanks

djmitz6 

As for the performance I'm not sure. Perhaps your variant will be better. That's only to test.

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

djmitz6 

So, you'd like to

- remove first N rows

- convert the rest into the structured table (keeping formulae in it as is)

- double ' for each cell within the table ignoring ones with formula

Correct?