Google-sheets – Split comma-separated cell in sheets while repeating other cell for all entries

google sheets

I have a Sheet that looks like this:

╔═══════╦═════════════════════════════════════╦══╗
║  ID   ║                Users                ║  ║
╠═══════╬═════════════════════════════════════╬══╣
║ 12345 ║ bob@domain.com,su'uzan@domain.com   ║  ║
║ 99999 ║ steven@domain.com,rachel@domain.com ║  ║
╚═══════╩═════════════════════════════════════╩══╝

It's two column, one with an ID and one with comma-separated lists of emails. I need to look like this:

╔═══════╦════════════════════╦══╗
║  ID   ║       Users        ║  ║
╠═══════╬════════════════════╬══╣
║ 12345 ║ bob@domain.com     ║  ║
║ 12345 ║ su'uzan@domain.com ║  ║
║ 99999 ║ steven@domain.com  ║  ║
║ 99999 ║ rachel@domain.com  ║  ║
╚═══════╩════════════════════╩══╝

I've tried using pivot tables and some query operations but haven't been able to crack it. There's roughly 6,000 entries in the Sheet now. I also wasn't able to get anything from this question to work (I need something automatic, there's too much data to do manually.) This question also seems similar but is unanswered.

Best Answer

I modified the excellent code provided by Jacob Jan Tuinstra to fit your needs assuming your data starts at A2.

Step 1

Open the Script editor from your sheet menu and paste this code:

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

Step 2

Paste anywhere (D1 or G1 or...) this formula: =result(A2:B5)

Step 3

Done

...wasn't able to get anything from this answer to work I need something automatic...

I believe it doesn't get more automatic.

If for any reason you cannot get it to work, please share the link to a test sheet in a comment so we can work it out.

Code

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

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: multi-line cells into new rows.

Add the script under Tools > Script editor.
Save the script, the name doesn't matter.
Use the custom function in a cell just like a built in (see the screenshot above).

Google-sheets – How to modify this Google script to pull the email from column E in the Google sheet

You could use a for loop and iterate down Column E, which is column 5. Then insert the cell values into an array.

var data = s.getDataRange().getValues();
//var data = s.getRange(1,5,s.getLastRow(),1).getValues();
var emails = [];

for (var row in data) {
    var cell_value = data[row][5];
    emails.push(cell_value);
}

for (var e in emails) {
    // TODO: Perform operations on emails[]
}