Google-sheets – Problem using the TRANSPOSE() function

So, the logic is: processing a data row, split the entries by commas where possible. Then output as many new rows as there are parts in the first column's entry. For those entries that are not comma-separated lists, the same value is repeated.

The way I wrote the script, its output is placed in the same sheet, replacing the original data. This can be changed by editing the last line with getRange.

The split method is split(/, */), allowing for whitespace after commas, but not requiring it.

function splitRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var output = [data[0]];    // same header row 
  for (var i = 1; i < data.length; i++) {
    var parts = data[i].map(function(value) {
      return typeof value == 'string' ? value.split(/, */) : [value];
    });
    for (var k = 0; k < parts[0].length; k++) {
      var row = [];
      for (var j = 0; j < data[0].length; j++) {
        row.push(parts[j].length > 1 ? parts[j][k] : parts[j][0]);
      }
      output.push(row);
    }
  }
  sheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}

One way to make it really automatic would be to stick the CSV someplace online. Then you could do something like . . .

=TRANSPOSE(IMPORTDATA("http://woodwardtw.github.io/test/comma.csv"))

That way any time you changed the CSV source file the SS would change with it.

Alternately, I believe making a new sheet and simply setting a wide cell pattern referencing your raw data sheet will get you there. Putting the following on Sheet 2 flips the data from Sheet 1 into columns. You might need to set your end cell to something more aggressive outer bound. Example here.

=transpose(Sheet1!A1:Z20)