Google表格复选框结果为新行 [英] Google sheets checkbox results as new rows

问题描述

我们在Google表单上具有复选框，该复选框可以输入到Google表格中.对复选框的响应以逗号分隔，我们想知道是否有可能添加新行.

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  

这就是我们想要的:

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

任何想法将不胜感激，谢谢！

Any ideas would be much appreciated, thanks!

推荐答案

不久前，我在google apps脚本中编写了一个自定义函数来处理相同的情况.这是脚本.

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;
}

您可以通过在电子表格中输入脚本来将该脚本用作其他任何(内置)公式(例如，假设要拆分的列为D列)

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表格复选框结果为新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！