Split strings in multiple columns into rows

I am currently working on a Google Sheet and I want to split a few columns that have strings into rows across the entire worksheet.

I used a script found on this platform which was helpful but the data came out with some errors. Wonder if someone could help me solve this.

I am very new to javascript.

Original Data looks like this. enter image description here

I would like it to look like this. enter image description here

However, it turns out like this, where it duplicates some of the texts. I've highlighted it in red. enter image description here

Here's the script that I used and the link to a sample worksheet.

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;
}

1 answer

  • answered 2021-11-23 02:33 Tanaike

    When you want to achieve your goal using Google Apps Script, in your situation, how about the following sample script?

    Sample script:

    const result = range =>
      range.flatMap(([a, ...v]) => {
        const { vv, len } = v.reduce((o, c) => {
          const t = c.split(",");
          o.vv.push(t);
          o.len = o.len < t.length ? t.length : o.len;
          return o;
        }, { vv: [], len: 0 });
        const temp = vv.map(e => e.concat(Array(len - e.length).fill("")));
        return temp[0].map((_, c) => [a, ...temp.map(r => r[c])]);
      });
    

    Result:

    When this script is used by putting a formula of =RESULT(Data!A:E), the following result is obtained.

    From:

    enter image description here

    To:

    enter image description here

    References:

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum