Workbook getting hanged during Excel data writing

Question

We have developed an Excel Javascript plugin using the React framework.

Add-in workflow as follows:

1. Request data from Ui using APIs with some parameters
2. Wait for the response and keep it in local cached
3. Start writing data into an active Excel sheet

Problem:

During the Excel data writing, the Excel workbook got frozen until the data is not written in sheet

My case the maximum amount of data coming from APIs is 5-8K.

Below is the sample code.

try {
  var i = 0;
  // these data are coming from APIs
  var allCompanies =[12,32,33,43,45,66,12,32,10,12,21,90];
  allCompanies.forEach(async _company => {
    // calling APIs for each _docs and writing data for that companies
    await fetchData(_company).then(async (responseRows) => {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getActiveWorksheet();
        sheet.load(["name"]);
        await context.sync();
        for (let i = 0; i < responseRows.length; i++) {
          // data operations into Exel sheet
          // creating table, table header, applying Css
          // writting value to each cells
        }
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
        await context.sync();
      });
    });
  });
} catch (error) {
  console.log("error on appending data into excel " + error);
}

Any solution will be helpful to me.

Thanks.

Answer 1

If possible, move context.sync() out of the loop. Calling context.sync() multiple times within a loop can result in decreased performance and increased latency.

Avoid using the context.sync method in loops