I came across this script that is a custom formula:
function result(range) {
var splitCol = 1; // split on column B
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var s = range[i][splitCol].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 == splitCol) {
output1.push(s[j]);
} else {
output1.push(range[i][k]);
}
}
output2.push(output1);
}
}
return output2;
}
The script/custom formuled worked like a charm for one column, but I couldn't get this to work with multiple columns.
I tried something that seems correct to me but the script didin't work.
function result(range) {
var splitCol = 17;
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var s = range[i][splitCol].split(",");
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 == splitCol) {
output1.push(s[j]);
} else {
output1.push(range[i][k]);
}
}
output2.push(output1);
}
}
var splitCol2 = 18;
var output3 = [];
for(var ii=0, iiLen=range.length; ii<iiLen; ii++) {
var ss = range[ii][splitCol2].split(",");
for(var jj=0, jjLen=s.length; jj<jjLen; jj++) {
var output4 = [];
for(var kk=0, kkLen=range[0].length; kk<kkLen; kk++) {
if(kk == splitCol2) {
output4.push(ss[jj]);
} else {
output4.push(range[ii][kk]);
}
}
output3.push(output4);
}
}
var outputs = output2, output3;
return outputs;
}
