Populating multi-dimensional array using getValues

Question

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

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
– tehhowch, Commented Nov 8, 2018 at 19:34

ControlAltDel · Accepted Answer · 2018-11-08 18:01:47Z

2

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

answered Nov 8, 2018 at 18:01

ControlAltDel

35.3k10 gold badges60 silver badges86 bronze badges

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

5 Comments

Ryan Richards Over a year ago

Awesome, thank you very much, that did the trick! However, as someone who just switched over to appscripts from VBA a few weeks back, I am not quite sure I understand WHY that does trick. Can you shed any light on that?

ControlAltDel Over a year ago

The returned value from finalsSheet.getRange(...).getValues() is a double array. However the outer array has just one element: the inner array. So all the [0] is doing is referencing that one element, inner array.

Peter van der Wal Over a year ago

Not that this only works properly as intended due to the '1' in the getRange (which is the max amount of rows the method will return parameter)

ControlAltDel Over a year ago

@PetervanderWal I think you mean "Note that..." :)

Ryan Richards Over a year ago

@ControlAltDel / PetervanderWal Thank you both - that makes perfect sense and helps a lot. Really appreciate it!

Collectives™ on Stack Overflow

Populating multi-dimensional array using getValues

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related