如何根据下拉列表选择从谷歌表格中获取信息?

在我的网络应用程序中显示一个下拉列表，该列表从Google工作表文件中获取工作表名称，因此我正在尝试根据下拉列表中的选定工作表获取工作表信息。该代码有效，但不基于下拉列表的选择。我做错了什么？这是完整的示例，希望它将有助于确定问题

CODE.gs

function doGet() {
return HtmlService.createHtmlOutputFromFile('dup');
}
function getSheetNames() {
var ss=SpreadsheetApp.getActive();
var shts=ss.getSheets();
var sObj={sA:[]};
shts.forEach(function(sh){
sObj.sA.push(sh.getName());
})
return sObj;
}

function getDataFromServer(e) {
var ss=SpreadsheetApp.getActive();
var data =ss.getSheetByName(e.name).getRange("B2:J22").getValues();
var ar = data.splice(0,1); //add headers
data.forEach(function(f) {
if (~f.indexOf(e.searchtext)) ar.push(f);
});
e['sA']=getSheetNames().sA;
return ar;
}

哎呀.HTML

<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>  
</head>
<body>
<select id="sel1"></select><label for="sel1">Report Date </label>
</body>   
<script>
$(function(){
google.script.run
.withSuccessHandler(function(sObj){
var select=document.getElementById('sel1');      
sObj.sA.unshift('Please Select a report date');
select.options.length=0;
for(var i=0;i<sObj.sA.length;i++) {
select.options[i]=new Option(sObj.sA[i],sObj.sA[i]);
}
})
.getSheetNames();
});
const loaded = new Promise((res, rej) => {
google.charts.load('current');
google.charts.setOnLoadCallback(res);
});
let wrapper = null;
async function drawTable(arr) {
await loaded; //wait if charts is not loaded
wrapper = new google.visualization.ChartWrapper({
chartType: 'Table',
dataTable: arr,
containerId: 'table_div',
});
wrapper.draw();
}
function getData(f) {
google.script.run
.withSuccessHandler(drawTable,function(rObj){
$('#sel1').css('background-color','#ffffff');
var select=document.getElementById('sel1');      
rObj.sA.unshift('Please Select by Report Date');
select.options.length=0;
for(var i=0;i<rObj.sA.length;i++) {
select.options[i]=new Option(rObj.sA[i],rObj.sA[i]);
}
})
.getDataFromServer(f);
}

</script>
<body>
<form>
<input  type="button"id="display"class="btn btn-primary" value="retrieve report Data" onClick="getData(this.parentNode)" />
</form>

<div id="table_div"></div>
</body>
</html>

这是你的html组织得更好一点。我还没有检查所有功能，但至少选择标签在您的表单中，并且只有一个简单的 html 结构。

<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>  
<script>
$(function(){
google.script.run
.withSuccessHandler(function(sObj){
var select=document.getElementById('sel1');      
sObj.sA.unshift('Please Select a report date');
select.options.length=0;
for(var i=0;i<sObj.sA.length;i++) {
select.options[i]=new Option(sObj.sA[i],sObj.sA[i]);
}
})
.getSheetNames();
});
const loaded = new Promise((res, rej) => {
google.charts.load('current');
google.charts.setOnLoadCallback(res);
});
let wrapper = null;
async function drawTable(arr) {
await loaded; //wait if charts is not loaded
wrapper = new google.visualization.ChartWrapper({
chartType: 'Table',
dataTable: arr,
containerId: 'table_div',
});
wrapper.draw();
}
function getData(f) {
google.script.run
.withSuccessHandler(drawTable,function(rObj){
$('#sel1').css('background-color','#ffffff');
var select=document.getElementById('sel1');      
rObj.sA.unshift('Please Select by Report Date');
select.options.length=0;
for(var i=0;i<rObj.sA.length;i++) {
select.options[i]=new Option(rObj.sA[i],rObj.sA[i]);
}
})
.getDataFromServer(f);
}
</script>
</head>
<body>
<form>
<select id="sel1"></select><label for="sel1">Report Date </label>
<input  type="button"id="display"class="btn btn-primary" value="retrieve report Data" onClick="getData(this.parentNode)" />
</form>
</body>
</html>

这就是我的做法。此脚本首先获取您帐户中所有电子表格的列表，并在 Web 应用程序上填充一个下拉列表。然后选择电子表格，它将返回到服务器以获取所选电子表格的所有工作表。做出选择后，它将整个工作表加载到 Web 应用程序上，并为您提供编辑该工作表上数据的功能。

Code.gs：

