Google Forms stores checkbox answers in one cell: Sheets, Gmail, Docs. Pivot tables and COUNTIF can't count that. splitMultiSelectToRows(values, colIndex, delimiter) takes a 2D array with a header row and returns one row per selected option, copying the other columns.
When to use
- Counting how many people picked each checkbox option
- Turning "Topics of interest" into one row per topic
- Any delimited cell you want to pivot
How to use this snippet
/**
* Explode a delimited column into one row per value (header row kept).
* Blank cells keep their row unchanged.
*
* @param {Array<Array<*>>} values 2D array including header row
* @param {number} colIndex 0-based column to split
* @param {string} [delimiter=',']
* @return {Array<Array<*>>}
*/
function splitMultiSelectToRows(values, colIndex, delimiter) {
delimiter = delimiter || ',';
var out = [values[0]];
for (var r = 1; r < values.length; r++) {
var row = values[r];
var parts = String(row[colIndex] == null ? '' : row[colIndex]).split(delimiter)
.map(function (s) { return s.trim(); }).filter(String);
if (!parts.length) { out.push(row.slice()); continue; }
parts.forEach(function (p) {
var copy = row.slice();
copy[colIndex] = p;
out.push(copy);
});
}
return out;
}
Example
var rows = [
['Name', 'Topics'],
['Ada', 'Sheets, Gmail'],
['Bo', '']
];
splitMultiSelectToRows(rows, 1);
// → [['Name','Topics'], ['Ada','Sheets'], ['Ada','Gmail'], ['Bo','']]
Tips:
- Forms joins checkbox answers with
,. If an option itself contains a comma, rename it in the Form or pass a different delimiter. - Write the result to a separate tab. Don't overwrite the response sheet the Form writes to.
Happy Coding!
