In my webapp show a drop list that get the sheets name from a google sheets file so I'm trying to get the sheet info based on the selected sheet from the drop list . the code works but not based on the selection of droplist. what I did wrong ? here is the full sample hopefully it will help to identify the issue
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;
}
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>
Here's your html a little better organized. I haven't checked all of the functions but atleast the select tag is inside your form and there's just a simple html structure.
<!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>
