How to split single rows into multiple rows depending on whether one cell has values separated by commas or new lines in Google Sheet?

Question

The spreadsheet has some cells with cell contents that are separated by commas, or a new line.

123, 876, 456

Column "C" is the column that determines whether a row should be split up into multiple rows.

EXAMPLE Spreadsheet

Information from the Form goes into the "Form Submission" page.

We have a specific format that we must meet to submit to our report tracking software that requires the issue numbers (found in Column C) to be separated into their own rows with the information found in Columns A:B, D:J remaining the same (see Desired Outcome sheet).

I found a similar question and we implemented it into our Google Sheets.

This script requires, on a separate sheet, the function =result('FormSubmission'!A2:J) to be placed in the first column / row that we wish the data to be displayed (see "Current Outcome" sheet, Cell A2.)

Here is the coding that we are using:

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

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

If someone submits multiple issue numbers separated by commas in the form, the row needs to be split up into multiple rows, as shown in the Desired Outcome sheet.

Answer 1

Here is some code that I tested, and it works. It also works for cells that have both new lines, and comma separated values. It does not required that the range be passed in. . . . . . Don't need that for this code. It writes the new rows directly to the 'Current Outcome' sheet.

function result() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var frmSubmissionSheet = ss.getSheetByName('Form Submission');
  var desiredOutcomeSheet = ss.getSheetByName('Current Outcome');

  var data = frmSubmissionSheet.getRange(1, 1, frmSubmissionSheet.getLastRow(), frmSubmissionSheet.getLastColumn()).getValues();

  var issueNumbers = "",
      hasComma = false,
      arrayOfIssueNumbers = [],
      arrayRowData = [],
      thisRowData,
      hasNewLine;

  for (var i=0;i<data.length;i+=1) {
    if (i===0) {continue}; //Skip row 1

    issueNumbers = data[i][2];
    hasComma = issueNumbers.indexOf(",") !== -1;
    hasNewLine = issueNumbers.indexOf("\n") !== -1;
    Logger.log(hasNewLine)
    if (!hasComma && !hasNewLine) {
      desiredOutcomeSheet.appendRow(data[i]);
      continue;  //Continue to next loop, there are no multiple issue numbers
    };

    if (hasNewLine) {
      var arrayNewNewLine = issueNumbers.split("\n");//Get rid of new line
      issueNumbers = arrayNewNewLine.toString(); //Back to string.  Handles cells with both new line and commas
    };

    arrayOfIssueNumbers = [];
    arrayOfIssueNumbers = issueNumbers.split(",");

    for (var j=0;j<arrayOfIssueNumbers.length;j+=1) {
      arrayRowData = []; //Reset
      thisRowData = [];

      thisRowData = data[i];
      for (var k=0;k<thisRowData.length;k+=1) {
        arrayRowData.push(thisRowData[k]);
      };

      arrayRowData.splice(2, 1, arrayOfIssueNumbers[j]);
      desiredOutcomeSheet.appendRow(arrayRowData);
    };
  };
};

Answer 2

In addition to Sandy's contribution, here is some alternative code:

function extract(range, colToSplit, delimiter) {
var resArr = [], row;
range.forEach(function (r) {
    r[colToSplit-1].replace(/(?:\r\n|\r|\n)(\d|\w)/g,", ").split(delimiter)
        .forEach(function (s) {
            row = [];
            r.forEach(function (c, k) {               
                row.push( (k === colToSplit-1) ? s.trim() : c);
            })
            resArr.push(row);
        })
})
return resArr;

}

This is a custom function that takes three arguments:

1. the range
2. the column on which the splitting should be based
3. the delimiter to split by

can be used in the spreadsheet like so:

=extract('Form Submission'!A1:J, 3, ", ")

The code subsitutes all new line characters (that are followed by a digit or a letter) to comma's and splits (based on column 3) using the comma as a delimiter.

I hope that helps ?