Split multi-line cells into different rows

Question

I have a script with a function which runs through a range of rows in a Google spreadsheet and splits multi-line cells in column F into multiple rows. It then proceeds to copy down the cells in other columns of the same row to the newly created rows. Following is an example of what I mean.

The range through which the function runs is this:

The function runs and produces the following:

As you can see, in the first image, some cells in column F have more than one line. The function detects those, splits them into as many rows as there are line breaks and then copies the remaining cells in the other columns. For both "John Doe" and "Candice Len", column F has multi-line entries and, as such, both of these names got as many rows as addresses in column F after the function ran.

The function I am using is as follows: (column F has index 5 in the function)

    function result(range) {
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][5].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == 5) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

This is working fine. However, I would like the function to do the same for other columns beyond column F. So, if I have other columns with multi-line entries, I would like the function to run through the rows, split the multi-line cells in as many rows as there are line breaks and copy the content of the cells in the remaining columns to the newly created rows. Below is an example where both column E and F have multi-line entries.

If I have something like this:

I would want the function to turn it into this:

In this case, it's important to note that each row would have to have the same number of line breaks for each multi-line cell as the different lines would have to map against the corresponding lines in different columns. In other words, taking the last two screenshots as an example, in the "John Doe" entry, "San Francisco" would have to end up in the same row as "address1" and "Cincinnati" in the same row as "address2". The same for "Washington", "Phoenix", "address1" and "address2", for the "Candice Len" entries.

Columns E and F are just an example. The script would have to run through multi-line cells ins more than two columns.

How would I be able to change the script in order for it to be able to do the same for additional columns and not just column F?

Thank you very much.

Answer 1

How about this sample script? I think that there are some methods for achieving what you want. So please think of this as one of them. In order to achieve your situation, I separated the script to 3 parts.

Flow :

1. Parse each cell.
   • This can be seen at c1 in the script.
2. Create each cell using parsed cells.
   • This can be seen at c2 in the script.
3. Create output cells.
   • This can be seen at c3 in the script.

Sample script :

// Main of this script.
function result(range) {
  var output = [];
  for (var i in range) {
    var celLen = 1;
    var c1 = range[i].map(function(e, i){
      var cell = e.toString().split("\n"); // Modified
      var len = cell.length;
      if (len == 1) {
        return cell[0];
      } else if (len > 1) {
        celLen = celLen > len ? celLen : len;
        var t2 = [];
        for (var k=0; k<cell.length; k++) {
          t2.push(cell[k]);
        }
        return t2;
      }
    });
    var c2 = c1.map(function(e, i){
      var r = [];
      if (!Array.isArray(e)) {
        for (var k=0; k<celLen; k++) {
          r.push(e);
        }
      } else {
        for (var k in e) {
          r.push(e[k]);
        }
        if (e.length < celLen) {
          for (var m=0; m<celLen - e.length; m++) {
            r.push("");
          }
        }
      }
      return r;
    });
    var c3 = c2[0].map(function(e, i){return c2.map(function(f, j){return c2[j][i]})});
    Array.prototype.push.apply(output, c3);
  }
  return output;
}

// For testing this script.
function main() {
  var ss = SpreadsheetApp.getActiveSheet();
  var data = ss.getDataRange().getValues();
  var r = result(data);
  ss.getRange(ss.getLastRow() + 1, 1, r.length, r[0].length).setValues(r);
}

Sample result :

Input :

Output :

Note :

• In this sample script, you can increase the columns freely.
• In order to parse each value of cells, \n is used.
• I'm not sure about other patterns for your situation. So I prepared this sample script for supposing some situations. Please confirm the sample result.

If I misunderstand your question, please tell me. I would like to modify it.