Google-sheets – Split multiple line cells from various column and duplicate the surrounding rows

google sheetsgoogle-apps-script

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

Best Answer

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.

Related Topic