Convert the data from comma-separated cells into repeated rows

Question

I have a Google Spreadsheet where the first column is multi-valued (not all rows though) and also a few other columns. Not all columns are multi-valued. The values are separated by ','

Here is an example:

10001a, 10001b | 8904083514008, 8904083512400 | DAILY NEEDS | ATTA | 24 MANTRA ORGANIC | WHOLE WHEAT ATTA | 24 MANTRA ORGANIC WHOLE WHEAT ATTA | 1 KG, 10LB | 3.99, 7.99 

Would like to transform such a row into multiple rows and removing the original row. So the above one would be transformed as follows (-1 row +2 rows):

10001a | 8904083514008 | DAILY NEEDS | ATTA | 24 MANTRA ORGANIC | WHOLE WHEAT ATTA | 24 MANTRA ORGANIC WHOLE WHEAT ATTA | 1 KG | 3.99 

10001b | 8904083512400 | DAILY NEEDS | ATTA | 24 MANTRA ORGANIC | WHOLE WHEAT ATTA | 24 MANTRA ORGANIC WHOLE WHEAT ATTA | 10LB | 7.99 

How can this be done with Apps Script?

Answer 1

So, the logic is: processing a data row, split the entries by commas where possible. Then output as many new rows as there are parts in the first column's entry. For those entries that are not comma-separated lists, the same value is repeated.

The way I wrote the script, its output is placed in the same sheet, replacing the original data. This can be changed by editing the last line with getRange.

The split method is split(/, */), allowing for whitespace after commas, but not requiring it.

function splitRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var output = [data[0]];    // same header row 
  for (var i = 1; i < data.length; i++) {
    var parts = data[i].map(function(value) {
      return typeof value == 'string' ? value.split(/, */) : [value];
    });
    for (var k = 0; k < parts[0].length; k++) {
      var row = [];
      for (var j = 0; j < data[0].length; j++) {
        row.push(parts[j].length > 1 ? parts[j][k] : parts[j][0]);
      }
      output.push(row);
    }
  }
  sheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}

Answer 2

I just answer here if somebody will look for not the same as asked here but a little bit simple task. I also had a task split comma separated column into multiple rows, I could find the original answer here

And formula is =SPLIT('All Nations'!I1,",",FALSE)

Hope will help somebody