Google sheets checkbox results as new rows

Question

We have checkboxes on a google form which feeds into a google sheet. Responses to checkboxes come comma separated and we are wondering if it's possible to have as new rows.

This is how the form populates the sheet:

[Col A] [Col B]
Name   Availability  
Larry  Monday  
Sergey Monday, Wednesday  
Sonali Thursday, Friday

This is how we would like it:

[Col A] [Col B] 
Name   Availability  
Larry  Monday  
Sergey Monday  
Sergey Wednesday  
Sonali Thursday  
Sonali Friday

Any ideas would be much appreciated, thanks!

JPV JPV · Accepted Answer · 2016-02-12T07:06:03

A little while ago I wrote a custom function in google apps script to deal with an identical situation. Here is the script.

/** 
* Splits the array by commas in the column with given index, by given delimiter
* @param {A2:B20}  range Range reference
* @param {2}  colToSplit Column index
* @param {","}  delimiter Character by which to split
* @customfunction
*/


function advancedSplit(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;
}

You can use the script as any other (built-in) formula by entering in the spreadsheet (assuming for example that the column to split is column D)

=advancedSplit(A1:Z, 4, ",")

I hope this helps ?

Google sheets checkbox results as new rows

2 Answers