将HTML下拉菜单与Google表格上的Google Apps脚本一起使用 [英] Using an HTML drop-down menu with Google Apps Script on Google Sheets
问题描述
我正在Google表格上运行一项功能，该功能要求用户从（相当长的）选项列表中进行选择。由于不推荐使用UI服务，我想尝试使用HTML，但是对此一无所知。我需要弹出HTML用户界面，让用户从列表中选择一个名称，然后在将名称传递回apps脚本函数后消失。我尝试将一些代码拼凑在一起，但是似乎无法始终弹出下拉菜单，而且似乎无法弄清楚如何将选择发送回原始功能。
I am running a function on Google Sheets that requires a user to pick from a (rather lengthy) list of options. As UI service is deprecated, I thought I'd try with HTML, but I know nothing about this. I need the HTML User interface to pop up, have the user pick a name from the list, then go away, after passing the name back to the apps script function. I have tried to cobble together some code, but I can't seem to always get the drop-down menu to pop up, and I just can't seem to figure out how to send the choice back to the original function. Help?
function genDiscRep(){
var ss=SpreadsheetApp.getActive();
var dontTouch=ss.getSheetByName("Do Not Touch");
var studentNamesArrayLength=dontTouch.getLastRow()-1000+1
var studentNames=dontTouch.getRange(1000,3,studentNamesArrayLength,1).getValues();
var test=HtmlService.createHtmlOutputFromFile('index')
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
Browser.msgBox(test);
}
然后是我的html代码（为清楚起见，删除了大多数选择）
And then my html code (most choices removed for clarity)
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Select to Autocomplete</title>
<script src="jquery-1.11.1.min.js"></script>
<script src="jquery-ui.min.js"></script>
<script src="jquery.select-to-autocomplete.js"></script>
<script>
(function($){
$(function(){
$('select').selectToAutocomplete();
$('form').submit(function(){
alert( $(this).serialize() );
return false;
});
});
})(jQuery);
</script>
<link rel="stylesheet" href="jquery-ui.css">
<style>
body {
font-family: Arial, Verdana, sans-serif;
font-size: 13px;
}
.ui-autocomplete {
padding: 0;
list-style: none;
background-color: #fff;
width: 218px;
border: 1px solid #B0BECA;
max-height: 350px;
overflow-x: hidden;
}
.ui-autocomplete .ui-menu-item {
border-top: 1px solid #B0BECA;
display: block;
padding: 4px 6px;
color: #353D44;
cursor: pointer;
}
.ui-autocomplete .ui-menu-item:first-child {
border-top: none;
}
.ui-autocomplete .ui-menu-item.ui-state-focus {
background-color: #D5E5F4;
color: #161A1C;
}
</style>
</head>
<body>
<form>
<select name="Student" id="name-selector" autofocus="autofocus" autocorrect="off" autocomplete="off">
<option value="" selected="selected">Select Student</option>
<option value="Abercrombie, Amber">Abercrombie, Amber(Gr 11)</option>
<option value="Yupa, Jason">Yupa, Jason(Gr 9)</option>
</select>
<input type="submit" value="Submit" onclick="myFunction()">
</form>
<p id="demo"></p>
<script>
function myFunction() {
var x = document.getElementById("name-selector").value;
document.getElementById("demo").innerHTML = x;
var ss=SpreadsheetApp.getActive();
Browser.msgBox(ss.getSheetName());
}
</script>
</body>
</html>
对于HTML代码的长度，我深表歉意。我不确定我可以忽略什么，仍然提供足够的信息。 html的开头是尝试利用 Jamie Appleseed的开放源代码，该代码允许自动完成和自动更正下拉菜单。 （该部分似乎也不起作用，但我想一次只能做一件事）。
I apologize for the length of the html code. I wasn't sure what I could omit and still provide sufficient information. The beginning of the html is an attempt to utilize Jamie Appleseed's open-source code that allows auto-complete and auto-correction of a drop-down menu. (That part doesn't seem to be working either, but one thing at a time, I suppose).
推荐答案
您不能使用 Browser.msgBox（test）; 使用 SpreadsheetApp.getUi（）。等等
function genDiscRep() {
var ss = SpreadsheetApp.getActive();
var dontTouch = ss.getSheetByName("Do Not Touch");
var studentNamesArrayLength=dontTouch.getLastRow()-1000+1;
var studentNames=dontTouch.getRange(1000,3,studentNamesArrayLength,1).getValues();
var test = HtmlService.createHtmlOutputFromFile('index')
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
SpreadsheetApp.getUi()
.showModalDialog(test, 'User Input')
};
您必须使用 google.script.run.myFunction（）传达回服务器端代码：
You must use google.script.run.myFunction() to communicate back to the server side code:
google.script.run
.functionToRunOnFormSubmit(x);
使用 google.script.host.close（）关闭对话框。
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Select to Autocomplete</title>
<script src="jquery-1.11.1.min.js"></script>
<script src="jquery-ui.min.js"></script>
<script src="jquery.select-to-autocomplete.js"></script>
<script>
(function($){
$(function(){
$('select').selectToAutocomplete();
$('form').submit(function(){
alert( $(this).serialize() );
return false;
});
});
})(jQuery);
</script>
<link rel="stylesheet" href="jquery-ui.css">
<style>
body {
font-family: Arial, Verdana, sans-serif;
font-size: 13px;
}
</style>
</head>
<body>
<form>
<select name="Student" id="name-selector" autofocus="autofocus" autocorrect="off" autocomplete="off">
<option value="" selected="selected">Select Student</option>
<option value="Abercrombie, Amber">Abercrombie, Amber(Gr 11)</option>
<option value="Yupa, Jason">Yupa, Jason(Gr 9)</option>
</select>
<input type="submit" value="Submit" onclick="myFunction()">
</form>
<p id="demo"></p>
<script>
function myFunction() {
var x = document.getElementById("name-selector").value;
document.getElementById("demo").innerHTML = x;
google.script.run
.functionToRunOnFormSubmit(x);
google.script.host.close();
}
</script>
</body>
</html>
需要第二个函数来处理用户输入的返回值：
There needs to be a second function to handle the return from the user input:
function functionToRunOnFormSubmit(fromInputForm) {
var ss = SpreadsheetApp.getActive();
ss.getSheetByName("Do Not Touch").getRange(2, 2, 1, 1).setValue(fromInputForm);
};
这篇关于将HTML下拉菜单与Google表格上的Google Apps脚本一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
