1

may I know if there is a way that I could split multiline cells from various columns in google sheet then duplicate the surrounding rows. I am new in this kind of environment so I am really confused.

I have several columns, some of which have cells with multiple data separated by comma. I tried app scripting but only it works for 1 column.

I want to post the code, unfortunately I am out of office right now.

Attached is the raw data.

I hope someone or anyone could help me. Thank you so much.

here is the sample file. https://docs.google.com/spreadsheets/d/1-MVjxKWMo0IFTGBrFqLzfP9JU44iVruEtbZ-OcUQYKo

  • 1
    Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Feb 22 at 12:17
  • I already find a solution to this issue. – Xan Mei Mar 5 at 17:03
  • 1
    Great to hear that. Could you please post an answer with your solution so others can benefit as well? – marikamitsos Mar 5 at 18:50
  • Sure.I’ll do that when I reached office. – Xan Mei Mar 6 at 3:44
  • As mentioned before it would be nice if you would share your solution. – marikamitsos May 30 at 8:30
1

Here is the solution I've used on my problem, which might not be the best since I am not really familiar with Google script.

In order to get the desired result, use this formula on the sheet you want the split result to be stored: =result(Import!A2:AD). Import is the sheet connected to the Google form.

function result(range) {
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
   var q = range[i][5].split(","); 
   var r = range[i][6].split(","); 
   var t = range[i][7].split(","); 
   var u = range[i][8].split(","); 
   var s = range[i][12].split(","); 
   var v = range[i][15].split(",");
   var w = range[i][18].split(",");
   var x = range[i][21].split(",");
   var y = range[i][24].split(",");

    for(var j=0, jLen=v.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == 5) 
        {
          output1.push(q[j]);
        }
        else if(k==12)
        {
          output1.push(s[j]);
        }
        else if(k==6)
        {
          output1.push(r[j]);
        }
        else if(k==7)
        {
          output1.push(t[j]);
        }
        else if(k==8)
        {
          output1.push(u[j]);
        }
        else if(k==15)
        {
          output1.push(v[j]);
        }
        else if(k==18)
        {
          output1.push(w[j]);
        }
        else if(k==21)
        {
          output1.push(x[j]);
        }
        else if(k==24){
          output1.push(y[j]);
        }
        else 
        {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

k represents the column of the sheet.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.