将HTML选择存储到变量中/使用Google表格中的数据 [英] Storing an HTML selection into a variable / using that data in Google Sheets
问题描述
我几乎有了一个HTML表单选择器,该选择器根据Google表格列中的数据创建一个数组(完成),并将该数据填充到一个下拉框中(完成).
我整个周末一直在尝试找出如何捕获下拉选项的选择,然后将其汇总到变量中.
这是我的Google脚本代码,可启动HTML弹出窗口:
function ClientDropDownHTML(){var template = HtmlService.createTemplateFromFile('index');var htmlDlg = template.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME)//var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();//var lastRow = sheet.getLastRow();//var myArray = sheet.getRange('A2:A'+ lastRow).getValues();.setWidth(500).setHeight(150);SpreadsheetApp.getUi().showModalDialog(htmlDlg,'选择现有客户端');}
HTML下拉列表运行.这是我的HTML表单的代码,该代码在Google表格数据上运行数组并将其填充为选择器:
<!DOCTYPE html>< html>< head></head><身体>< form id ="myForm">< select id =" selectClient">< option>选择现有客户端</option>< ;?var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();?>< ;?var clientindex = sheet.getRange('T2').getValue();?>< ;?var myArray = sheet.getRange('V2:V'+ clientindex).getValues();?>< ;?for(var i = 0; i< myArray.length; ++ i){< option><?= myArray [i]?></option>< ;?}?></select></form></body></html>
这就是我被困住的地方.作为后盾,我需要确保可以捕获选择内容,因此我尝试将其记录-我尝试了类似以下内容:
I am almost there with an HTML form selector, which creates an array based on data from a google sheets column (done) and populates that data into a dropdown box (done).
What I have been trying all weekend to figure out is how to capture the selection of the dropdown option and then lump that into a variable.
Here is my google script code that launches the HTML popup:
function ClientDropDownHTML() {
var template = HtmlService.createTemplateFromFile('index');
var htmlDlg = template.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME)
// var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// var lastRow = sheet.getLastRow();
// var myArray = sheet.getRange('A2:A' + lastRow).getValues();
.setWidth(500)
.setHeight(150);
SpreadsheetApp.getUi()
.showModalDialog(htmlDlg, 'Select An Existing Client');
}
The HTML dropdown runs. This is the code of my HTML form, which runs the array on Google sheets data and populates it as a selector:
<!DOCTYPE html>
<html>
<head>
</head>
<body>
<form id="myForm">
<select id="selectClient">
<option>Choose an Existing Client</option>
<? var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); ?>
<? var clientindex = sheet.getRange('T2').getValue(); ?>
<? var myArray = sheet.getRange('V2:V' + clientindex).getValues(); ?>
<? for (var i = 0; i < myArray.length; ++i) { ?>
<option> <?=myArray[i]?> </option>
<? } ?>
</select>
</form>
</body>
</html>
This is where I am stuck. To behin with I need to ensure I can capture the selection so I tried to log it - I have tried something like seen here: https://developer.mozilla.org/en-US/docs/Web/API/HTMLSelectElement#Example
I end up with something like the following to my HTML:
<? var select = document.getElementById('selectClient'); ?>
<? console.log(select.selectedIndex); ?>
<? console.log(select.options[select.selectedIndex].value) ?>
However, if I do this I just get a "document is not defined" error.
I have read different issues on this website and none of the solutions seem to work for me.
In short, I need a way to:
Reference the user's selection from the HTML Store it in a variable Be able to move that variable back to Google Sheets.
Make a selection from Select tag
GS:
function getClientList() {
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Sheet15');
var vA=sh.getRange(2,1,sh.getLastRow()-1,1).getValues();
return vA;
}
function selectClient(client) {
SpreadsheetApp.getUi().alert('Client is ' + client);
}
function runTwo() {
var ui=HtmlService.createHtmlOutputFromFile('ah2');
SpreadsheetApp.getUi().showModelessDialog(ui, 'Select Client')
}
HTML('ah2'):
<!DOCTYPE html>
<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
</head>
<body>
<form id="myForm">
<select id="sel1" onchange="selClient()"></select>
<script>
$(function(){
google.script.run
.withSuccessHandler(function(vA){
updateSelect(vA);
})
.getClientList();
});
function updateSelect(vA,id){
var id=id || 'sel1';
var select = document.getElementById(id);
select.options.length = 0;
for(var i=0;i<vA.length;i++)
{
select.options[i] = new Option(vA[i],vA[i]);
}
}
function selClient() {
google.script.run.selectClient($('#sel1').val());
}
</script>
</form>
</body>
</html>
Sheet15:
这篇关于将HTML选择存储到变量中/使用Google表格中的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!