office script replace formula with value

please advice best way to replace formulas with values. I used before:

//copy-paste values
rEPORT__2_.getRange().copyFrom(rEPORT__2_.getRange(), ExcelScript.RangeCopyType.values, false, false);

but it stopped to work starting from today. Excel just get unexpected error if i try to copy-paste more tnan 100 rows in one script. So, is there any other way to replace formalas with values at whoole sheet ?

I tried to use loop (for i). But result almost the same - it process about 50-100 rows with copy-paste and then i just get an error (time-out or unexpected).

There's an effective method for transforming formulas into fixed values.

• Obtain the intersection range to minimize the size of the target range.
• Use the setValues function for conversion, as it is more efficient than copyFrom.
• Disable auto-calculation before update
• Clear formulas before update cells' value

    let application = workbook.getApplication(); 
    application.setCalculationMode(ExcelScript.CalculationMode.manual);
    const selectedSheet = workbook.getWorksheet("ReportSheet"); // Update name as needed
    let dataRanage = rEPORT__2_.getRange().getIntersection(selectedSheet.getUsedRange());
    let dataValues = dataRange.getValues();
    dataRange.clear(ExcelScript.ClearApplyTo.contents);
    dataRange.setValues(dataValues);     
    application.setCalculationMode(ExcelScript.CalculationMode.automatic);

Sample code to convert formula to values on batch (eg. 300 rows)

    let application = workbook.getApplication();
    application.setCalculationMode(ExcelScript.CalculationMode.manual);
    // let selectedSheet = workbook.getActiveWorksheet();
    let selectedSheet = workbook.getWorksheet("ReportSheet"); 
    let allRanage = rEPORT__2_.getRange().getIntersection(selectedSheet.getUsedRange());
    let startRow = allRanage.getCell(0,0).getRowIndex();
    let rowCount = allRanage.getRowCount();
    const rowBatch = 300
    for(let i=startRow+1; i<startRow+rowCount+1; i+=rowBatch){
        let lastRow = i+rowBatch-1;
        console.log(i, lastRow)
        let rowRange = selectedSheet.getRange(`${i}:${lastRow}`)
        let dataRange = allRanage.getIntersection(rowRange);
        let dataValues = dataRange.getValues();
        dataRange.clear(ExcelScript.ClearApplyTo.contents);
        dataRange.setValues(dataValues);     
    }
    application.setCalculationMode(ExcelScript.CalculationMode.automatic);

I have tested it with both Excel Online and Excel 365 desktop. It only takes several seconds.

Test code

function main(workbook: ExcelScript.Workbook) {    
    let application = workbook.getApplication();     
    application.setCalculationMode(ExcelScript.CalculationMode.manual);
    let selectedSheet = workbook.getActiveWorksheet();
    let dataRange = selectedSheet.getRange("A1:Z5000");
    dataRange.setFormulaLocal("=row()+column()");
    let dataValues = dataRange.getValues();
    dataRange.clear(ExcelScript.ClearApplyTo.contents);
    dataRange.setValues(dataValues);    
    application.setCalculationMode(ExcelScript.CalculationMode.automatic);  
}