Iterate over a Range fast in Excelscript for web

Question

I want to check that a range of cell are empty or has any values in them, I use this for loop :

for (let i = 0; i <= namesRange.getCellCount(); i++) {
  if (namesRange.getCell(i,0).getText() == "") 
    {
      break;
    }
  bookedCount += 1;
}

However this iteration is extremely slow (as is the use of Range.getValue, but the console warns you that iterating with .getValue is slow, does not warn you with getText) It takes several seconds to iterate over a very short list of 10 elements.

Is there any way to check for the values of a cell in a speedy manner using ExcelScripts?

Does this mean that, even if I develop a UDF or a ribbon Add-In with office.js and Node.js it will also be this extremely slow for iterating over cells?

Is there any way to make this faster?

score 5 · Answer 1 · answered Mar 15 '22 at 00:43

Another alternative to the first answer is to use the forEach approach for every cell in the range of values.

It can cut down the amount of variables you need to achieve the desired result.

function main(workbook: ExcelScript.Workbook)
{
  let worksheet = workbook.getActiveWorksheet();
  let usedRange = worksheet.getUsedRange().getValues();

  usedRange.forEach(row => {
    row.forEach(cellValue => {
      console.log(cellValue);
    });
  });
}

Brian Gonzalez · Accepted Answer · 2022-03-15T01:08:03.027

The reason your code is likely performing slowly is that the calls to getCell() and getText() are expensive. Instead of performing these calls every time in the loop you can try a different approach. One approach is to get an array of the cell values and iterate over that. You can use your namesRange variable to get the array of values. And you can also use it to get the row count and the column count for the range. Using this information, you should be able to write nested for loops to iterate over the array. Here's an example of how you might do that:

function main(workbook: ExcelScript.Workbook) {
  let namesRange: ExcelScript.Range = workbook.getActiveWorksheet().getRange("A1");
  let rowCount: number = namesRange.getRowCount();
  let colCount: number = namesRange.getColumnCount();
  let vals: string[][] = namesRange.getValues() as string[][];

  for (let i = 0; i < rowCount; i++) {
    for (let j = 0; j < colCount; j++) {
      if (vals[i][j] == "") {
        //additional code here
      }
    }
  }
}

Worked like a charm! Thank you, it's so much more faster now! — Wolfeius, Mar 15 '22 at 15:36

Iterate over a Range fast in Excelscript for web

2 Answers2