I am trying to populate multiple ranges using a formula and then convert the range to values using paste as value. The Office add-in is being used on Sharepoint Excel for the web. The code usually works but once in a while I get a "Rich API: An internal error has occurred" error, due to which the formulas do not get replaced by values. After the first time, the error happens on every subsequent try it crashes with the "Rich API: Timeout" error. There are about 300 ranges of size approx 25x25.
Code:
async function loadValues() {
//This function is exectued to fill some ranges after data is retrived from server and pasted in a bacckend table
await Excel.run(async function main(context) {
context.workbook.application.calculationMode = "Manual";
let names = context.workbook.names
context.application.suspendScreenUpdatingUntilNextSync();
var rng = names.getItem("controlsToUse").getRange();
rng.load("values");
await context.sync();
context.application.suspendScreenUpdatingUntilNextSync();
// Controls to use contains the name of the ranges in which data has to be loaded and the ranges from which formula to load data has to be copied
var controlsToUse = rng.values;
for (i = 0; i < controlsToUse.length; i++) {
// str is the range in which data has to be pasted and str1 is the range from which formula has to be copied
var str = controlsToUse[i][0];
var str1 = controlsToUse[i][1];
var range1 = names.getItem(str).getRange();
range1.copyFrom(str1, Excel.RangeCopyType.formulas);
range1.untrack();
}
await context.sync();
context.workbook.application.calculate();
await context.sync();
context.application.suspendScreenUpdatingUntilNextSync();
for (i = 0; i < controlsToUse.length; i++) {
var str = controlsToUse[i][0];
var range1 = names.getItem(str).getRange();
range1.copyFrom(str, Excel.RangeCopyType.values);
range1.untrack();
}
await context.sync();
context.workbook.application.calculationMode = "Automatic";
await context.sync();
}).catch(errorHandler)
}
