0
289
ViewsSplitting sheet text using an array to mantain unique ID
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.
1 answers
Answer question0
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);
}