Google Spreadsheet Script - How to Transpose/Rotate Multi-Dimensional Array

How to transpose and split in Google Apps Script?

One way to solve this is to

1. Split the options values
2. and then build an output array with two columns and 13 (in this example) rows

Best practices suggest minimising calls to the sheet.

So the following variation of your script does all of the above:

function formatData() {

  // File
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Get rawData sheet
  var rawData = ss.getSheetByName('rawData');

  // Input data
  var data = rawData.getRange("A2:B").getValues(); // Gets titles and options in a single call to the sheet

  // Initialise an array that will hold the output
  var outputArray = [];

  // Name a variable to hold the data from each set of options
  var options;

  // Start looping through the data
  for (var row = 0; row < data.length; row++) {

    // Split the options into an array: "Option1, Option2, Option3" ---> [Option1, Option2, Option3]
    options = data[row][1].split(", ");

    // Loop through the array of split options and place each of them in a new row
    for (var element = 0; element < options.length; element++) {

      outputArray.push([data[row][0], // Place the title in a new row
                        options[element]]); // Place one option in the 2nd column of the row

    } // Options loop ends here

  } // Data loop ends here

  // Get processedData sheet
  var processedData = ss.getSheetByName('processedData');

  // Get last row in processedData sheet
  var lastRow = processedData.getLastRow();

  // Post the outputArray to the sheet in a single call
  processedData.getRange(lastRow + 1, 1, outputArray.length, outputArray[0].length).setValues(outputArray);
}

How to flatten and transpose an array in AppsScript

Transpose does not work with Array#map(), because the length could be not the wanted length.

Transpose with Array#forEach()

The forEach() method executes a provided function once per array element.

var range = [[0, 1], [2, 3], [4, 5]];
range = function (array) {    var r = [];    array.forEach(function (a, i) {        a.forEach(function (b, j) {            r[j] = r[j] || [];            r[j][i] = b;        });    });    return r;}(range);
document.write('<pre>' + JSON.stringify(range, 0, 4) + '</pre>');

Google Apps Script for copying an array from multiple sheets onto a single Status Summary sheet

try this:

function atAGlance() {
  var ss=SpreadsheetApp.getActive();
  var excl=['Status Summary'];
  var sh=ss.getSheetByName('Status Summary');
  var shts=ss.getSheets();
  var n=1;
  for(var i=0;i<shts.length;i++) {
    if(excl.indexOf(shts[i].getName())==-1){
      var v1=shts[i].getRange("M3:M10").getValues();
      var v2=transpose(v1);
      sh.getRange(1 + n++,1,v2.length,v2[0].length).setValues(v2);
    }
  }
}
//https://stackoverflow.com/a/16705104/7215091
function transpose(a)
{
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

Got the transpose from [https://stackoverflow.com/a/16705104/7215091]
(https://stackoverflow.com/a/16705104/7215091)

This version allows you to exclude sheets from the process and you can run it as shown below in the testAtAGlance() function. The example I was using excluded 'Status Summary' and 'Globals'.

function testAtAGlance() {
  atAGlance(['Status Summary','Globals']);
}

function atAGlance(excl) {
  var excl=excl || ['Status Summary'];
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Status Summary');
  var shts=ss.getSheets();
  var n=1;
  for(var i=0;i<shts.length;i++) {
    if(excl.indexOf(shts[i].getName())==-1){
      var v1=shts[i].getRange("M3:M10").getValues();
      var v2=transpose(v1);
      sh.getRange(1 + n++,1,v2.length,v2[0].length).setValues(v2);
    }
  }
}
//https://stackoverflow.com/a/16705104/7215091
function transpose(a)
{
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

Sheets: Transpose Single Column to Multiple Rows

As you say, this is very well suited to using the =TRANSPOSE function.

Using Range.getValues() will give a 2-dimensional array that is trivial to transpose in code. Like the answer here for example.

From there, set a write rage to match the dimensions of the transposed array and then Range.setValues()

edit

Custom function that repeats the desired result.

/**
 * Pivots the list of images by image key
 *
 * @param {Range} range The range of values. Assumes a column.
 * @return The range of results as array per image key
 * @customfunction
 */
function PIVOTIMAGES(range) {
  var imagesByKey = {};

  range
  .map(function(d){ return d[0]; }) // assumes single column
  .filter(function(d) { return d && d != ''; }) // filters out blank rows
  .forEach(function(d){   // arranges all images as an array per key
    var key = d.split('_')[0]; // assumes key is always XXX_
    if (!imagesByKey[key]) {
      imagesByKey[key] = [d];
    } else {
      imagesByKey[key].push(d);
    }
  });

  return Object.keys(imagesByKey).map(function(key) {
    return imagesByKey[key];
  }); 
}

This would need some work to deal with receiving multiple columns of information or information in a row not a column, but in principle, this is a simple solution.

I've added it to your example sheet. Use it as a formula =PIVOTIMAGES(A2:A10) for example.

Transpose Data row to Diagonal in Google Spreadsheet

Here's another way that goes back to your original post

=iferror(if(columns($A1:A1)>rows(A$1:A1),"",index(A$1:A$5,rows(A$1:A1)-columns($A1:A1)+1)),"")

Transposing a 2D-array in JavaScript

output = array[0].map((_, colIndex) => array.map(row => row[colIndex]));

map calls a provided callback function once for each element in an array, in order, and constructs a new array from the results. callback is invoked only for indexes of the array which have assigned values; it is not invoked for indexes which have been deleted or which have never been assigned values.

callback is invoked with three arguments: the value of the element, the index of the element, and the Array object being traversed. [source]

Transpose Diagonal to row in Google Spreadsheet?

If you use the exact range you mentioned (A1:D4) then this should work

=ARRAYFORMULA(TRIM(QUERY(IF(ROW(A1:D4)=COLUMN(A1:D4), A1:D4,),,ROWS(A1:A4))))

(No need to fill down.)

If you want to get the diagonal values of any range (here: B11:E14), try

=ARRAYFORMULA(TRIM(QUERY(IF(ROW(B11:E14)-ROW(B11)+1=COLUMN(B11:E11)-COLUMN(B11)+1, B11:E14,),,ROWS(B11:B14))))

Combining arrays to use in Google Sheets

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

---

Related Topics