Sheets: Transpose Single Column to Multiple Rows

Question

I'm trying to take a row of data in Google Sheets and transpose it into multiple rows in a new worksheet. See example spreadsheet. Each row should have the filenames for one product. The products can be distinguished by the unique hex product ID (which is the first four characters of the filename).

I assume that if this can be done it will be done with a combination of ARRAYFORMULA and TRANSPOSE, but I'm wondering if it can actually be done with standard Sheets formulas at all.

The list of filenames I would be pasting in could always have blank rows between products, so any formula/script could either use the blank rows or the 4-character ID to determine when to create a new row.

JSDBroughton JSDBroughton · Accepted Answer · 2017-06-12T15:46:38

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.

Sheets: Transpose Single Column to Multiple Rows

2 Answers