正在更新列表框所选索引
Updating listbox selected index
我有一个谷歌脚本,可以在谷歌电子表格和谷歌表单之间读写。它基本上是一个访问材料数据库的表单。
当第一次访问表单时,它会显示所有字段为空,一个填充的材料id(CW_id)的"选择器"列表框,以及选择器列表框中"新建"部分的选项。如果用户停留在"新建"上,则字段保持为空,以便用户手动填充它们,并将其作为新项目附加到数据库中。如果用户滚动"选择器"列表框并选择CW_ID,它将从电子表格中提取并用与所选CW_ID相对应的数据填充字段,供用户编辑(修改零件的详细信息)。
因为我的数据库使用SQL结构,所以我有几个链接表:
-
材料表(保存有关零件的大部分信息,包括制造商的ID[制造商ID])
-
制造商表(链接到材料。manufacturer_ID)
目前,表单仅使用材料表填充字段,因此在"制造商"字段中,它只显示ID号。出于用户目的,我希望脚本检查材料表中显示的manufacturer_ID,进入制造商表,找到匹配项,并让制造商列表框将其显示为当前选择的索引。(或者只是在字段中显示制造商的名称)
到目前为止,这是我的脚本的一部分:
(滚动至底部"//***这是我正在处理的部分")
function doGet() {
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/something")
var materialsSheet = ss.getSheetByName('materials');
var manufacturersSheet = ss.getSheetByName('manufacturers');
var vendorsSheet = ss.getSheetByName('vendors');
var usersSheet = ss.getSheetByName('users');
var projectApp = UiApp.createApplication();
projectApp.setTitle("Materials Form");
var activeEmail = Session.getActiveUser().getEmail();
//I create the vertical panel.
var panel = projectApp.createVerticalPanel().setId('face');
//Here is where I actually create the drop down menu, and assign the function "goSelection" to be activated whenever a selection is made.
var selector = projectApp.createListBox(true);
selector.setName('selectionBox').setId('selectionBox').addItem('New');
var materialsData = materialsSheet.getDataRange().getValues();
//do until row is less than length of
for (var i=0; i < materialsData.length; i++){
if (materialsData [i][1] == "Email"){
//if row in Column B does not equal gmail, skip to i++
continue;
}
//add Column C (CW_ID) of current row to the selector list
selector.addItem(materialsData [i][2]);
}
selector.setSelectedIndex(0);
var selectHandler = projectApp.createServerHandler('goSelection');
selectHandler.addCallbackElement(panel);
selector.addChangeHandler(selectHandler);
//Here is where I create the drop down menu to show list of manufacturers from manufacturers sheet
var manufSelectorLabel = projectApp.createHTML("<br><b>Manufacturer</b><br>").setWidth('100%');
var manufSelector = projectApp.createListBox(true);
manufSelector.setName('manufSelectionBox').setId('manufSelectionBox').addItem('New');
var manufacturersData = manufacturersSheet.getDataRange().getValues();
//do until row is less than length of
for (var i=0; i < manufacturersData.length; i++){
if (manufacturersData [i][1] == "Email"){
//if row in Column B does not equal gmail, skip to i++
continue;
}
//add Column C (Manufacturers) of current row to the selector list
manufSelector.addItem(manufacturersData [i][3]);
}
manufSelector.setSelectedIndex(0);
var manufSelected = manufSelector.SelectedItem;'
//**I am unsure whether this should have it's own function
//var manufSelectHandler = projectApp.createServerChangeHandler('goManuf');
//manufSelectHandler.addCallbackElement(panel);
//manufSelector.addChangeHandler(manufSelectHandler);
var gmailLabel = projectApp.createHTML("<br><b>Gmail:</b><br>").setWidth('100%');
var gmailField = projectApp.createTextArea().setSize('100%', '25px');
gmailField.setName('gmailArea').setId('gmailArea');
gmailField.setText(activeEmail);
var savedLabel = projectApp.createLabel('Thank you for your submission.');
savedLabel.setVisible(false).setId('sLabel');
//At this point, I'm actually declaring the variables for all the fields and text for the actual form.
var selectorLabel = projectApp.createHTML("<br><b>Select CW_ID from list.</b>").setId('selectLabel');
var descriptionLabel = projectApp.createHTML("<br><b>Description</b><br>").setWidth('100%');
var descriptionField = projectApp.createTextArea().setSize('100%', '100px');
descriptionField.setName('descriptionArea').setId('descriptionArea');
var manufacturerLabel = projectApp.createHTML("<br><b>Manufacturer</b></br>").setWidth('100%');
var manufacturerField = projectApp.createTextArea().setSize('100%x', '25px');
manufacturerField.setName('manufacturerArea').setId('manufacturerArea');
var manufacturerListLabel = projectApp.createHTML("<br><b>ManufacturerList</b></br>").setWidth('100%');
var manufacturerListField = projectApp.createTextArea().setSize('100%x', '25px');
manufacturerListField.setName('manufacturerListArea').setId('manufacturerListArea');
var modelnumberLabel = projectApp.createHTML("<br><b>Model Number</b><br>").setWidth('100%');
var modelnumberField = projectApp.createTextArea().setSize('100%', '25px');
modelnumberField.setName('modelnumberArea').setId('modelnumberArea');
//Next, i create the save button and assign the function "saved" to be activated whenever the button is pressed.
var saveButton = projectApp.createButton('Save');
var saveHandler = projectApp.createServerHandler('saved');
saveHandler.addCallbackElement(panel);
saveButton.addClickHandler(saveHandler);
//Now that all the componentes of the form have been declared and set up, I'm going to assemble them on the panel.
panel.setSpacing(6);
panel.add(nameLabel);
panel.add(nameField);
panel.add(gmailLabel);
panel.add(gmailField);
panel.add(selectorLabel);
panel.add(selector);
panel.add(descriptionLabel);
panel.add(descriptionField);
panel.add(manufSelectorLabel);
panel.add(manufSelector);
panel.add(manufacturerLabel);
panel.add(manufacturerField);
panel.add(modelnumberLabel);
panel.add(modelnumberField);
panel.add(saveButton);
panel.add(savedLabel);
projectApp.add(panel);
return projectApp;
}
//This function looks to see what has been selected in the drop down menu, and then pulls the appropriate data from the spreadsheet to display in the fields.
function goSelection(e){
var activeEmail = Session.getActiveUser().getEmail();
var app = UiApp.getActiveApplication();
var gmailField = app.getElementById('gmailArea');
var nameField = app.getElementById('nameArea');
var chosen = e.parameter.selectionBox;
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/something")
var materialsSheet = ss.getSheetByName('materials');
var manufacturersSheet = ss.getSheetByName('manufacturers');
var vendorsSheet = ss.getSheetByName('vendors');
var materialsData = materialsSheet.getDataRange().getValues();
var manufacturersData = manufacturersSheet.getDataRange().getValues();
var vendorsData = vendorsSheet.getDataRange().getValues();
var panel = app.getElementById('face');
var standardpartField = app.getElementById('standardpartArea');
var descriptionField = app.getElementById('descriptionArea');
var manufacturerField = app.getElementById('manufacturerArea');
var manufacturerListField = app.getElementById('manufSelectionBox');
var modelnumberField = app.getElementById('modelnumberArea');
if (chosen != 'New') {
for (var i=1; i < materialsData.length; i++){
if (materialsData [i][1] == "Email"){
//if row in Column B does not equal gmail, skip to i++
continue;
}
if (materialsData [i][2] != chosen){
continue;
}
nameField.setText(materialsData [i][0]);
gmailField.setText(materialsData [i][1]);
standardpartField.setText(materialsData [i][3]);
descriptionField.setText(materialsData [i][4]);
//****THIS IS THE PART I'M WORKING ON
//set manufacturerField to manufacturer of current row
//loop through manufacturer sheet until row matches with manufacturersField
//when a match is found, selector box index to same row
manufacturerField.setText(materialsData [i][5]);
for (var i=1; i < manufacturersData.length; i++){
if (manufacturersData [i][1] == "Email"){
//if row in Column B does not equal Email, skip to i++
continue;
}
if (manufacturersData [i][2] != manufacturerField){
continue;
}
manufacturerListField.setSelectedIndex(i);
}
modelnumberField.setText(materialsData [i][6]);
}
}
这对我来说很有道理,但不起作用。我点击选择器列表框中的一个零件,所有信息都会填充到字段中,正如我所希望的那样。然而,制造商列表框并没有跳转到正确的制造商(如制造商字段中所填充的),它只是停留在那里并保持原样。
(查看图片以了解表单的外观)
http://oi61.tinypic.com/6h6tqp.jpg
制造商列表框和字段显示,但现在,只有制造商字段更改以显示选择零件时的数据)
如有任何帮助,我们将不胜感激!:)
以下是一些可以让您开始使用HTML服务的HTML:
输入表单HTML
<div>
<div>
Name:
<select>
<option value="one">One</option>
<option value="two">Two</option>
<option value="three">Three</option>
<option value="four">Four</option>
</select>
</div>
<br/>
<div>
Gmail:
<select>
<option value="one">One</option>
<option value="two">Two</option>
<option value="three">Three</option>
<option value="four">Four</option>
</select>
</div>
<br/>
<div>Select_CW_ID_From List:</div>
<select name="CW_ID" multiple>
<option value="one">One</option>
<option value="two">Two</option>
<option value="three">Three</option>
<option value="four">Four</option>
</select>
</div>
<br/>
<div>Standard Part</div>
<input type="text"/>
<br/>
<br/>
<div>Description</div>
<textarea rows="4" cols="50" name="comment" form="usrform">
Enter text here...</textarea>
</div>
<script>
function onSuccess(argReturnValue) {
alert('was successful ' + argReturnValue);
}
google.script.run.withSuccessHandler(onSuccess)
.nameOfFunctionInGS_File();
</script>
下载Notepad++:Notepad plus plus
并在其中设计您的HTML。然后在Apps Script项目中创建一个HTML文件。
您的doGet()
应该只有为HTML提供服务的代码。
代码.gs
function doGet() {
return HtmlService.createTemplateFromFile('myHTML_File_Name_Here')
.evaluate() // evaluate MUST come before setting the NATIVE mode
.setTitle('Materials Form')
.setSandboxMode(HtmlService.SandboxMode.NATIVE);
};
创建另一个.gs
文件来处理数据。这就是将数据写入电子表格的所有代码的位置。
若需要填充选择框,可以在HTML的脚本标记中使用JavaScript。
有了我给你的,你应该能够创建一个独立的应用程序脚本HTML服务应用程序。发布并运行它。您可能对当前项目执行了此操作。
- 下拉列表在使用z索引放置在前面后停止工作
- 如何在JqWidgets网格中获取所有未选中行索引的列表
- 正在更新列表框所选索引
- 如何使用 asp.net 中的另一个下拉列表更改下拉列表选定的索引和可见性
- 访问可扩展QML应用程序中的列表视图索引
- 下拉列表中的选定索引在未选择任何值时引发错误
- 在JavaScript中更改下拉列表索引后,ASP.Net提交按钮单击事件不起作用
- 在索引更改中选择的下拉列表无效!!由于引导
- 列表框所选索引更改的原因没有启动
- 使用 javascript 更改 Gridview 文本框的可见属性 true 在下拉列表的选定索引更改事件上为 tru
- 为什么来自.getJSON的名称在声明为列表(JavaScript / JQuery)的索引后仍未定义
- 在 Meteor 中获取集合索引的列表
- 在 jQuery 中从某个索引循环访问列表到另一个索引
- jQuery - 获取可排序列表的索引
- 下拉列表索引更改时日期选取器不起作用
- 使用 jQuery 将项目附加到列表中,但如果使用 Javascript 的项目相同,则使用索引
- 如何查找列表项的索引 (ul-li)
- 当下拉列表选择索引相同时,不会触发 __doPostBack()
- 如果在移动设备上,将数组(索引列表)拆分为两行
- 我一直得到indexerror:索引列表超出python的范围