如何预填充Google表单复选框？ [英] How to prefill Google form checkboxes?
问题描述
我看过这个问题是否可以使用谷歌电子表格中的数据预填谷歌表单？，并且答案中提供的代码（感谢Mogsdad）适用于文本类型Google表单问题。我的问题是：
是否可以预先填写复选框类型的Google表单问题？例如，如果我现有的电子表格中包含名称条目，并且其中一个条目是Fred，Barney，则可以通过编码，在名称复选框下勾选Fred和Barney的复选框来填充表单，然后填写表单问题？
谢谢，
Greig
对于大多数其他类型，每个响应的基本模式都可以重复使用。例如，这适用于多选：
item =项[I] .asMultipleChoiceItem（）;
var respItem = item.createResponse（resp）;
然而，复选框可能会非常棘手，因为它可能有一个项目，多个项目，甚至其他的回应。当响应被记录到电子表格中时，它将显示为逗号分隔的字符串;当在一个表单提交事件（例如在一个触发函数中）中接收到数据时，我们得到一个数组（...，其中所有响应都在数组中的第一个项目中，用逗号分隔的字符串）。 checkboxItem的 createResponse（）方法需要一个有效选择的数组......所以我们可以用一点javascript魔术来提供它：
item = items [i] .asCheckboxItem（）;
//响应是一个CSV字符串，需要数组
var respArray = resp.split（/ *，* /）;
var respItem = item.createResponse（respArray）;
编辑： CheckboxItems和MultipleChoiceItems与其他选项一起使用时。这些其他选项是允许的，但是在预填充的URL中显示不正确，因此它们不会显示在显示的表单中。请参阅问题4454 。
以下是是否有可能预填充谷歌形式使用谷歌电子表格中的数据？，更新处理列表，多项选择和复选框回复。此版本更一般，它可以适应电子表格中的标题。 BONUS ：如果您添加标有预填写网址的列，该脚本将在其中写入其生成的网址。
/ **
*使用表单API生成预填充表单URL
*
* https://stackoverflow.com/a/26395487/1677912
* /
函数evenBetterBuildUrls（）{
var ss = SpreadsheetApp.getActive（）;
var sheet = ss.getSheetByName（Form Responses 1）;
var data = ss.getDataRange（）。getValues（）; //预填充数据
var headers = data [0]; //表单标题==表单标题（问题）
var formUrl = ss.getFormUrl（）; //使用表单附加到工作表
var form = FormApp.openByUrl（formUrl）;
var items = form.getItems（）;
var urlCol = headers.indexOf（预填充网址）; //如果有这样一列标记，我们将更新它
//跳过标题，然后为Sheet1中的每一行创建URL。
for（var row = 1; row< data.length; row ++）{
Logger.log（根据电子表格为row =+ row生成预填充网址）;
//通过电子表格信息构建响应。
var response = form.createResponse（）;
for（var i = 0; i< items.length; i ++）{
var ques = items [i] .getTitle（）; //获取项目
的问题文本var quesCol = headers.indexOf（ques）; //获取包含此问题的col索引
var resp = ques？ data [row] [quesCol]：;
var type = items [i] .getType（）。toString（）;
Logger.log（Question ='+ ques +'，resp ='+ resp +'type：+ type）;
//需要将每种类型的答案视为其特定类型。
switch（items [i] .getType（））{
case FormApp.ItemType.TEXT：
var item = items [i] .asTextItem（）;
休息;
case FormApp.ItemType.PARAGRAPH_TEXT：
item = items [i] .asParagraphTextItem（）;
休息;
case FormApp.ItemType.LIST：
item = items [i] .asListItem（）;
休息;
case FormApp.ItemType.MULTIPLE_CHOICE：
item = items [i] .asMultipleChoiceItem（）;
休息;
case FormApp.ItemType.CHECKBOX：
item = items [i] .asCheckboxItem（）;
//在表单提交事件中，resp是一个包含CSV字符串的数组。加入1个字符串。
//在电子表格中，只需要CSV字符串。转换为单独的选择数组，准备好createResponse（）。
if（typeof resp！=='string'）
resp = resp.join（'，'）; //将数组转换为CSV
resp = resp.split（/ *，* /）; //将CSV转换为数组
break;
case FormApp.ItemType.DATE：
item = items [i] .asDateItem（）;
resp = new Date（resp）;
resp.setDate（resp.getDate（）+1）;
休息;
case FormApp.ItemType.DATETIME：
item = items [i] .asDateTimeItem（）;
resp = new Date（resp）;
休息;
默认值：
item = null; //未处理DURATION，GRID，IMAGE，PAGE_BREAK，SCALE，SECTION_HEADER，TIME
break;
}
//将此答案添加到我们的预填充网址
if（item）{
var respItem = item.createResponse（resp）;
response.withItemResponse（respItem）;
}
//否则如果我们有其他类型的响应，我们将跳过它
else Logger.log（跳过i =+ i +，question =+ ques +类型：+型）;
}
//为此行生成预填充的URL
var editResponseUrl = response.toPrefilledUrl（）;
//如果存在预填充URL列，则更新它
if（urlCol> = 0）{
var urlRange = sheet.getRange（row + 1，urlCol + 1） .setValue（editResponseUrl）;
}
}
};
I have looked at the question "Is it possible to 'prefill' a google form using data from a google spreadsheet?" and the code provided in the answer (thanks Mogsdad) works well for text type Google form questions. My question is: Is it possible to prefill a checkbox type Google form question?
For example, if I have an existing spreadsheet with an entry for "Names" and one of the entries is "Fred, Barney" would it be possible, via coding, to have a form prefill with the checkboxes ticked for "Fred" and "Barney" under a "Names" checkbox type Google form question?
Thanks, Greig
The basic pattern for each response can be repeated for most other types. For example, this works for multiple choice:
item = items[i].asMultipleChoiceItem();
var respItem = item.createResponse(resp);
However, a checkbox can be tricky, as it may have one item, multiple items, and even "other" responses. When the response is recorded to your spreadsheet, it will appear as a comma-separated string; when received in a form submission event (e.g. in a trigger function), we get an array (... where all responses are in the first item in the array, in a comma-separated string). The createResponse() method for a checkboxItem expects an array of valid choices... so we can provide that with a little javascript magic:
item = items[i].asCheckboxItem();
// Response is a CSV string, need array
var respArray = resp.split(/ *, */);
var respItem = item.createResponse(respArray);
EDIT: Google has a bug with CheckboxItems and MultipleChoiceItems, when used with "Other" options enabled. Those "other" options are allowed, but get rendered incorrectly in the pre-filled URL, and as a result they don't appear in the displayed form. Please see and star Issue 4454.
Here's an updated version of the function from Is it possible to 'prefill' a google form using data from a google spreadsheet?, updated to handle lists, multiple choice, and checkbox responses. This version is more general, it can adapt to the headings in your spreadsheet. BONUS: if you add a column labeled "Prefilled URL", the script will write its generated URLs there.
/**
* Use Form API to generate pre-filled form URLs
*
* https://stackoverflow.com/a/26395487/1677912
*/
function evenBetterBuildUrls() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("Form Responses 1");
var data = ss.getDataRange().getValues(); // Data for pre-fill
var headers = data[0]; // Sheet headers == form titles (questions)
var formUrl = ss.getFormUrl(); // Use form attached to sheet
var form = FormApp.openByUrl(formUrl);
var items = form.getItems();
var urlCol = headers.indexOf("Prefilled URL"); // If there is a column labeled this way, we'll update it
// Skip headers, then build URLs for each row in Sheet1.
for (var row = 1; row < data.length; row++ ) {
Logger.log("Generating pre-filled URL from spreadsheet for row="+row);
// build a response from spreadsheet info.
var response = form.createResponse();
for (var i=0; i<items.length; i++) {
var ques = items[i].getTitle(); // Get text of question for item
var quesCol = headers.indexOf(ques); // Get col index that contains this question
var resp = ques ? data[row][quesCol] : "";
var type = items[i].getType().toString();
Logger.log("Question='"+ques+"', resp='"+resp+"' type:"+type);
// Need to treat every type of answer as its specific type.
switch (items[i].getType()) {
case FormApp.ItemType.TEXT:
var item = items[i].asTextItem();
break;
case FormApp.ItemType.PARAGRAPH_TEXT:
item = items[i].asParagraphTextItem();
break;
case FormApp.ItemType.LIST:
item = items[i].asListItem();
break;
case FormApp.ItemType.MULTIPLE_CHOICE:
item = items[i].asMultipleChoiceItem();
break;
case FormApp.ItemType.CHECKBOX:
item = items[i].asCheckboxItem();
// In a form submission event, resp is an array, containing CSV strings. Join into 1 string.
// In spreadsheet, just CSV string. Convert to array of separate choices, ready for createResponse().
if (typeof resp !== 'string')
resp = resp.join(','); // Convert array to CSV
resp = resp.split(/ *, */); // Convert CSV to array
break;
case FormApp.ItemType.DATE:
item = items[i].asDateItem();
resp = new Date( resp );
resp.setDate(resp.getDate()+1);
break;
case FormApp.ItemType.DATETIME:
item = items[i].asDateTimeItem();
resp = new Date( resp );
break;
default:
item = null; // Not handling DURATION, GRID, IMAGE, PAGE_BREAK, SCALE, SECTION_HEADER, TIME
break;
}
// Add this answer to our pre-filled URL
if (item) {
var respItem = item.createResponse(resp);
response.withItemResponse(respItem);
}
// else if we have any other type of response, we'll skip it
else Logger.log("Skipping i="+i+", question="+ques+" type:"+type);
}
// Generate the pre-filled URL for this row
var editResponseUrl = response.toPrefilledUrl();
// If there is a "Prefilled URL" column, update it
if (urlCol >= 0) {
var urlRange = sheet.getRange(row+1,urlCol+1).setValue(editResponseUrl);
}
}
};
这篇关于如何预填充Google表单复选框？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
