Split Comma Separated Values in a String against ID with Google Sheets

Question

I have some data that lives in a Google Sheet and is connected to Google Data Studio. Some of this data is comma separated within a string. Above is a link to a sample of some of this data.

I’ll try to explain the best I can but this video of how it’s resolved using Power Query in Excel is EXACTLY what I am trying to do, only in Google Sheets. Instructional Video on YouTube

In the above sample sheet, I have a respondent ID in column A in the ‘Data’ tab and I have the comma separated string in column B of the ‘Data’ tab.

In the ‘Solution’ tab in column B you’ll see I used the following code to split and transpose the contents of column B from the ‘Data’ tab to the ‘Solution’ tab. =transpose(split(join(" ",arrayformula(Data!B2:B&",")),",")) which worked well for splitting out the values into their own rows within that column

But what I need is the ID to be mapped against the respective values in column A in the ‘Solution’ tab. For the life of me, I cannot figure this out.

Any help would be appreciated. Thank you!

Update:

I also found this script for a custom function that I think will work but I am not sure how to get this to work. I get an error saying “TypeError: cannot read property length...”

function myFunction(range) {
delimiter = ", ";
targetColumn = 1;
targetColumn2 = 2;

var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][targetColumn].split(delimiter);    
    var s2 = range[i][targetColumn2].split(delimiter);
    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 == targetColumn) {
          output1.push(s[j]);
       } 
       else if (k == targetColumn2) {
           output1.push(s2[j]);
       } else {
            output1.push(range[i][k]);
       }
    }
    output2.push(output1);
  } 
 }
return output2;
}

I also found this script for a custom function webapps.stackexchange.com/questions/106027/… But I can’t get it to work. — Jason Nockels

Alessandro Alessandro · Accepted Answer · 2020-05-08T10:39:29

Approach

I would use a custom function here:

/**
* @customfunction
*/
function covid(range) {
  let delimiter = ", ";
  let newRows = [];
  for (let i in range) {
    let row = range[i];
    let id = row[0];
    let strings = row[1].split(delimiter); // split the responses
    for (let j in strings) {
      newRows.push([id, strings[j]]); // links the responder id to its splitted response
    }
  }
  return newRows; // prints the rows
}

The input parameter is a A1 Notation range in your case it would be: =covid(Data!A2:B383)

Split Comma Separated Values in a String against ID with Google Sheets

3 Answers

Approach

spreadsheet demo