Google Spreadsheet Split Based on newline

Question

I am trying to split a cell that has multiple values delimited by a new line, it works perfectly for cells that have more than 1 value, but if I get a cell with just 1 value (ie, no newline character it errors out) how can I solve this?

function splitColumnAndRepeatRows(anArray, splitColumnIndex) 
{
var output = [];
for (i in anArray)
{ // for each row
var splitArray = anArray[i][splitColumnIndex].split("\n"); // split values in specified column
for (j in splitArray)
 { // for each split cell value
  if(splitArray[j]=="")
    continue;
  var row = anArray[i].slice(0); // take a copy of source row 
  //row[splitColumnIndex] = alltrim(splitArray[j]); // replace comma separated value with current split value
  row[splitColumnIndex] =splitArray[j];
   output.push(row); // push new row to output
 }
} 
return output;
}

link to spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AjA1J4T5598RdGRWd0p4Q3FtaW5QTG1MVVdEVUQ0NFE#gid=0

Adventure

Would be better if the posted spreadsheet didn't require users to request access - why should I give you my email address? – Rabbinate

sorry about that, but the problem was solved! thanks A MILLION!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! – Adventure

Answer 1

The error that's thrown is:

TypeError: Cannot find function split in object //OBJECT//. (line xx, file "xxxx")

The problem is that .split() is a String method. If your "single item" is text, you're fine - but if it's a number or date... boom!

Luckily, there's a .toString() in common for Number and Date, and it's a no-op for Strings. So you can just add it to this line:

var splitArray = anArray[i][splitColumnIndex].toString().split("\n"); // split values in specified column

You're using the wrong version of for for indexing arrays. It works, but you really should be using:

for (var i=0; i < anArray.length; i++) {
  ...
  for (var j=0; j < splitArray.length; j++) {
    ...
  }
  ...
}

You have another problem, although it's less likely. There are cases that will result in the loss of an entire line of input, due to your null-string test.

• blank "split" cell
• "split" cell with 1 or more linefeeds in it, but no text

To catch that, you just need to allow that ONE "split" item may be null:

if(splitArray[j]=="" && j>=1)
  continue;

Full Code

function splitColumnAndRepeatRows(anArray, splitColumnIndex) {
  var output = [];
  for (var i = 0; i < anArray.length; i++) { // for each row
    var splitArray = anArray[i][splitColumnIndex].toString().split("\n"); // split values in specified column
    for (var j = 0; j < splitArray.length; j++) { // for each split cell value
      if (splitArray[j] == "" && j >= 1)
        continue;
      var row = anArray[i].slice(0); // take a copy of source row 
      row[splitColumnIndex] = splitArray[j];
      output.push(row); // push new row to output
    }
  }
  return output;
}

Rabbinate

Answer 2

You can use built in function =SPLIT(D1;CHAR(10))

Selftaught

I don't know why this answer received a down-vote, as this solves the issue elegantly and answers the question succinctly without meddling with a custom function. Your answer was very helpful to me, please take my up-vote and hope you keep contributing to SO – Hustle

this is the better answer – Telford

@timpeterson "Better"? That depends on the context... if the need is to operate from a script, the built-in is not accessible. The original question was not clear about where the function would be invoked from. – Rabbinate

Still, this is the better answer for 95% of people finding this question who just want to split a column by newlines, as can also be judged by the number of upvotes vs. the accepted answer ;) – Polyadelphous

While the original question is tagged "google-apps-script", the title understandably leads readers to think it's referring to the built-in split() function. – Certainly

Answer 3

I couldn't get the Split by CHAR(10) to work, so I used a regex-replace before the split. You don't need to use "@". Any character that won't be found in your data can work. So to explain. The REGEXREPLACE replaces new line ("\n") with "@", and then the SPLIT splits by "@".

=SPLIT(REGEXREPLACE(D1;"\n";"@"); "@"))

Cheers Mads

Minneapolis