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?

djmitz6 

When your script shall work. You have values only, all single apostrophes in texts will be doubled, and you have nothing but texts and numbers.