I’m trying to set a formula for several columns of data using Excel js API. Below is working but performance is very poor as it updates on a onchanged event to refelect any change in the data. Is there a betterfaster way I can achieve this?
async function setformulas(worksheetName) {
await Excel.run(async (context) => {
worksheet = context.workbook.worksheets.getItem(worksheetName);
worksheet.load(["protection/protected"]);
await context.sync();
worksheet.protection.unprotect("");
await context.sync();
var myUsedRange = worksheet.getUsedRange();
let lastrow = myUsedRange.getLastRow().load(["rowindex", "values"]);
await context.sync();
let lastrowindex = lastrow.rowIndex + 1;
var load_opts_arr = ["rowCount", "rowIndex", "formulas"];
var Used_Rng_And_Props = worksheet.getRange("B4:B" + lastrowindex);
Used_Rng_And_Props.load(load_opts_arr);
var Used_Rng_And_Props2 = worksheet.getRange("H4:H" + lastrowindex);
Used_Rng_And_Props2.load(load_opts_arr);
var Used_Rng_And_Props3 = worksheet.getRange("M4:M" + lastrowindex);
Used_Rng_And_Props3.load(load_opts_arr);
await context.sync();
Used_Rng_And_Props.set({ formulas: '=IF(C4<>"",C4,"01/01/4000")' });
Used_Rng_And_Props2.set({ formulas: '=IF(G4="","",HYPERLINK("Tel:"&G4,"call"))' });
Used_Rng_And_Props3.set({
formulas: '=IF(L4="","",HYPERLINK("https://maps.google.co.uk/maps/dir/"&L4,"Google Maps"))'
});
await context.sync();
});
}