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);
    }
  };

In addition to @RaymondLu answer, don't have a context.sync in a loop. For details about how to avoid this, see learn.microsoft.com/en-us/office/dev/add-ins/concepts/…. — Rick Kirkham
– Rick Kirkham, Commented Apr 23, 2020 at 1:08

Raymond Lu · Accepted Answer · 2020-04-23 00:57:42Z

3

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

answered Apr 23, 2020 at 0:57

Raymond Lu

2,2561 gold badge8 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FreeSoftwareServers · Accepted Answer · 2022-01-28 19:19:52Z

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

A.Fuentes · Accepted Answer · 2020-04-29 10:52:59Z

0

VBA allows you to access every cell on the iteration using the for loop. What I did in TypeScript to replicate this for a range was loading the properties of columns and rows and storing the values in variables:

await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
const range = context.workbook.getSelectedRange();
range.load(["columnIndex", "columnCount", "rowCount", "rowIndex"]);
let col_1 = range.columnIndex; //first column index
let nc = range.columnCount; //number of columns
let row_1 = range.rowIndex; //first row
let nr = range.rowCount; //number of rows

Then you can access each cell with two for loops:

for (let r = 0; r < nr; r++) {
      for (let c = 0; c < nc; c++) {
         cell = sheet.getCell(r + row_1, c + col_1);
         cell.load(["address", "values", "formulasR1C1", "format"]);
         await context.sync();
         let cell_add = cell.address;
         let cell_val = cell.values[0][0]; //values of a cell/range is always a matrix
         if (cell_val <= 5) {
         //do something
         }
      }
  };

The code might not be the best, but it worked for me.

Hope this helps.

answered Apr 29, 2020 at 10:52

A.Fuentes

278 bronze badges

2 Comments

Ethan Over a year ago

Thank you for this! This definitely works, but per Rick’s comment above, you should avoid context.sync in a loop.

A.Fuentes Over a year ago

Yes, I just realised later! I'll get deep in the working of the code and I'll try to avoid unnecessary synchronizations.

Collectives™ on Stack Overflow

For Each Cell Loop - Excel JavaScript API

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related