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.
setValues
function for conversion, as it is more efficient than copyFrom
. 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);
}