function htmlSpreadsheet(ssO) {
var br='<br />';
var s='';
var hdrRows=1;
var ss=SpreadsheetApp.openById(ssO.id);
var sht=ss.getSheetByName(ssO.name);
var rng=sht.getDataRange();
var rngA=rng.getValues();
s+='<table>';
for(var i=0;i<rngA.length;i++)
{
s+='<tr>';
for(var j=0;j<rngA[i].length;j++)
{
if(i<hdrRows)
{
s+='<th id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="20" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
} 
else
{
s+='<td id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="20" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
}
}
s+='</tr>';
}
s+='</table>';
s+='</body></html>';
var namehl=Utilities.formatString('<h1>%s</h1>', ss.getName());
var shnamehl=Utilities.formatString('<h2>%s</h2>', sht.getName());
var opO={hl:s,name:namehl,shname:shnamehl};
return opO;
}
function updateSpreadsheet(updObj) {
var i=updObj.rowIndex;
var j=updObj.colIndex;
var value=updObj.value;
var ss=SpreadsheetApp.openById(updObj.id);
var sht=ss.getSheetByName(updObj.name);
var rng=sht.getDataRange();
var rngA=rng.getValues();
rngA[i][j]=value;
rng.setValues(rngA);
var data = {'message':'Cell[' + Number(i + 1) + '][' + Number(j + 1) + '] Has been updated', 'ridx': i, 'cidx': j};
return data;
}
function doGet() {
var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);
return userInterface.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function getAllSpreadSheets() {
var files=DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS); 
var s = '';
var vA=[['Select Spreadsheet',0]];
while(files.hasNext())
{
var file = files.next();
var fileName=file.getName();
var fileId=file.getId();
vA.push([fileName,fileId]);
}
//return vA;
return {array:vA,type:'sel1'};
}
//working on this function right now 2017/11/08
function getAllSheets(ssO) {
var ss=SpreadsheetApp.openById(ssO.id);
var allSheets=ss.getSheets();
var vA=[['Select Sheet']];
for(var i=0;i<allSheets.length;i++)
{
var name=allSheets[i].getName();
vA.push([name]);
}
return {array:vA,type:'sel2'};
}

htmlss.html：

<!DOCTYPE html>
<html>
<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script>
$(function() {
$('#msg').html('Please wait. Collecting a list of all Spreadsheets...');
google.script.run
.withSuccessHandler(updateSelect)
.getAllSpreadSheets();
});
function updateSS(i,j)
{
var str='#txt' + String(i) + String(j);
var value=$(str).val();
var ssId=$('#sel1').val();
var name=$('#sel2').val();
var updObj={rowIndex:i,colIndex:j,value:value,id:ssId,name:name};
$(str).css('background-color','#ffff00');
google.script.run
.withSuccessHandler(updateSheet)
.updateSpreadsheet(updObj);
}
function updateSheet(data)
{
//$('#success').text(data.message);
$('#txt' + data.ridx + data.cidx).css('background-color','#ffffff');
}
function updateSelect(dtO)
{
$('#sel1').css('background','#ffffff');
$('#sel2').css('background','#ffffff');
$('#msg').html('Spreadsheet List has been updated.  Now select a SpreadSheet to display');
var select = document.getElementById(dtO.type);
var vA=dtO.array;
select.options.length = 0; 
for(var i=0;i<vA.length;i++)
{
select.options[i] = new Option(vA[i][0],vA[i][vA[i].length-1]);
}
}
function loadSelectSheet()
{
var shId=$('#sel1').val();
var name=$('#sel1').text();
$('#sel1').css('background','#ffff00');
document.getElementById('ssname').innerHTML="";
var ssO={name:name ,id:shId}
google.script.run
.withSuccessHandler(updateSelect)
.getAllSheets(ssO);
}
function displaySelectedSheet()
{
var ssId=$('#sel1').val();
var name=$('#sel2').val();
$('#sel2').css('background','#ffff00');
document.getElementById('shname').innerHTML="";
var ssO={id:ssId,name:name};
google.script.run
.withSuccessHandler(displaySheet)
.htmlSpreadsheet(ssO);
}
function displaySheet(opO)
{
$('#sel2').css('background','#ffffff');
document.getElementById('ssname').innerHTML=opO.name;
document.getElementById('shname').innerHTML=opO.shname;
document.getElementById('sss').innerHTML=opO.hl;
}
console.log('My Code');
</script>
<style>
th{text-align:left}
</style>
</head>
<body>
<div id="msg"></div><br />
<br /><select id="sel1" onChange="loadSelectSheet();"></select>
<br /><select id="sel2" onChange="displaySelectedSheet();"></select>
<div id="ssname"></div>
<div id="shname"></div>
<div id="sss"></div>
</body>
</html>

几年前我这样做了，但我仍然偶尔使用它。

相关内容

最新更新

热门标签：