问题描述
我在 Google 表格上运行一个函数,要求用户从(相当长的)选项列表中进行选择.由于不推荐使用 UI 服务,我想我会尝试使用 HTML,但我对此一无所知.我需要弹出 HTML 用户界面,让用户从列表中选择一个名称,然后在将名称传递回应用程序脚本函数后离开.我试图拼凑一些代码,但我似乎无法总是弹出下拉菜单,而且我似乎无法弄清楚如何将选择发送回原始函数.有帮助吗?
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 脚本结合使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!