Combining arrays to use in Google Sheets

Question

I'm having some trouble combining arrays into one array and then flipping it with the code below. The goal is to set an array of values in one column of a spreadsheet. After the titleArray function, I would like to have an array spread across multiple columns on one row. Then, using a function I found in another Stack Overflow thread (Google Spreadsheet Script - How to Transpose / Rotate Multi-dimensional Array?), I want to flip the array so it is spread across multiple rows on one column. The current code is returning an array for each element in array1 all inside of another array. Then, the transposeArray function is combining values from each array, 1 with 1, 2 with 2, and so on. Can anyone spot what I'm doing wrong here?

var titleArray = function(){
    var output = [];
    for(i=0; i < array1.length; i++){
          if(array1[i].length > 0){   
            var titles = ["",array1[i] + " Data"].concat(array2, array3, array4, array5, array6, array7);
            output.push(titles);
          }
        }
        return(output);
      }
    }

      
    function transposeArray(array){
     var result = [];
     for (var col = 0; col < array[0].length; col++) { // Loop over array cols
       result[col] = [];
        for (var row = 0; row < array.length; row++) { // Loop over array rows
          result[col][row] = array[row][col]; // Rotate
         }
        }
       return result;
      }
    transposeArray(titleArray);

What happens with last line change to `transposeArray(titleArray());`? — Martin Ernst, Nov 21 '14 at 14:31
Each value is paired with corresponding values in the other array. After the first function, I see something like this... [[,array1value1,array1value2,array1value3],[array2value1,array2value2,array2value3]] Then after the second function, I see something like this... [[, ],[array1value1,array2value1],[array1value2,array2value2],[array1value3,array2value3]] — Calvin Gaunce, Nov 21 '14 at 14:36

score 4 · Accepted Answer · answered Nov 21 '14 at 15:54

I was able to sit down with a programmer and he helped me through this. Essentially, I needed to use the concat method instead of the push method. I tried using concat early in this process but was using it in the first way demonstrated below instead of the second. I've included the full new code below as well.

output.concat(titles);
output = output.concat(titles);

var titleArray = function(){
    var output = [];
    for(i=0; i < array1.length; i++){
          if(array1[i].length > 0){   
            var titles = ["",array1[i] + " Data"].concat(array2, array3, array4, array5, array6, array7);
            ouput = output.concat(titles);
          }
        }
        return(output);
      }
    }

      
    function transposeArray(array){
     var result = [];
     for (var col = 0; col < array[0].length; col++) { // Loop over array cols
       result[col] = [];
        for (var row = 0; row < array.length; row++) { // Loop over array rows
          result[col][row] = array[row][col]; // Rotate
         }
        }
       return result;
      }
    transposeArray([titleArray]);

I ran into this problem. thanks Calvin. I want to note for anyone who stumbles upon this, that it is CRITICAL that inside the for loop you "var foo" if you later will do bar.concat(foo) in that same array. i was doing everything mentioned just i had declared foo outside the for loop. — Jtello, Feb 04 '18 at 06:01

Combining arrays to use in Google Sheets

1 Answers1