JS pushing arrays to array (concat) in apps script

Question

Note this is not a duplicate of How to extend an existing JavaScript array with another array, without creating a new array? because I'm looking to have a nested array, not to simply extend an array with another array to result into 1 array. Please read the question properly before you mark this as duplicate.

I'm looping through rows in a (Google) sheet to collect values, and would like to add each row as array to an array, which should result in an output like this (simplified example to illustrate):

array_main = [[row1_cell1,row1_cell2,row1_cell3], [row2_cell1,row2_cell2,row2_cell3], ...]

I first tried this with .push, which adds the values, but not as array:

accounts_last_row = 10
accounts_array = []

for (var i = 0; i < accounts_last_row; ++i) {
  if ((accounts_range[i][1] == 'test') {
    accounts_array.push([ [accounts_range[i][1]],[accounts_range[i][2]] ])
  }
}

I'm aware similar questions have been asked, but most of them simply recommend using .concat to merge 2 arrays. I tried this as well but it doesn't add anything to the array:

...
if ((accounts_range[i][1] == 'test') {
    accounts_array.concat( [accounts_range[i][1]],[accounts_range[i][2]] )
  }
...

What am I missing? Thanks in advance.

Possible duplicate of How to extend an existing JavaScript array with another array, without creating a new array? — tehhowch
– tehhowch, Commented Aug 23, 2018 at 13:11
different use case, your link aims to have a single array (not nested) output. — tealowpillow
– tealowpillow, Commented Aug 23, 2018 at 13:31
what exact syntax are you proposing? If I simply use it as in the other question, my array stays empty: accounts_array.push.apply( [accounts_range[i][1],accounts_range[i][2]] ) — tealowpillow
– tealowpillow, Commented Aug 23, 2018 at 13:41
I wrote an answer which demonstrates what I intended you to get from the other question. — tehhowch
– tehhowch, Commented Aug 23, 2018 at 14:24

Serge insas · Accepted Answer · 2018-08-23 12:53:43Z

2

You almost had it, inner arrays are simple ones, you had too many brackets.

Try like this:

accounts_array.push( [accounts_range[i][1],accounts_range[i][2]] );

the code above will work to add rows. If you want to add data as a single column the you will have to change the brackets like this:

accounts_array.push( [accounts_range[i][1]],[accounts_range[i][2]] );

edited Aug 23, 2018 at 12:53

answered Aug 23, 2018 at 12:39

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

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

14 Comments

tealowpillow Over a year ago

thought so too and tried that as well, but still doesn't return the nested array. At leats that's what I'm seeing in the apps script log file. Could this be a problem with the apps script logger that it doesn't show the nested arrays? Log output is as follows: accounts_array: test,96145912,test,37275407,test,127772724 (I'm expecting accounts_array: [test,96145912],[test,37275407],[test,127772724]

Serge insas Over a year ago

Use JSON.Stringify(value) to see the structure. I'm sure this is the right way, use it all the time ;)

tealowpillow Over a year ago

Do you mean in the log or how do I use that in apps script?

mTv Over a year ago

This answer(accounts_array.push( [accounts_range[i][1],accounts_range[i][2]] );) should work. You must be doing something wrong somewhere else.

tealowpillow Over a year ago

that's why it's accepted :). The (normal) log still shows it as one array, that's were the confusion started. Tried the json log with all options but the most obvious one...

|

tehhowch · Accepted Answer · 2018-08-23 13:58:07Z

This type of operation can be done neatly with Array#filter and Array#push and apply:

const results = [];
const colIndexToTest = /** 0, 1, etc. */;
const requiredValue = /** something */;
SpreadsheetApp.getActive().getSheets().forEach(
  function (sheet, sheetIndex) {
    var matchedRows = sheet.getDataRange().getValues().filter(
      function (row, rowIndex) {
        // Return true if this is a row we want.
        return row[colIndexToTest] === requiredValue;
      });
    if (matchedRows.length)
      Array.prototype.push.apply(results, matchedRows);
  });
// Use Stackdriver to view complex objects properly.
console.log({message: "matching rows from all sheets", results: results});

The above searches the given column of all rows on all sheets for the given value, and collects it into a 2d array. If all rows are the same number of columns, this array would be directly serializable with Range#setValues.

This code could have used map instead of forEach and the push.apply, but that would place empty or undefined elements for sheet indexes that had no matches.

TheWizEd · Accepted Answer · 2018-08-23 14:47:49Z

0

I'm assuming if account-range[i][1] is 'test' copy the entire row to accounts_array. Drop the second index.

accounts_last_row = 10
accounts_array = []

for (var i = 0; i < accounts_last_row; ++i) {
  if ((accounts_range[i][1] == 'test') {
    accounts_array.push(accounts_range[i])
  }
}

answered Aug 23, 2018 at 14:47

TheWizEd

8,6542 gold badges14 silver badges23 bronze badges

Collectives™ on Stack Overflow

JS pushing arrays to array (concat) in apps script

3 Answers 3

14 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

14 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related