Working with custom excel formulas. When I try to unlink formula in the cells and put only the value back to the cells it takes too much time for context sync. How do I reduce the timing? Maybe it's because of the context sync in the loop, but not working without context sync. The toast message after the successful unlinking of the formulas comes very late almost takes 5 to 10 mins to show.
async unlinkWorkbook() {
try {
Excel.run((ctx) => {
var worksheets = ctx.workbook.worksheets;
worksheets.load('name');
var usedRange: Excel.Range[] = [];
var cellProperties: any = [];
return ctx
.sync()
.then(function () {
usedRange = [];
cellProperties = [];
for (var i = 0; i < worksheets.items.length; i++) {
usedRange[i] = worksheets.items[i].getUsedRange();
usedRange[i].load(['address', 'values', 'formulas', 'text']);
const propertiesToGet = usedRange[i].getCellProperties({
address: true,
});
cellProperties.push(propertiesToGet);
}
})
.then(ctx.sync)
.then(async () => {
var count = 0;
for (var i = 0; i < worksheets.items.length; i++) {
await ctx.sync().then(async () => {
var cells: any = [];
// To load the spill values of each cell in the range
usedRange[i].formulas.forEach((array, xindex) => {
array.forEach((formula, yindex) => {
var location = cellProperties[i].value[xindex][yindex].address.split('!')[1];
var cell = worksheets.items[i].getRange(location);
// Load the spill values of the cell
cells.push(cell.getSpillingToRangeOrNullObject().load(['values']));
});
});
await ctx.sync().then(() => {
var k = 0;
usedRange[i].formulas.forEach((array, xindex) => {
array.forEach((formula, yindex) => {
if (
formula &&
isNaN(formula) &&
(formula.toLowerCase().indexOf('q.get') !== -1 ||
formula.toLowerCase().indexOf('q.getlist') !== -1)
) {
var location = cellProperties[i].value[xindex][yindex].address;
var cell = worksheets.items[i].getRange(location);
// Check if cells contains the spill values or not
if (cells[k].values) {
cell = cell.getResizedRange(cells[k].values.length - 1, cells[k].values[0].length - 1);
cell.formulas = cells[k].values; // replace cells with spill values
} else {
const value = usedRange[i].values[xindex][yindex];
cell.values = [[value]]; // replace cells with normal values
if (!isNaN(value) && value.toString().indexOf('.') != -1) {
cell.numberFormat = [['0.00']];
}
}
count++;
}
k++;
});
});
});
});
}
if (count > 0) {
await ctx.sync();
this.apiDataService.showUnlinkModalFn({
severity: 'success',
summary: 'Success',
detail: 'Formulas successfully unlinked.',
});
} else {
this.apiDataService.showBannerUp({
severity: 'info',
summary: 'Unlink',
detail: 'No formulas found in the selected range to unlink!',
});
}
});
});
} catch {
this.apiDataService.showUnlinkModalFn({
severity: 'error',
summary: 'Failed To Unlink Formulas',
detail: `Something went wrong.
Please try again!`,
type: 'workbook',
});
}
}
