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?](https://stackoverflow.com/questions/1374126/how-to-extend-an-existing-javascript-array-with-another-array-without-creating) — tehhowch, Aug 23 '18 at 13:11
different use case, your link aims to have a single array (not nested) output. — tealowpillow, Aug 23 '18 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, Aug 23 '18 at 13:41
I wrote an answer which demonstrates what I intended you to get from the other question. — tehhowch, Aug 23 '18 at 14:24

Serge insas · Accepted Answer · 2018-08-23T12:53:43.380

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 '18 at 12:53

answered Aug 23 '18 at 12:39

Serge insas

45,904
7
105
131

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] – tealowpillow Aug 23 '18 at 12:44
Use JSON.Stringify(value) to see the structure. I'm sure this is the right way, use it all the time ;) – Serge insas Aug 23 '18 at 12:46
Do you mean in the log or how do I use that in apps script? – tealowpillow Aug 23 '18 at 12:50
Logger.log(JSON.Stringify(value)) – Serge insas Aug 23 '18 at 12:55
I assume by 'value' you mean the 'accounts_array'? It results in an error: TypeError: Cannot find function Stringify in object [object JSON]. – tealowpillow Aug 23 '18 at 13:02
I'm typing this on a phone, please use control+space on your keyboard to activate the autocomplete feature. JSON. and at the dot press CTRL SPACE you will see all the available methods with the right spelling. Sorry about that – Serge insas Aug 23 '18 at 13:05
You should not be using `Logger` to look at complex data! Either pause at a breakpoint or use `console` with Stackdriver: https://stackoverflow.com/questions/51582449/utilities-parsecsvcsv-also-splits-on-commas/51582684#51582684 – tehhowch Aug 23 '18 at 13:09
@tealowpillow the correct spelling has no Capital in 'stringify' indeed, stupid phone... @ tehhowch that's also a manner indeed, but it's mainly a question of habit. – Serge insas Aug 23 '18 at 13:24
thanks, stringify works now, but confirms that it's still one array (instead of nested): [18-08-23 14:27:59:281 BST] [["test"],[96145912],["test"],[37275407],["test"],[127772724]] – tealowpillow Aug 23 '18 at 13:28
2

This answer(`accounts_array.push( [accounts_range[i][1],accounts_range[i][2]] );`) should work. You must be doing something wrong somewhere else. – mTv Aug 23 '18 at 13:44
What you are showing is obviously an array of arrays, aka nested arrays... Write it to a sheet to verify it works fine. – Serge insas Aug 23 '18 at 13:46
@Sergeinsas yes it's an array of arrays, but not what I am aiming for. I'm aiming for this: [["test",96145912],["test",37275407],["test",127772724]] – tealowpillow Aug 23 '18 at 13:50
got it! the damn brackets... accounts_array.push([accounts_range[i][1],accounts_range[i][2]]) – tealowpillow Aug 23 '18 at 13:51
1

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... – tealowpillow Aug 23 '18 at 13:56

tehhowch · Answer 2 · 2018-08-23T13:58:07.640

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.

score 0 · Answer 3 · answered Aug 23 '18 at 14:47

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])
  }
}

JS pushing arrays to array (concat) in apps script

3 Answers3