Optimization of code while working with range object in excel

Question

I have recently moved for office add-in from vb.net to JavaScript and Office.js. In General, I have observed that JavaScript add-in is way too fast than to vb.net. In one of the operations, I am not able to get benefit of speed in JavaScript add-in. Maybe it is due to inadequate code that I am using!
I have one master sheet which contains a large table, first column of table is name of every other sheets of that workbook and first row of table have address of cells. Rest of data in table are values to be transferred at the sheet name defined in the first column and cell adress defined in the first row.
In coding, I am creating an array of range object for each value indicated in table and then I run context. Sync () function to restore values in every sheets. In my real-life application data in table can be 10K to 50K, I can see time taken for this operation is about approx. one minute (for 10K). In contrast of this, I can create the table (master sheet) within few seconds (5 -6 sec) only. Is there any other workaround or suggestion to reduce time?

/* global Excel, console*/
export default async function restoreData() {
  var allRollback = false;
  await Excel.run(async (context) => {
    var sheets = context.workbook.worksheets.load("items/name");
    var wsdb = context.workbook.worksheets.getItem("db");
    const arryRange = wsdb.getUsedRange();
    var addRow = 0;
    var sheetName = [];
    var rangeObj = [];
    //Get last row/column from used range
    arryRange.load(["rowCount", "columnCount", "values", "address"]);
    await context.sync();
    sheets.items.forEach((sheet) => sheetName.push(sheet.name));
    for (let iRow = 0; iRow < arryRange.rowCount; iRow++) {
      if (arryRange.values[iRow][0] == "SheetName/CellAddress") {
        addRow = iRow;
      }
      if (sheetName.indexOf(arryRange.values[iRow][0]) != -1) {
        for (let iCol = 1; iCol < arryRange.columnCount; iCol++) {
          if (arryRange.values[addRow][iCol]) {
            const sheet = context.workbook.worksheets.getItem(arryRange.values[iRow][0]);
            const range = sheet.getRange(arryRange.values[addRow][iCol]);
            range.values = arryRange.values[iRow][iCol];
            rangeObj.push(range);
          }
        }
      } else {
        // code for higlight Row in db
        console.log("Y");
      }
    }
    console.log("Range object created");

    await context.sync();
    // console.log(arryRange.rowCount);
    // console.log(arryRange.columnCount);
    console.log("done");
    // Copy a range starting at a single cell destination.
  });
  allRollback = true;
  return allRollback;
}

In general reducing the number of context.sync() call can reduce the time. You may can check the logic to see if operations can be combined before calling context.sync(). — xiaochun
– xiaochun, Commented Oct 8, 2021 at 8:22
@xiaochun-Microsoft, please note that I am calling context.sync() two times only in this operation. First time to get data from master sheet and second time to set data to each sheets. — Shailesh Patel
– Shailesh Patel, Commented Oct 8, 2021 at 16:31
See learn.microsoft.com/en-us/office/dev/add-ins/concepts/… If that doesn't help, please show your code. — Rick Kirkham
– Rick Kirkham, Commented Oct 8, 2021 at 16:48
@RickKirkham, as per me context.sync() have not been used in loop. Function code added in main post. — Shailesh Patel
– Shailesh Patel, Commented Oct 8, 2021 at 18:16

Dharman · Accepted Answer · 2021-12-02 19:02:30Z

First, base on your statement, I assume you have a table in master sheet. This table's heading is like ["sheetName","A13","D23",...] ("A13" and "D23" are examples of cell address). In each row of this table, contain sheet's name and some values. The sheet's name may not related to a real sheet's name(not exist), and values may contain some blank. And you want to set values on other sheets based on the information given by master sheet's table.

Then I have some suggestions based on my assumptions and your code.

Move unchanged value out of loops. For example, you called const sheet = context.workbook.worksheets.getItem(arryRange.values[iRow][0]);. We can move context.workbook.worksheets out of loops by define var sheets = context.workbook.worksheets and const sheet = sheets.getItem(arryRange.values[iRow][0]). Which could increase the performance.

Also some reused values like arryRange.values[iRow][0], arryRange.values[0][iCol] can be moved out of loop.

Seems you use arryRange.values[addRow][iCol] only for get the address in table's heading. You can replace it by arryRange.values[0][iCol].

Below is the code I rewrite, just for reference, it may not fully satisfy what you need.

export default async function restoreData() {
  var allRollback = false;
  await Excel.run(async (context) => {
    var sheets = context.workbook.worksheets.load("items/name");
    var wsdb = context.workbook.worksheets.getItem("db");
    const arryRange = wsdb.getUsedRange();
    //var addRow = 0;
    var sheetName = [];
    var rangeObj = [];
    //Get last row/column from used range
    arryRange.load(["rowCount", "columnCount", "values", "address"]);
    await context.sync();
    sheets.items.forEach((sheet) => sheetName.push(sheet.name));

    var cellAddress, curSheetName;
    const mySheets = context.workbook.worksheets;

    for (let iRow = 0; iRow < arryRange.rowCount; iRow++) {
      curSheetName = arryRange.values[iRow][0]
      if (sheetName.indexOf(curSheetName) != -1) {
        for (let iCol = 1; iCol < arryRange.columnCount; iCol++) {
          cellAddress = arryRange.values[0][iCol];
          if (cellAddress) {
            const sheet = mySheets.getItem(curSheetName);
            const range = sheet.getRange(cellAddress);
            range.values = arryRange.values[iRow][iCol];
            rangeObj.push(range);
          }
        }
      } else {
        // code for higlight Row in db
        console.log("Y");
      }
    }
    console.log("Range object created");

    await context.sync();
    // console.log(arryRange.rowCount);
    // console.log(arryRange.columnCount);
    console.log("done");
    // Copy a range starting at a single cell destination.
  });
  allRollback = true;
  return allRollback;
}

More references:

https://learn.microsoft.com/en-us/office/dev/add-ins/excel/performance?view=excel-js-preview

https://learn.microsoft.com/en-us/office/dev/add-ins/concepts/correlated-objects-pattern

Shailesh Patel · Accepted Answer · 2021-11-11 04:53:04Z

0

with refer of you assumption, please note that master sheet was crated based on actual workbook with user selected area + deletion of empty value columns in master sheet (I,e empty cell at the adress at each sheet). sheet's name will be as same as actual sheet name unless user don’t change value in master sheet by accident. With reference to yours,

Suggestions 1) I believe that Move unchanged value out of loops will be the key to my problem. I will refabricate one range and get data comparison for changes. I believe speed will be approved drastically in best case scenario. (I will also have some worst-case scenario (less than 5% case), where I will be required to write every values of master sheet).

Suggestions 2) I am planning to have a new functionality, which may have more rows as address in, that is the reason I have I am keep looking for address rows.

Thanks for your reply.

answered Nov 11, 2021 at 4:53

Shailesh Patel

179 bronze badges

1 Comment

Ike Over a year ago

Please use the edit link on your question to add additional information. The Post Answer button should be used only for complete answers to the question. - From Review

Collectives™ on Stack Overflow

Optimization of code while working with range object in excel

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related