Splitting sheet text using an array to mantain unique ID

Question

I'm using this: How to transpose and split in Google Apps Script?

Which works great in the first part (I use it to copy data an split it) but then I would need to redo it since I have 2 different separators, first time ";" second time ",".

The issue and I'm guessing it's more JS related than anything else, is that if I use the same for it splits the 2nd column vertically. I'll post examples.

If I use it again to reformat it gives this:

I would like it to be split into Column B and C.

I figured it was because the for loop only pushes 2 rows, but I can't solve adding a third.

Answer 1

Issue:

If I understood it correctly, you have something like this:

And you would like to end up with something like this:

---

Solution:

The following code will do just that, splitting (and putting into several lines) the values in the first image by ;, and then separating the quantity from the product name (accomplished by splitting the ,).

function myFunction() {
  
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Sheet1");

  var range = sheet.getRange(2,1,sheet.getLastRow()-1,2);
  var values = range.getValues();

  var valuesToInput = [];

  for(var i = 0; i<values.length; i++){
    var productList = values[i][1].split(";");
    
    for(var j = 0; j < productList.length; j++){
      var productVariables = productList[j].split(",");
      var productQuantity = productVariables[0];
      var productName = productVariables[1];

      valuesToInput.push([values[i][0], productQuantity, productName]);
    }
  }

  var rangeToInput = sheet.getRange(2,1,valuesToInput.length, 3);
  rangeToInput.setValues(valuesToInput);
}