Google Apps Script: Create new rows for cells that contain commas

0
votes

Background: Users will fill out a google form and separate their answers with commas. I would like to write a script that can separate cells into rows for each comma used; however, I need to do this for multiple columns.

For example, here are screenshots of the concept that I would want:

How it starts

End goal

And here is my actual data set: My sheet

I have columns A through S. I only want to look at columns D through L and check if they have commas. In the picture, you can see that the first three rows of information do not contain commas. I would just like the code to return those values as they are. In row 5, column D through L does contain commas that represent separate responses, and so I would like those values to be split into separate rows in the output.

This is what I have so far, that I found the bulk of from someone else on Stackoverflow. This code works but only if you specify one column.

For example, on a blank google sheet I write =split_rows(Sheet1!A:S,3), which only checks column 3 of my other sheet for commas. I need a code that will do the same but for several columns and split up the values.

function split_rows(anArray, splitColumnIndex) {
  var output = [];
  for (i in anArray){                                                 // for each row
    var current_cell = anArray[i][splitColumnIndex];                  // current cell in loop
    if (current_cell.toString().indexOf(",")>-1) {                    // if the cell in the designated column contains a comma,  
      var splitArray = current_cell.split(",");                       // split values in specified column
        for (j in splitArray){                                          // for each split cell value
         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
         output.push(row);                                             // push new row to output
       } 
    }

    else {
      var row = anArray[i].slice(0); 
      output.push(row);
    }
  }
      return output;
  }
 
function alltrim(str) {
  return str.replace(/^\s+|\s+$/g, '');
}

Here is an example of my sheet; https://docs.google.com/spreadsheets/d/1mgqKriAxf6-hAPNJ9Um8i9SD22TxDXVKM8llRn0sCS4/edit#gid=0

1
arraysgoogle-apps-scriptgoogle-sheets

1 Answers

0
votes
  • You want to obtain the output image of "End goal" from the input image of "How it starts".
  • You want to use the script as a custom function.

If my understanding is correct, how about this modification? In this modification, I used the following flow. I think that there are several answers for your situation. So please think of this as just one of them.

  1. Retrieve values.
  2. When the values of column "A", "B" and "C", split the values of column "B" and column "C" by ,.
  3. When the length of column "B" and "C" is the same, create 2 dimensional array by putting each value to each row.
    • From your input image, I supposed that the numbers of , for column "B" and "C" are the same.
  4. Put the converted values.

Modified script:

function split_rows(anArray) {
  var res = anArray.reduce(function(ar, e) {
    if (e.slice(0, 3).every(String)) {
      var colB = e[1].split(",").map(function(f) {return f.trim()});
      var colC = e[2].split(",").map(function(f) {return f.trim()});
      if (colB.length == colC.length) {
        var temp = colB.map(function(g, k) {
          return [e[0], g, colC[k]];
        });
        Array.prototype.push.apply(ar, temp);
      }
    }
    return ar;
  }, []);
  return res;
}

Result:

When the values of your input image are used, the result is as follows.

enter image description here

Note:

  • I prepared this modified script using your input and output images.
  • Although you use the range of Sheet1!A:S, it seems that there are no values from column "D" to "S"i n your input image. So in this modified script, the values from column "A" to "C" are used.
    • In you want to use the values from column "D" to "S", can you provide the information of about the converted result?

References:

If this was not the result you want, please tell me. I would like to modify it. At that time, can you provide the detail information you want?

Edit:

For your 2nd question, above script was modified using your shared Spreadsheet.

Modified script:

function split_rows2(anArray) {
  var res = anArray.reduce(function(ar, e) {
    var splitted = e.slice(3, 12).map(function(f) {return f.toString().split(",").map(function(g) {return g.trim()})});
    var temp = [];
    for (var j = 0; j < splitted[0].length; j++) {
      temp.push(e.slice(0, 3).concat(splitted.map(function(f) {return f[j] ? (isNaN(f[j]) ? f[j] : Number(f[j])) : ""})).concat(e.slice(12, 20)));
    }   
    Array.prototype.push.apply(ar, temp);
    return ar;
  }, []);
  return res;
}

Result:

enter image description here