For Each Cell Loop - Excel JavaScript API

Question

In the following VBA code, I debug.print each cell value. Using the data in the picture, my answer appears like this.

Sub loopAndDebugPrintEachCell()
Dim cl As Object
With Sheets("Sheet1")
    For Each cl In .Range("A1:D2")
        Debug.Print cl.Value
    Next cl
End With
End Sub

I am trying to reiterate this code in JavaScript and I figured out a solution, but I am not sure that this is the most efficient way. I am getting the exact same answer, but is there a more advantageous way to loop through a range?

  loopAndConsoleLogEachCell = async () => {
    try {
      await Excel.run(async context => {
        const range = context.workbook.worksheets.getItem("Sheet1").getRange("A1:D2");
        range.load(["columnCount", "rowCount"]);
        await context.sync();
        let i = 0;
        let rowC = 0;
        do {
          let loopRng = range.getAbsoluteResizedRange(1, 1).getOffsetRange(rowC, i).load(["values"]);
          await context.sync();
          console.log(`${loopRng.values} `);
          let rangeColCount = Math.floor(((range.columnCount) / 2) + 1);
          if (rowC < (range.rowCount) && i == (rangeColCount)) {
            rowC++;
            i = 0;
          } else {
            i++;
          }
        }
        while (rowC < range.rowCount && i < range.columnCount);
      });
    } catch (error) {
      console.error(error);
    }
  };

Answer 1

If you want to print the value of each cell in the range, you could use range.values API

Here is the sample code for getting the values for the range

  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:E6");
    range.load("text");
    range.load("values");

    await context.sync();
    console.log(range.values);
    //console.log(JSON.stringify(range.text, null, 4));
  });

The document can be found https://learn.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#values

Answer 2

Update:

My original answer searched the entire range starting at row/col 0/0, but I quickly realized a few "gotchas" based on the code (copied from MS somewhere...).

Here is an example where I'm only searching in a column and I'm not starting at row 0.

A few notes:

• I changed r to i because r makes you think row but really, its the index of the range which is zero indexed, but could start at row 42.

• So, when accessing values for B2 inside of say Range("B2:B5") your value starts with .values[0][0].

• To get the value of Cell C2 inside Range("B2:C5") your value would be .values[0][1]. (0 = first row of RANGE and 1 = second col in range).

• Using foo.length inside loop declaration re-calculates the variable each iteration of the loop, for readability and efficiency, declare your start end variables before loop.

        var ws = context.workbook.worksheets.getActiveWorksheet();
        var rng = ws.getRange("B2:B5");
  
        rng .load(["rowCount", "values"]);
        return context.sync()
            .then(function () {
                var RowCount = rng.rowCount  
                for (var i = 0; i < RowCount; ++i) {
                    var rcellval = rng .values[i][0]
                    console.log(rcellval)
                }
            })
  

---

Here is an example I used to iterate over a range, only using one context.sync() to find a cell match via RegEx which should illustrate how to use a For Each Cell loop. Keep in mind I am new to JS as well, but I believe this is proper method.

function Bool_RegEx(rexp, findstr) {
    return rexp.test(findstr)
}

---

function Make_FakeData(context) {
    var ws = context.workbook.worksheets.getActiveWorksheet();
    var rng = ws.getRange("A1:D5");
    rng.values = "TESTDATA";

    var rng = ws.getRange("A5");
    rng.values = "AB12345678"
    return context;
}

---

export async function helloworld(event) {
    try {
        await Excel.run(async (context) => {
            //Start Func
            Make_FakeData(context);

            var ws = context.workbook.worksheets.getActiveWorksheet();
            var rng = ws.getRange("A1:D5");
            rng.load(["columnCount", "rowCount", "values"]);

            let rexp = new RegExp('[a-z]{2}[0-9]{8}', 'i');
            return context.sync()
                .then(function () {
                    for (var r = 0; r < rng.rowCount; ++r) {
                        for (var c = 0; c < rng.columnCount; ++c) { 
                        var rcellval = rng.values[r][c];
                        console.log("r = " + r);
                        console.log("c = " + c);
                        console.log("rcellval = " + rcellval);
                        if (Bool_RegEx(rexp, rcellval) === true) {
                            console.log("RegEx Match in Row r" + r + ", Col " + c);
                            console.log("RegEx Match rcellval = " + rcellval);
                        }
                    }
                }

                })

            //End Func
            await context.sync();
        });
    } catch (error) {
        console.error(error);
    }
    event.completed();
}