gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 22:21:20 29 4
gpt4 key购买 nike

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

代码.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;
}

dup.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,组织得更好一些。我还没有检查所有的功能,但至少 select 标签在你的表单中,并且只有一个简单的 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>

最佳答案

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

代码.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>

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

关于javascript - 如何根据下拉列表选择从谷歌表格获取信息?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59221299/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com