MS 365 Excel Scripts Error " Range clear: There was an internal error while processing the request."

Question

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.

Answer 1

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