Looping and output in Google Apps Script

Question

I am using 2 sheets, rawData and processedData.

rawData looks like this:

Status Title Options
Live Title1 Option1, Option2, Option3, Option4
Live Title2 Option1, Option2, Option3, Option4, Option5
Live Title3 Option1, Option2, Option3

processedData should look like this:

Status Title Options
Live Title1 Option1
empty Title1 Option2
empty Title1 Option3
empty Title1 Option4
Live Title2 Option1
empty Title2 Option2
empty Title2 Option3
empty Title2 Option4
empty Title2 Option5
Live Title3 Option1
empty Title3 Option2
empty Title3 Option3

The emptyis supposed to be empty, didn't know how to leave it blank to represent an empty cell.

I tried using this code, but I can't get the desired output with the loops.

function formatData() {

  // File
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Get rawData sheet
  var rawData = ss.getSheetByName('rawData');

  // Input data
  var data = rawData.getRange("A2:C").getValues(); // Gets titles and options in a single call to the sheet

  // Initialise an array that will hold the output
  var outputArray = [];

  // Name a variable to hold the data from each set of options
  var options;

  var status;

  // Start looping through the data
  for (var row = 0; row < data.length; row++) {

    status = data[row][0];

    outputArray.push([status]);

    // Split the options into an array: "Option1, Option2, Option3" ---> [Option1, Option2, Option3]
    options = data[row][1].split(", ");

    // Loop through the array of split options and place each of them in a new row
    for (var element = 0; element < options.length; element++) {

      outputArray.push([data[row][0], // Place the title in a new row
                        options[element]]); // Place one option in the 2nd column of the row

    } // Options loop ends here

  } // Data loop ends here

  // Get processedData sheet
  var processedData = ss.getSheetByName('processedData');

  // Get last row in processedData sheet
  var lastRow = processedData.getLastRow();

  // Post the outputArray to the sheet in a single call
  processedData.getRange(lastRow + 1, 1, outputArray.length, outputArray[0].length).setValues(outputArray);
}

I'm a total beginner in this language, sorry for asking a probably very basic question.

Answer 1

How about this modification? Please think of this as just one of several modifications.

Pattern 1:

Modified script:

When your script is modified, it becomes as follows. Please modify the for loop as follows.

for (var row = 0; row < data.length; row++) {
  status = data[row][0];
  var title = data[row][1]; // Added
//    outputArray.push([status]); // Removed
  options = data[row][2].split(", "); // Modified
  for (var element = 0; element < options.length; element++) {
    if (element == 0) { // Added and modified
      outputArray.push([status, title, options[element]]);
    } else {
      outputArray.push(["", title, options[element]]);
    }
  }
}

Pattetn 2:

As another pattern, how about the following modification? When you use this, please modify as follows.

Modified script:

From:

// Initialise an array that will hold the output
var outputArray = [];

// Name a variable to hold the data from each set of options
var options;

var status;

// Start looping through the data
for (var row = 0; row < data.length; row++) {

  status = data[row][0];

  outputArray.push([status]);

  // Split the options into an array: "Option1, Option2, Option3" ---> [Option1, Option2, Option3]
  options = data[row][1].split(", ");

  // Loop through the array of split options and place each of them in a new row
  for (var element = 0; element < options.length; element++) {

    outputArray.push([data[row][0], // Place the title in a new row
                      options[element]]); // Place one option in the 2nd column of the row

  } // Options loop ends here

} // Data loop ends here

To:

var outputArray = data.reduce(function(ar1, e) {
  var h1 = e.splice(0, 1)[0];
  var h2 = e.splice(0, 1)[0];
  var options = e[0].split(", ");
  return ar1.concat(options.reduce(function(ar2, f, j) {
    if (f) ar2.push(j == 0 ? [h1, h2, f] : ["", h2, f]);
    return ar2;
  }, []));
}, []);

• The result is the same with the pattern 1.

Note:

• In your script, it seems that the header of "Status, Title, Options" is not copied. How about this?

Reference:

• reduce()

If I misunderstood your question and this was not the result you want, I apologize.