Populating multi-dimensional array using getValues

1
votes

I'd like to populate a multi-dimensional array with the values of 3 contiguous cells using getValues, but when I do this, I seem to be getting an extra set of brackets that prevents me from using the array as intended later in the code.

var finalsArray = [[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0]];

var x = 0;

for(var i = 4; i <= finalsLastRow; i++) {

 finalsArray[x] = finalsSheet.getRange(i, 7, 1, 3).getValues();

 x++;

}

Using this method, finalsArray[x] returns [[88.0, 95.0, 43412HOUOKC]].

But I want finalsArray[x] to return [88.0, 95.0, 43412HOUOKC].

I am able to achieve this and get rid of the extraneous bracket if I populate the array individually using getValue separately for each cell (see below), but I would love to optimize my code and understand why getValues is not working.

var finalsArray = [[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0],[0,0,0]];

var x = 0;

for(var i = 4; i <= finalsLastRow; i++) {

 finalsArray[x][0] = finalsSheet.getRange(i, 7).getValue();
 finalsArray[x][1] = finalsSheet.getRange(i, 8).getValue();
 finalsArray[x][2] = finalsSheet.getRange(i, 9).getValue();

 x++;

}

Thank you very much for any help you guys can provide.

Ryan

1
javascriptgoogle-apps-scriptgoogle-sheets
getValues returns a "2D" array, in which the outer elements are rows and the inner elements are values of that row. So for an N-row getvalues call, you get [ [r1c1, r1c2, ..., r1cN], [r2c1, r2c2, ...], ..., [rNc1, ...] ]. To me it looks like you could just do finalsArray = finalsSheet.getRange(numHeaders + 1, 7, finalsSheet.getLastRow() - numHeaders, 3).getValues() where you have set the numHeaders appropriately (e.g. 3) – tehhowch
got it, thank you very much! – Ryan Richards

1 Answers

2
votes

Just reference the first element of the returned value in place

for(var i = 4; i <= finalsLastRow; i++) {
 finalsArray[x] = finalsSheet.getRange(i, 7, 1, 3).getValues()[0];
 x++;
}