2

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.

Michael Collins
  • 47
  • 7

2 Answers2

3

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.

JSDBroughton
  • 3,966
  • 4
  • 32
  • 52
  • Thanks for the response @Jonathon. Am I understanding correctly that is something I'd have to do with Scripts, not just formulas? I've only adapted other people's GoogleScripts, so I'm trying to figure out how to move forward on implementing. – Michael Collins Jun 12 '17 at 16:52
  • I wouldn't bother with scripts for this use case. Anytime formulas will do, they end up much clearer and easier to modify. – JSDBroughton Jun 12 '17 at 17:13
  • But can I use `Range.getValues()` and `Range.setValues()` in a formula? – Michael Collins Jun 12 '17 at 17:20
  • No. These were the API examples if you did want a script… Without sample data or a script you've tried there's not much more to say. – JSDBroughton Jun 12 '17 at 17:26
  • Sorry, still new to how this works. Is the example spreadsheet I linked to not sufficient sample data? – Michael Collins Jun 12 '17 at 18:00
  • that was my mistake - my phone didn't show the link to the example spreadsheet. I've added a custom function example of how to solve your specific issue. – JSDBroughton Jun 13 '17 at 08:00
  • Thanks. This is great! – Michael Collins Jun 22 '17 at 17:16
  • 1
    I made an update to this script so that it takes two parameters, one for the range and one for the number of columns in the resultant data. So instead of using a matching key to reset the row, it just counts each cell to find the new row. https://codepen.io/danielfowler/pen/JjrEVva – Daniel Fowler Dec 15 '21 at 01:26
2

I found an indirect solution. It's not very elegant, but it's the best I can figure out. See it in the third tab of the updated example spreadsheet. I added explanatory comments, too.

In case that example spreadsheet is eventually lost to time, here's a quick overview of the formulas I used for posterity.

In column A of a new worksheet I used the =left(Input!A1,4) formula to reproduce the first four characters of each filename in the original list.

Then I used =UNIQUE(FILTER(A:A,A:A<>"")) to create a deduped list of the product IDs from column A of the new worksheet.

Finally, I used the list of product IDs to build a FILTER formula that I could then transpose to get my desired results: =IF(ISBLANK(A1),"",TRANSPOSE(FILTER(Input!A$1:A,REGEXMATCH(Input!A$1:A,"^"&B1))))

The RegEx formula matches anything that starts (^) with the product ID in cell B1. I added an ISBLANK formula to avoid getting errors in empty rows.

Michael Collins
  • 47
  • 7