Splitting comma-separated data from cell into multiple new rows without duplicating data

Question

I have a list of data with thousands of entries that I need to import to a management system for a google sheets file. Currently I have something like this:

	A	B	C
1	Dermazone	Medical Center	Dermal, laser hair removal, massage
2	3Dental	Clinic	Orthodontics, General Practitioner, Prosthodontics

I want to achieve something like this:

	A	B	C
1	Dermazone	Medical Center	Dermal
2			Laser Hair Removal
3			Massage
4	3Dental	Clinic	Orthodontics
5			General Practitioner
6			Prosthodontics

I have researched around and I found this script

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    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 == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

However I have two issues:

1. I don't want duplicate entries for other data in the row (I only want my C column to be split into new rows)
2. This script doesn't work for a lot of data (I have around 12k rows to apply this to)

Please assist me in solving these issues, I appreciate any help. Thanks!

Answer 1

In your situation, how about the following sample script?

Sample script:

From your script, I thought that you might want to use the function as the custom function. So, in this modification, please copy and paste the following script to the script editor of Spreadsheet and put a custom function of =result(A1:C2) to a cell.

function result(values) {
  return values.flatMap(([a, b, c]) => c.split(",").map((e, i) => i == 0 ? [a, b, e.trim()] : [null, null, e.trim()]));
}

• If you want to use this script by running with the script editor, please use the following script.

    function result() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var srcSheet = ss.getSheetByName("Sheet1"); // Please set the source sheet name.
      var dstSheet = ss.getSheetByName("Sheet2"); // Please set the destination sheet name.
      var values = srcSheet.getDataRange().getValues();
      var res = values.flatMap(([a, b, c]) => c.split(",").map((e, i) => i == 0 ? [a, b, e.trim()] : [null, null, e.trim()]));
      dstSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
    }
  

Testing:

When the above script is run, the following result is obtained.

Reference:

• map()