Google spreadsheet division based on new row

Question

I am trying to split a cell with multiple values ​​separated by a new line, it works fine for cells that have more than one value, but if I get a cell with one value (i.e. no newline character is an error) 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 the table: https://docs.google.com/spreadsheet/ccc?key=0AjA1J4T5598RdGRWd0p4Q3FtaW5QTG1MVVdEVUQ0NFE#gid=0

Answer 1

The error you selected:

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

The problem is that .split() is a String method. If your “only element” is text, you're fine, but if it's a number or date ... an arrow!

Fortunately, .toString() exists for numbers and dates, and no-ops for strings. Therefore, you can simply add it to this line:

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

You are using the wrong version of for to index arrays. It works, but you really should use:

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

You have another problem, although it is less likely. There are cases that will lead to the loss of the entire input line due to your test with a zero line.

• empty cell "split"
• "split" with 1 or more lines in it, but without text

To catch this, you just need to allow one split element to be empty:

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

Answer 2

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