从Google电子表格数据构建/填充网页上的下拉菜单 [英] Building / Populating a Drop Down Menu on Web Page from Google Spreadsheet Data
本文介绍了从Google电子表格数据构建/填充网页上的下拉菜单的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我使用Google可视化查询从该中提取列标题电子表格。目前，我必须在菜单中包含我想要的每个单元格的行和列索引。我想要的是一个脚本，可以使用电子表格第1行单元格中的数据动态填充此菜单。换句话说，使菜单与电子表格中存在的菜单一样大或小。
我相信我需要以下内容，但我不确定如何实施它也许我需要服务器端脚本（GAS）来完成这个？
< select> （var i = 0; i< data.length; ++ i）
{
< option>！= data [i]< / option>
}
< / select>
我发现大量的文档可以使用Google的HTMLService来完成，但是我需要这个菜单托管使用不同的服务。
任何帮助将不胜感激。谢谢！
我当前的代码如下...
google.load（'visualization'，'1'，{'packages'：['corechart']}）; google.setOnLoadCallback（ValIDS）;函数ValIDS（）{var queryValIDS = new google.visualization.Query（'https://docs.google.com/spreadsheet/ccc?key=1HpHMfoEnPgESb2XPVCgb7XyGwRAvrq3EoQj4WHj4vhA&sheet=QUERY2'）; queryValIDS.send（handleQueryValIDResponse）; } function handleQueryValIDResponse（response）{if（response.isError（））{alert（'Error in ID Validation Query：'+ response.getMessage（）+''+ response.getDetailedMessage（））;返回; } var datatable = response.getDataTable（）; var cat1 = datatable.getValue（0,0）; var cat2 = datatable.getValue（0,1）; var cat3 = datatable.getValue（0,2）; var cat4 = datatable.getValue（0,3）; var cat5 = datatable.getValue（0,4）; var cat6 = datatable.getValue（0,5）; var cat7 = datatable.getValue（0,6）; var cat8 = datatable.getValue（0,7）; var cat9 = datatable.getValue（0,8）; var cat10 = datatable.getValue（0,9）; document.getElementById（'cat1'）。innerHTML = cat1; document.getElementById（'cat2'）。innerHTML = cat2; document.getElementById（'cat3'）。innerHTML = cat3; document.getElementById（'cat4'）。innerHTML = cat4; document.getElementById（'cat5'）。innerHTML = cat5; document.getElementById（'cat6'）。innerHTML = cat6; document.getElementById（'cat7'）。innerHTML = cat7; document.getElementById（'cat8'）。innerHTML = cat8; document.getElementById（'cat9'）。innerHTML = cat9; document.getElementById（'cat10'）。innerHTML = cat10; } function myFunction（）{document.getElementById（myDropdown）。classList.toggle（show）;} window.onclick = function（event）{if（！event.target.matches（'。dropbtn'））{ var dropdowns = document.getElementsByClassName（dropdown-content）; var i; for（i = 0; i< dropdowns.length; i ++）{var openDropdown = dropdowns [i]; if（openDropdown.classList.contains（'show'））{openDropdown.classList.remove（'show'）; }}}}
.dropbtn {background-color： 4CAF50;颜色：白色;填充：16px; font-size：16px;边界：无; cursor：pointer;}。dropbtn：hover，.dropbtn：focus {background-color：＃3e8e41;}。dropdown {position：relative; display：inline-block;}。dropdown-content {display：none;位置：绝对背景颜色：＃f9f9f9;最小宽度：160px;溢出：自动; box-shadow：0px 8px 16px 0px rgba（0,0,0,0.2）;}。dropdown-content a {color：black; padding：12px 16px;文字装饰：无; display：block;}。dropdown a：hover {background-color：＃f1f1f1} .show {display：block;} dropbtn {background-color：＃4CAF50;颜色：白色;填充：16px; font-size：16px;边界：无; cursor：pointer;}。dropbtn：hover，.dropbtn：focus {background-color：＃3e8e41;}。dropdown {position：relative; display：inline-block;}。dropdown-content {display：none;位置：绝对背景颜色：＃f9f9f9;最小宽度：160px;溢出：自动; box-shadow：0px 8px 16px 0px rgba（0,0,0,0.2）;}。dropdown-content a {color：black; padding：12px 16px;文字装饰：无; display：block;}。dropdown a：hover {background-color：＃f1f1f1} .show {display：block;}
< script src =https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js>< / script>< script type =text / javascriptsrc =https://www.google.com/jsapi>< / script>< div class =dropdown>< button onclick = myFunction（）class =dropbtn>下拉菜单< / button> < div id =myDropdownclass =dropdown-content> < a href =＃>< div id =cat1>< / div>< / a> < a href =＃>< div id =cat2>< / div>< / a> < a href =＃>< div id =cat3>< / div>< / a> < a href =＃>< div id =cat4>< / div>< / a> < a href =＃>< div id =cat5>< / div>< / a> < a href =＃>< div id =cat6>< / div>< / a> < a href =＃>< div id =cat7>< / div>< / a> < a href =＃>< div id =cat8>< / div>< / a> < a href =＃>< div id =cat9>< / div>< / a> < a href =＃>< div id =cat10>< / div>< / a> < / div>< / div>
解决方案
您可以使用客户端JavaScript动态构建下拉列表
，使用 datatable
有很多方法，但这应该可以完成你所需要的
你可以使用 getNumberOfColumns 而不是硬编码每个下拉项目
这将允许其他列添加到电子表格
，而无需更改代码对于（var i = 0; i< datatable.getNumberOfColumns（）; i ++）{
var（$）
ddItem = document.getElementById（'myDropdown'）。appendChild（document.createElement（'A'））;
ddItem.href ='＃';
var ddItemContent = ddItem.appendChild（document.createElement（'SPAN'））;
ddItemContent.id ='cat'+（i + 1）;
ddItemContent.innerHTML = datatable.getValue（0，i）;
}
（i + 1）在上面的 id 上使用，
，所以 id 将是一样的因为锚点（< a> ）是内联元素，因为您已经硬编码
a < span> 已被使用，而不是< div>
您可能甚至不需要 id ，或者 SPAN
，因为下载项目正在动态创建
请参阅以下工作代码段...
google.load（'visualization'，'1'，{'packages'：['corechart']}）; google.setOnLoadCallback ValIDS）;函数ValIDS（）{var queryValIDS = new google.visualization.Query（'https://docs.google.com/spreadsheet/ccc?key=1HpHMfoEn PgESb2XPVCgb7XyGwRAvrq3EoQj4WHj4vhA&安培;片= QUERY2’ ）; queryValIDS.send（handleQueryValIDResponse）;} function handleQueryValIDResponse（response）{if（response.isError（））{alert（'Error in ID Validation Query：'+ response.getMessage（）+''+ response.getDetailedMessage（））;返回; } var datatable = response.getDataTable（）; for（var i = 0; i< datatable.getNumberOfColumns（）; i ++）{var ddItem = document.getElementById（'myDropdown'）。appendChild（document.createElement（'A'））; ddItem.href ='＃'; var ddItemContent = ddItem.appendChild（document.createElement（'SPAN'））; ddItemContent.id ='cat'+（i + 1）; ddItemContent.innerHTML = datatable.getValue（0，i）; }} function myFunction（）{document.getElementById（'myDropdown'）。classList.toggle（'show'）;} window.onclick = function（event）{if（！event.target.matches（'。dropbtn'）） {var dropdowns = document.getElementsByClassName（'dropdown-content'）; var i; for（i = 0; i< dropdowns.length; i ++）{var openDropdown = dropdowns [i]; if（openDropdown.classList.contains（'show'））{openDropdown.classList.remove（'show'）; }}}}
.dropbtn {background-color： 4CAF50;颜色：白色;填充：16px; font-size：16px;边界：无; cursor：pointer;}。dropbtn：hover，.dropbtn：focus {background-color：＃3e8e41;}。dropdown {position：relative; display：inline-block;}。dropdown-content {display：none;位置：绝对背景颜色：＃f9f9f9;最小宽度：160px;溢出：自动; box-shadow：0px 8px 16px 0px rgba（0,0,0,0.2）;}。dropdown-content a {color：black; padding：12px 16px;文字装饰：无; display：block;}。dropdown a：hover {background-color：＃f1f1f1} .show {display：block;} dropbtn {background-color：＃4CAF50;颜色：白色;填充：16px; font-size：16px;边界：无; cursor：pointer;}。dropbtn：hover，.dropbtn：focus {background-color：＃3e8e41;}。dropdown {position：relative; display：inline-block;}。dropdown-content {display：none;位置：绝对背景颜色：＃f9f9f9;最小宽度：160px;溢出：自动; box-shadow：0px 8px 16px 0px rgba（0,0,0,0.2）;}。dropdown-content a {color：black; padding：12px 16px;文字装饰：无; display：block;}。dropdown a：hover {background-color：＃f1f1f1} .show {display：block;}
< script type =text / javascriptsrc =https://www.google.com/jsapi>< / script>< script src =https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js>< / script>< div class =dropdown> < button onclick =myFunction（）class =dropbtn>下拉菜单< / button> < div id =myDropdownclass =dropdown-content>< / div>< / div>
/ p>
编辑
getValue 方法需要两个参数
getValue（rowIndex，columnIndex）
both rowIndex 和 columnIndex 是基于零的，
意味着第一个单元格值将为 getValue（0，0 ）
如果你想从每行的第一列拉取值...
使用 i as rowIndex 和 0 as （var i = 0; i< datatable）中的 columnIndex
getNumberOfRows（）; i ++）{
var test = datatable.getValue（i，0）;
}
I'm using a Google visualization query to pull column headings from this spreadsheet. Currently I must include the row and column indexes for each cell I want in the menu. What I'd like is a script that'll populate this menu dynamically with data from the cells in row 1 of the spreadsheet. In other words, make the menu as big or as small as what exists on the spreadsheet.
I believe I need something like the following, but I'm unsure how to implement it. Perhaps I need server side script (GAS) to accomplish this??
<select>
for (var i = 0; i < data.length; ++i) {
<option>!= data[i] </option>
}
</select>
I've found plenty of documentation to accomplish this using Google's HTMLService, but I need this menu to be hosted using a different service.
Any help would be greatly appreciated. Thanks!
My current code follows...
google.load('visualization', '1', {'packages':['corechart']});
google.setOnLoadCallback(ValIDS);
function ValIDS() {
var queryValIDS = new google.visualization.Query('https://docs.google.com/spreadsheet/ccc?key=1HpHMfoEnPgESb2XPVCgb7XyGwRAvrq3EoQj4WHj4vhA&sheet=QUERY2');
queryValIDS.send(handleQueryValIDResponse);
}
function handleQueryValIDResponse(response) {
if (response.isError()) {
alert('Error in ID Validation Query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var datatable = response.getDataTable();
var cat1 = datatable.getValue(0,0);
var cat2 = datatable.getValue(0,1);
var cat3 = datatable.getValue(0,2);
var cat4 = datatable.getValue(0,3);
var cat5 = datatable.getValue(0,4);
var cat6 = datatable.getValue(0,5);
var cat7 = datatable.getValue(0,6);
var cat8 = datatable.getValue(0,7);
var cat9 = datatable.getValue(0,8);
var cat10 = datatable.getValue(0,9);
document.getElementById('cat1').innerHTML = cat1;
document.getElementById('cat2').innerHTML = cat2;
document.getElementById('cat3').innerHTML = cat3;
document.getElementById('cat4').innerHTML = cat4;
document.getElementById('cat5').innerHTML = cat5;
document.getElementById('cat6').innerHTML = cat6;
document.getElementById('cat7').innerHTML = cat7;
document.getElementById('cat8').innerHTML = cat8;
document.getElementById('cat9').innerHTML = cat9;
document.getElementById('cat10').innerHTML = cat10;
}
function myFunction() {
document.getElementById("myDropdown").classList.toggle("show");
}
window.onclick = function(event) {
if (!event.target.matches('.dropbtn')) {
var dropdowns = document.getElementsByClassName("dropdown-content");
var i;
for (i = 0; i < dropdowns.length; i++) {
var openDropdown = dropdowns[i];
if (openDropdown.classList.contains('show')) {
openDropdown.classList.remove('show');
}
}
}
}
.dropbtn {
background-color: #4CAF50;
color: white;
padding: 16px;
font-size: 16px;
border: none;
cursor: pointer;
}
.dropbtn:hover, .dropbtn:focus {
background-color: #3e8e41;
}
.dropdown {
position: relative;
display: inline-block;
}
.dropdown-content {
display: none;
position: absolute;
background-color: #f9f9f9;
min-width: 160px;
overflow: auto;
box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
}
.dropdown-content a {
color: black;
padding: 12px 16px;
text-decoration: none;
display: block;
}
.dropdown a:hover {background-color: #f1f1f1}
.show {display:block;}
.dropbtn {
background-color: #4CAF50;
color: white;
padding: 16px;
font-size: 16px;
border: none;
cursor: pointer;
}
.dropbtn:hover, .dropbtn:focus {
background-color: #3e8e41;
}
.dropdown {
position: relative;
display: inline-block;
}
.dropdown-content {
display: none;
position: absolute;
background-color: #f9f9f9;
min-width: 160px;
overflow: auto;
box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
}
.dropdown-content a {
color: black;
padding: 12px 16px;
text-decoration: none;
display: block;
}
.dropdown a:hover {background-color: #f1f1f1}
.show {display:block;}
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<div class="dropdown">
<button onclick="myFunction()" class="dropbtn">Dropdown</button>
<div id="myDropdown" class="dropdown-content">
<a href="#"><div id="cat1"></div></a>
<a href="#"><div id="cat2"></div></a>
<a href="#"><div id="cat3"></div></a>
<a href="#"><div id="cat4"></div></a>
<a href="#"><div id="cat5"></div></a>
<a href="#"><div id="cat6"></div></a>
<a href="#"><div id="cat7"></div></a>
<a href="#"><div id="cat8"></div></a>
<a href="#"><div id="cat9"></div></a>
<a href="#"><div id="cat10"></div></a>
</div>
</div>
解决方案
you can use client-side JavaScript to build the drop-down dynamically
using the data from the datatable
there are a number of ways, but this should accomplish what you need
you can use getNumberOfColumns rather than hard-coding each drop-down item
this will allow additional columns to be added to the spreadsheet,
without having to change the code / html
for (var i = 0; i < datatable.getNumberOfColumns(); i++) {
var ddItem = document.getElementById('myDropdown').appendChild(document.createElement('A'));
ddItem.href = '#';
var ddItemContent = ddItem.appendChild(document.createElement('SPAN'));
ddItemContent.id = 'cat' + (i + 1);
ddItemContent.innerHTML = datatable.getValue(0, i);
}
(i + 1) was used on the id above,
just so the id would be the same as what you had hard-coded
since anchors (<a>) are inline elements,
a <span> was used instead of <div>
you may not even need id, or the SPAN for that matter,
since the drop-down items are being created dynamically
see following working snippet...
google.load('visualization', '1', {'packages':['corechart']});
google.setOnLoadCallback(ValIDS);
function ValIDS() {
var queryValIDS = new google.visualization.Query('https://docs.google.com/spreadsheet/ccc?key=1HpHMfoEnPgESb2XPVCgb7XyGwRAvrq3EoQj4WHj4vhA&sheet=QUERY2');
queryValIDS.send(handleQueryValIDResponse);
}
function handleQueryValIDResponse(response) {
if (response.isError()) {
alert('Error in ID Validation Query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var datatable = response.getDataTable();
for (var i = 0; i < datatable.getNumberOfColumns(); i++) {
var ddItem = document.getElementById('myDropdown').appendChild(document.createElement('A'));
ddItem.href = '#';
var ddItemContent = ddItem.appendChild(document.createElement('SPAN'));
ddItemContent.id = 'cat' + (i + 1);
ddItemContent.innerHTML = datatable.getValue(0, i);
}
}
function myFunction() {
document.getElementById('myDropdown').classList.toggle('show');
}
window.onclick = function(event) {
if (!event.target.matches('.dropbtn')) {
var dropdowns = document.getElementsByClassName('dropdown-content');
var i;
for (i = 0; i < dropdowns.length; i++) {
var openDropdown = dropdowns[i];
if (openDropdown.classList.contains('show')) {
openDropdown.classList.remove('show');
}
}
}
}
.dropbtn {
background-color: #4CAF50;
color: white;
padding: 16px;
font-size: 16px;
border: none;
cursor: pointer;
}
.dropbtn:hover, .dropbtn:focus {
background-color: #3e8e41;
}
.dropdown {
position: relative;
display: inline-block;
}
.dropdown-content {
display: none;
position: absolute;
background-color: #f9f9f9;
min-width: 160px;
overflow: auto;
box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
}
.dropdown-content a {
color: black;
padding: 12px 16px;
text-decoration: none;
display: block;
}
.dropdown a:hover {background-color: #f1f1f1}
.show {display:block;}
.dropbtn {
background-color: #4CAF50;
color: white;
padding: 16px;
font-size: 16px;
border: none;
cursor: pointer;
}
.dropbtn:hover, .dropbtn:focus {
background-color: #3e8e41;
}
.dropdown {
position: relative;
display: inline-block;
}
.dropdown-content {
display: none;
position: absolute;
background-color: #f9f9f9;
min-width: 160px;
overflow: auto;
box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
}
.dropdown-content a {
color: black;
padding: 12px 16px;
text-decoration: none;
display: block;
}
.dropdown a:hover {background-color: #f1f1f1}
.show {display:block;}
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<div class="dropdown">
<button onclick="myFunction()" class="dropbtn">Dropdown</button>
<div id="myDropdown" class="dropdown-content"></div>
</div>
EDIT
the getValue method takes two arguments
getValue(rowIndex, columnIndex)
both rowIndex and columnIndex are zero-based,
meaning the first cell value would be getValue(0, 0)
if you want to pull values from the first column of each row...
use i as rowIndex and 0 as columnIndex
for (var i = 0; i < datatable.getNumberOfRows(); i++) {
var test = datatable.getValue(i, 0);
}
这篇关于从Google电子表格数据构建/填充网页上的下拉菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
