1

I am trying to create a list of tabs based on values in a row, as I have previously done with values in a column (The script here is an example test as I was trying to identify the issue, not the actual script used). However, the forEach() function is working differently and I do not understand why. Below I will append two sets of scripts and their results.

For column:

  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(1,1,5,1);
  var STlist = [];

  range.getValues().forEach(function(x){
    Logger.log(x + " why");
    STlist.push(x);
  });

  Logger.log(STlist);  
}

Output for column

[20-02-28 12:10:58:012 HKT] z why
[20-02-28 12:10:58:018 HKT] a why
[20-02-28 12:10:58:021 HKT] b why
[20-02-28 12:10:58:023 HKT] c why
[20-02-28 12:10:58:026 HKT] d why
[20-02-28 12:10:58:029 HKT] [[z], [a], [b], [c], [d]]

For Row

  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(1,1,1,5);
  var STlist = [];

  range.getValues().forEach(function(x){
    Logger.log(x + " why");
    STlist.push(x);
  });

  Logger.log(STlist);  
}

Output for row

[20-02-28 12:11:35:013 HKT] z,x,d,v,g why
[20-02-28 12:11:35:018 HKT] [[z, x, d, v, g]]

Would it be possible for the row format to work the same as the column format? As I was expecting to see the same results but I got different results.

Thank you

Santosh Aryal
  • 1,276
  • 1
  • 18
  • 41
edmund chan kei yun
  • 111
  • 7
  • In one you have a 5-element array where each element is itself a 1-element array. In the other you have a 1-element array in which the element is a 5-element array. In both cases you received a multidimensional array. – tehhowch Feb 28 '20 at 04:20
  • but in case number 2, the why is only appended to the last element in the array, not every element. – edmund chan kei yun Feb 28 '20 at 04:22
  • That's because `+` operator implicitly converts the row array to a string. – TheMaster Feb 28 '20 at 05:42

1 Answers1

2

To put in spreadsheet terms, the values array always contain rows and each of the rows contain column elements. In both cases,

range.getValues().forEach(function(x){

The x will be a row. And row will be a array with column elements:

A1:A5: [[z], [a], [b], [c], [d]]
A1:E1: [[z, x, d, v, g]]

A1:A5 has 5 rows and each of those rows contain a column element. So, forEach(row=>) will iterate 5 rows. A1:E1 contain 1 row and therefore forEach(row=>) will iterate 1 row. To iterate the elements in each row, you'd need another for-loop:

const arr1 = [["a1","b1"]];
const arr2 = [["a1"],["a2"]];
const arr3 = [["a1","b1"],
                   ["a2","b2"]];
const st = JSON.stringify
const loop_ = (arr) => 
  arr.forEach((row, i) => 
    row.forEach((colEl, j) => 
      console.info(`This array is ${st(arr)} 
            row is ${st(row)} 
            Current column is ${st(colEl)}
            col Element at index [${i},${j}] in this array is ${colEl}`)));

[arr1,arr2,arr3].forEach(loop_)

References:

Community
  • 1
  • 1
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Note: The above code will not run on deprecated ES5 engine. To use the code, [upgrade to modern faster V8 engine](https://developers.google.com/apps-script/guides/v8-runtime#enabling_the_v8_runtime). – TheMaster Feb 28 '20 at 05:35
  • 1
    Hi, TheMaster, thank you for your explanation, now i understand:) Are you able to include some comments on how each line work? As i would really like to understand the purpose of each line of code, and i am not familiar with JSON.stringify and how the arrows work, are they the same as forEach? – edmund chan kei yun Mar 03 '20 at 00:31
  • @edmund JSON.stringify just converts the array to string. It's not needed. I used it to show the array as string in the console logs. `=>` is the same as `function(){}`. It is the same ``forEach`` just written shortly. You can refer the official Mozilla documentation for arrow functions. – TheMaster Mar 03 '20 at 04:09
  • Ah, thank you for explaining, I was wondering if this was supposed to convert the 2D array into 1D, and just realized that this was a proofing code, thank you. – edmund chan kei yun Mar 04 '20 at 00:28
  • 1
    @edmund To convert try `.flat()` – TheMaster Mar 04 '20 at 04:49
  • Hi TheMaster, thank you for the suggestion, it worked well ^^ thank you – edmund chan kei yun Mar 06 '20 at 04:07