0

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?

2

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);
}
0

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

  • If it's not about what was asked here, it shouldn't be an answer to this question 🤔 – arieljannai Jun 10 '18 at 8:17
  • @arieljannai I have mentioned it above, that it's not the same case but little bit simple option. Question also on google spreadsheet, it's also comma separated data and also need to put in rows. I had this issue I found this question, so thought that someone else could find it useful. Sure I can remove this question but then someone could not find this answer at all – sambua Jun 10 '18 at 20:11

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.