I am working on a workbook that has several worksheets, I am using the following code to find the formulas on each sheet and convert then to their calculated values.
function main(workbook: ExcelScript.Workbook) {
// Acc Input
{
const accInp = workbook.getWorksheet("Accuracy Input and Calc");
const uRaccInp = accInp.getUsedRange();
const fcaccInpu = uRaccInp.getSpecialCells(ExcelScript.SpecialCellType.formulas);
if (accInp) {
fcaccInpu.getAreas().forEach((range) => {
let currVal = range.getValues();
range.clear(ExcelScript.ClearApplyTo.contents);
range.setValues(currVal);
})
} else {
console.log("Acc Sheet is already Calculated.")
}}
}
This has worked for all 6 of the worksheets except for this one. Whenever I try to run the script I receive the following error:
Line 11: Range Clear: There was an internal error while processing the request.
Can someone please help me troubleshoot this?
I have tried checking to see if the "range.clear(ExcelScript.ClearApplyTo.contents); only failed because of the "contents" portion by changing it to "formats" and "all" but I am getting the same error each time.
It is difficult to say what's the root cause w/o your file. But a more efficient approach is to operate on the entire UsedRange
. Please try it.
function main(workbook: ExcelScript.Workbook) {
// Acc Input
{
const accInp = workbook.getWorksheet("Accuracy Input and Calc");
const uRaccInp = accInp.getUsedRange();
const fcaccInpu = uRaccInp.getSpecialCells(ExcelScript.SpecialCellType.formulas);
if (accInp) {
let valuesInp = uRaccInp.getValues();
uRaccInp.clear(ExcelScript.ClearApplyTo.contents);
uRaccInp.setValues(valuesInp);
}
else {
console.log("Acc Sheet is already Calculated.")
}
}
}