Converting array into 2D array within loop

Question

I have a loop which defines arrays with changing variables - start row and row numbers always changing. I would need to be able to access certain columns within this array, but I have no idea how to do that.

var nameRowN = starting row numbers already in an array
var sor = number of rows in each mini array (which is arej variable) already calculated and put in an array

 for (var m = 0; m < nameRowN.length; m++) { 
      arej[m] = sourcesheet.getRange(nameRowN[m],5,sor[m]-1,29).getValues();
 }

For example, arej[0] gives me this:

[20-01-07 11:35:33:289 GMT] arej: ,water outlet,,,,,,,,,,,,,,,,,,,,,,,,,,,,Chlorine content,,,,,1 / day alternate,,ASTM,,,,,NSP,,,Sat 1899,,0.2,,,,ppm,,,2ppm,,,,,WATER,,,,,,,,,,,,,,,,,,,,,,,,,,,,

The empty cells are fine, because they're always the same amount at the same place. I need to access certain data from these arrays, which are always in the same column, sometimes through multiple rows. In the above example, it's in 3 rows. I'm stuck as I don't know what to do with this.

Your script will be extremely slow if you implement it this way (making many separate getRange().getValues() calls). Consider instead using a RangeList to convert multiple A1- or R1C1-style strings into Ranges, or just calling getValues() on the whole sheet and using Array#filter to remove the rows you dont care about — tehhowch
– tehhowch, Commented Jan 7, 2020 at 12:27
There are never more arrays than 20, in this case it's 16, so it doesn't take that long to get all the values. I'm not too sure what you mean by filtering the array. All the data changes constantly. The only way I could filter them is by columns. The data I need is in column 1, 2, 16, and 18 - the columns are always the same. But I don't know how to look for columns as my array has only [m] as variable. If I change m, that gives me a whole new mini array, so arej[1] would have all different data. — Vidomina Sangre
– Vidomina Sangre, Commented Jan 7, 2020 at 12:56

tehhowch · Accepted Answer · 2020-01-07 13:21:11Z

If I were to write a type declaration for arej, it would be any[][][]. Another way to say that is Array<any[][]> -- you created it as an array whose elements are the result of calling getValues. What's the return type of getValues? An array whose elements represent spreadsheet rows by also being an array of cell values in that row (i.e. column values). So just keep indexing!

// arej[0] -> first 2D array of [rowIndex][columnIndex]
// arej[1] -> second 2D array
// ...
// arej[0][0] -> first row in arej[0]
// arej[0][0][0] -> first column in the first row in the first range stored in arej
// Print all values
arej.forEach(function (rg, rgIdx) {
  // rg is arej[0], then arej[1], etc.
  rg.forEach(function (row, rowIdx) {
    row.forEach(function (value, colIdx) {
      Logger.log("rg#: " + rgIdx + ", row#: " + rowIdx + ", col#: " + colIdx + ", Val: '" + value + "'");
    });
  });
});

Here I reduce the likelihood of indexing errors by using the Array class method forEach.

Collectives™ on Stack Overflow

Converting array into 2D array within loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related