The sheet has multiple rows joined by ";" and I need to expand the strings and also keep the table IDs.
| ID | Column X: Joined Rows |
|---|---|
| 01 | a;bcdfh;345;xyw... |
| 02 | aqwx;tyuio;345;xyw... |
| 03 | wxcv;gth;2364;x89... |
function expand_joins(range) {
var output2 = [];
for(var i = 0, iLen = range.length; i < iLen; i++) {
var s = range[i][1].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 == 1) {
output1.push(s[j]);
} else {
output1.push(range[i][k]);
}
}
output2.push(output1);
}
}
return output2;
}
Desirable Output: two columns
| ID | Output |
|---|---|
| 01 | a |
| 01 | bcdfh |
| 01 | 345 |
| 01 | xyw |
| 01 | ... |
| 02 | aqwx |
UPDATED
If you're using the V8 runtime (new IDE) you can try the following:
function expandJoins(range) {
return range
.map(row => row[1]
.split(';')
.map(splitItem => [row[0], splitItem])
)
.flat();
}