OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Excel Js Api. Set Formula for a range of cells

  • Thread starter Thread starter user552769
  • Start date Start date
U

user552769

Guest
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 better\faster way I can achieve this?

Code:
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();
  });
}

<p>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 better\faster way I can achieve this?</p>
<pre><code>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();
});
}
</code></pre>
 

Latest posts

Top