Split strings in multiple columns into rows

Question

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

When I tested your script, unfortunately, I cannot replicate your sample current output situation from your sample input situation. I apologize for this. So can I ask you about the detailed flow for replicating your issue? And, how do you run your script? Can you explain the detail of it using your sample input image? — Tanaike
– Tanaike, Commented Nov 23, 2021 at 2:04
Hi @Tanaike, thank you for your fast response. Here's a link to show you what I meant. I'd highlighted the errors in the 'Result' sheet. docs.google.com/spreadsheets/d/… — Phronimos
– Phronimos, Commented Nov 23, 2021 at 2:21
Pls share a spreadsheet. People are most likely to not help when there is a lot of data involved but only a screenshot is attached. It is like asking them to create a sheet which they need to fill in by themselves (copy the data of your screenshot) then find the solution. Please make it easy for people to help you. — Mike Steelson
– Mike Steelson, Commented Nov 23, 2021 at 2:22
This can be done by a formula, but I don't understand the relation between input and ouput! — Mike Steelson
– Mike Steelson, Commented Nov 23, 2021 at 2:23
Hi @MikeSteelson, thanks for the advice. I'm new here. Will edit the question and add in the link. — Phronimos
– Phronimos, Commented Nov 23, 2021 at 2:23

Tanaike · Accepted Answer · 2021-11-23 02:33:46Z

1

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:

To:

References:

answered Nov 23, 2021 at 2:33

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Phronimos Over a year ago

Hi @Tanaike, it works! Thank you so much! Is there a way I can expand this so that it applies to more than just 4 columns? I noticed I can go all the way up to column G but not beyond.

Phronimos Over a year ago

Yes, I can do that but how do I close it? I've tried looking for a 'close question' tab.

Collectives™ on Stack Overflow

Split strings in multiple columns into rows

1 Answer 1

Sample script:

Result:

From:

To:

References:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

Result:

From:

To:

References:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related