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

Answer 1

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

Answer 2

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