2d subarray index google script

Question

I have 2D array question in google spreadsheet using app script.

I know to get make the code fastest, I'm supposed to use as few get and set functions as possible. So I'm trying to read a whole sheet into an 2D array first then referencing parts of it. Except I'm not sure how to reference the 2D array once it's in.

Here is what i have done so far.

var ss = SpreadsheetApp.getActiveSpreadsheet()
var COMPANYIOSheet=ss.getSheetByName("Sheet1"); 
var IO_array= COMPANYIOSheet.getRange("A1:c10").getValues();

I want to do something like:

var subarray=IO_array[3:5][1];

but that doesn't work. I can only seem to reference just one point ie:

var subarray=IO_array[3][1];

How do i slice into a sub 2D array?

Thanks.

AdamL · Accepted Answer · 2012-11-04 02:17:18Z

5

How do i slice into a sub 2D array?

By using slice(). ;-)

var subarray=IO_array.slice(2, 5);

Then you can reference subarray[i][1]. If you needed to create a new array with only the "second column", you would need to use some iteration.

edit: to add to Serge's addition, and in reply to your comment, here is a general way for returning a 2D sub-array from an array:

function slice2d(array, rowIndex, colIndex, numRows, numCols) {
  var result = [];
  for (var i = rowIndex; i < (rowIndex + numRows); i++) {
    result.push(array[i].slice(colIndex, colIndex + numCols));
  }
  return result;
}

edited Nov 4, 2012 at 2:17

answered Nov 3, 2012 at 20:40

AdamL

24.8k7 gold badges73 silver badges61 bronze badges

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

2 Comments

Serge insas Over a year ago

Hi Adam, this is what I'd call a 'positive collaboration' ;-) thanks and +1 for the "complement to complement" !

Richard de Ree Over a year ago

This solution works like charme. I copied and pasted it and did exactly what I needed. My scripts running >100 times faster

Serge insas · Accepted Answer · 2012-11-03 23:19:32Z

4

As a complement to Adam's answer, in your example IO_array[0] is row 1, IO_array[1] is row 2 etc..., each of them being a 1 dimension array. So the 'rows' are pretty easy to manage.

As for the columns, as Adam suggested you will have to iterate, here is a possible example :

var col = new Array()
for(i=0;i<IO_array.length;++i){
  col.push(IO_array[i][0] ;// taking index 0 means I'll get column A of each row and put it in the new array
}

hoping both answers will make it clear enough ;)

answered Nov 3, 2012 at 23:19

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

1 Comment

jason Over a year ago

wow. so complicated for such an easy action. what if i wanted to make a sub 3x3 array out of the IO_array? can you help me with some code on that? Thanks Serge.

Collectives™ on Stack Overflow

2d subarray index google script

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related