javascriptexceloffice365office-scripts

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).


Solution

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

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