如何根据所附电子表格中的列自动填充表单选项 [英] How to auto populate form options based on a column in the attached spreadsheet
本文介绍了如何根据所附电子表格中的列自动填充表单选项的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我创建了一个新的Google表格来收集用户信息.在工作表脚本编辑器中，我创建了以下表单: https://script.google.com/macros/s/AKfycbz6u4Q4>
I created a new google sheet for collecting user information. In my sheet script editor, I created this form: https://script.google.com/macros/s/AKfycbz6uGmXfSiXg4lYseeX0IN7Qv_9eM4eN9knUtBm5Co/exec
上面的表单有一个选择题，我需要使用所附电子表格中的特定列自动填充
The above form has a multiple choice question that I need to auto populate using a specific column in the attached spreadsheet
我在下面的代码中使用了他，但是由于某种原因，更新功能未读取表单选项或其他内容
I used he below code, but for some reason the update function is not reading the form options or something
当我运行更新表单函数时，它显示"TypeError:在对象中找不到函数getItemById"
When I run the update form function, it gives "TypeError: Cannot find function getItemById in object"
有什么解决方案可以通过工作表列更新表单选项
Is there any solution to update the form options by the sheet column
function doGet() {
return HtmlService.createTemplateFromFile('form.html')
.evaluate() // evaluate MUST come before setting the Sandbox mode
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function updateForm(){
// call your form and connect to the drop-down item
var form = ("https://script.google.com/macros/s/AKfycbz6uGmXfSiXg4lYseeX0IN7Qv_9eM4eN9knUtBm5Co/exec");
var namesList = form.getItemById("890244015").asListItem();
// identify the sheet where the data resides needed to populate the drop-down
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("CHOICES");
// grab the values in the first column of the sheet - use 2 to skip header row
var namesValues = names.getRange(2, 16, names.getMaxRows() - 1).getValues();
var studentNames = [];
// convert the array ignoring empty cells
for(var i = 0; i < namesValues.length; i++)
if(namesValues[i][0] != "")
studentNames[i] = namesValues[i][0];
// populate the drop-down with the array data
namesList.setChoiceValues(studentNames);
}
这是form.html代码:
This is the form.html code:
<!DOCTYPE html>
<html>
<body>
<style>
body {
padding: 0px 0px 0px 0px;
margin: 0px 0px 0px 0px;
background-color: white;
}
</style>
<link href='/static/forms/client/css/3145455273-
mobile_formview_st_ltr.css' type='text/css' rel='stylesheet' media='screen
and
(max-device-width: 721px)'>
<div class="ss-form-container">
<div class="ss-header-image-container"><div class="ss-header-image-image">
<div class="ss-header-image-sizer"></div></div></div>
<div class="ss-top-of-page"><div class="ss-form-heading"><h1 class="ss-
form-title" dir="ltr">Test</h1>
<div class="ss-form-desc ss-no-ignore-whitespace" dir="ltr">WELD DATE
00</div>
<div class="ss-required-asterisk" aria-hidden="true" id="Required">*
Required</div></div></div>
<br><br>
<div class="ss-form">
<script type="text/javascript">var submitted=false;</script>
<iframe name="hidden_iframe" id="hidden_iframe" style="display:none;"
onload="if(submitted)
{window.location='https://sites.google.com/site/formredirection/';}">
</iframe>
<form action="https://docs.google.com/forms/d/e/formid/formResponse"
method="post" target="hidden_iframe"
onsubmit="submitted=true;">
<div class="ss-form-question errorbox-good" role="listitem">
<div dir="auto" class="ss-item ss-item-required ss-select"><div class="ss-
form-entry">
<label class="ss-q-item-label" for="entry_890244015"><div class="ss-q-
title">JOINT
<label for="itemView.getDomIdToLabel()" aria-label="(Required field)">
</label>
<span class="ss-required-asterisk" aria-hidden="true">*</span></div>
<div class="ss-q-help ss-secondary-text" dir="auto"></div></label>
<select name="entry.890244015" id="entry_890244015" aria-label="JOINT "
aria-required="true" required=""><option value=""></option>
<option value="OPTION 01">OPTION 01</option> <option value="OPTION
02">OPTION 02</option> <option value="OPTION 03">OPTION 03</option>
<option value="OPTION 04">OPTION 04</option></select>
</div></div></div>
<div class="ss-form-question errorbox-good" role="listitem">
<div dir="auto" class="ss-item ss-item-required ss-radio"><div class="ss-
form-entry">
<label class="ss-q-item-label" for="entry_117174731"><div class="ss-q-
title">REP NO.
<label for="itemView.getDomIdToLabel()" aria-label="(Required field)">
</label>
<span class="ss-required-asterisk" aria-hidden="true">*</span></div>
<div class="ss-q-help ss-secondary-text" dir="auto"></div></label>
<ul class="ss-choices" role="radiogroup" aria-label="REP NO. "><li
class="ss-choice-item">
<label><span class="ss-choice-item-control goog-inline-block"><input
type="radio" name="entry.735659431" value="" id="group_735659431_1"
role="radio" class="ss-q-radio" aria-label="" required="" aria-
required="true"></span>
<span class="ss-choice-label"></span>
</label></li></ul>
<div class="error-message" id="117174731_errorMessage"></div></div></div>
</div>
<div class="ss-form-question errorbox-good" role="listitem">
<div dir="auto" class="ss-item ss-item-required ss-date"><div class="ss-
form-entry">
<label class="ss-q-item-label" for="entry_1715668372"><div class="ss-q-
title">WELD DATE
<label for="itemView.getDomIdToLabel()" aria-label="(Required field)">
</label>
<span class="ss-required-asterisk" aria-hidden="true">*</span></div>
<div class="ss-q-help ss-secondary-text" dir="auto"></div></label>
<input type="date" name="entry.1715668372" value="" class="ss-q-date"
dir="auto" id="entry_1715668372" aria-label="WELD DATE " aria-
required="true" required="">
</div></div></div>
<div class="ss-form-question errorbox-good" role="listitem">
<div dir="auto" class="ss-item ss-item-required ss-select"><div class="ss-
form-entry">
<label class="ss-q-item-label" for="entry_1048274308"><div class="ss-q-
title">WELDER
<label for="itemView.getDomIdToLabel()" aria-label="(Required field)">
</label>
<span class="ss-required-asterisk" aria-hidden="true">*</span></div>
<div class="ss-q-help ss-secondary-text" dir="auto"></div></label>
<select name="entry.1048274308" id="entry_1048274308" aria-label="WELDER
" aria-required="true" required=""><option value=""></option>
<option value="WR 01">WR 01</option> <option value="WR 02">WR 02</option>
<option value="WR 03">WR 03</option> <option value="WR 04">WR 04</option>
</select>
</div></div></div>
<div class="ss-form-question errorbox-good" role="listitem">
<div dir="auto" class="ss-item ss-item-required ss-select"><div class="ss-
form-entry">
<label class="ss-q-item-label" for="entry_1712008875"><div class="ss-q-
title">WPS
<label for="itemView.getDomIdToLabel()" aria-label="(Required field)">
</label>
<span class="ss-required-asterisk" aria-hidden="true">*</span></div>
<div class="ss-q-help ss-secondary-text" dir="auto"></div></label>
<select name="entry.1712008875" id="entry_1712008875" aria-label="WPS "
aria-required="true" required=""><option value=""></option>
<option value="WPS 01">WPS 01</option> <option value="WPS 02">WPS
02</option> <option value="WPS 03">WPS 03</option> <option value="WPS
04">WPS 04</option></select>
</div></div></div>
<input type="hidden" name="draftResponse" value="
[null,null,"-8333688893315580231"]
">
<input type="hidden" name="pageHistory" value="0">
<input type="hidden" name="fbzx" value="-8333688893315580231">
<div class="ss-send-email-receipt" style="margin-bottom: 4px;" dir="ltr">
<label for="emailReceipt" style="display:inline;"></label></div>
<input type="submit" name="submit" value="Submit" id="ss-submit"
class="jfk-button jfk-button-action "></form></div></div>
</body>
</html>
推荐答案
考虑使用<?=?> 和< ;??> 脚本
这使您可以访问HTML文件中的Apps SCript功能.
Consider using <?= ?> and <? ?> scriptlets
This allows you to access Apps SCript functionalities within the HTML file.
.gs文件
function doGet() {
return HtmlService
.createTemplateFromFile('index')
.evaluate();
}
function createInnerHTML(){
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("CHOICES");
var namesValues = names.getRange(2, 16, names.getMaxRows() - 1).getValues();
return namesValues;
}
HTML文件
<body>
...
<? var namesValues= createInnerHTML(); ?>
<div>
<select name="entry.890244015" id="entry_890244015" aria-label="JOINT " aria-required="true" required="">
<option value=""></option>
<option value="OPTION 01"><?= namesValues[0][0]?></option>
<option value="OPTION 02"><?= namesValues[1][0]?></option>
<option value="OPTION 03"><?= namesValues[2][0]?></option>
<option value="OPTION 04"><?= namesValues[3][0]?></option>
</select>
</div>
...
</body>
如果您的HTML框架是预先确定的，这是一个简单的解决方案(您预先知道下拉菜单将有4个选项).
This is an easy solution if your HTML frame is predetermined (you know in advance that your dropdown menu will have 4 options).
.gs文件
function doGet() {
return HtmlService
.createTemplateFromFile('index')
.evaluate();
}
function createInnerHTML()
{
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("CHOICES");
var namesValues = names.getRange(2, 16, names.getMaxRows() - 1).getValues();
var InnerHTML = [];
for (var i=0;i<namesValues.length;i++){
InnerHTML.push('<option value="OPTION '+(i+1)+'>' + namesValues[i][0]+ '</option>';
});
InnerHTML.join('');
return InnerHTML;
}
HTML文件
<body>
...
<? var innerHTML= createInnerHTML(); ?>
<div>
<select name="entry.890244015" id="entry_890244015" aria-label="JOINT " aria-required="true" required="">
<option value=""></option>
//HERE the inner HTML will be inserted
<?= innerHTML?>
</select>
</div>
...
</body>
这篇关于如何根据所附电子表格中的列自动填充表单选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
