google-apps-script - 将下拉列表动态映射到 Google 表格中的条件
问题描述
我想要的下拉列表应该是动态的。因此,根据我在 D 和 E 列中选择的选项,E 列中的下拉列表应该只显示 C 列中的产品。下面我添加了代码,您可以在其中看到我是如何完成其他下拉列表的。
var mainWsName = "Inkoop";
var wsVerkoop = "Verkoop"
var sittardWsName = "Sittard";
var merkColumn = 4;
var modelColumn = 5;
var categorieColumn = 6;
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wVerkoop = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wsVerkoop);
var wsSittard = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sittardWsName);
var arraySittard = wsSittard.getRange(2,1,wsSittard.getLastRow()-1,3).getValues();
function categorie(){
}
function onEdit(e){
var activeCell = e.range;
var val = activeCell.getValue();
var r = activeCell.getRow();
var c = activeCell.getColumn();
var wsName = activeCell.getSheet().getSheetName()
if(wsName == mainWsName && c == merkColumn && r > 1){
if(val === ""){
ws.getRange(r ,modelColumn).clearContent();
ws.getRange(r ,modelColumn).clearDataValidations();
ws.getRange(r ,categorieColumn).clearContent();
} else{
ws.getRange(r ,modelColumn).clearContent();
var filterarraySittard = arraySittard.filter(function(o){return o[0] === val});
var listToApply = filterarraySittard.map(function(o){ return o[1]});
var cell = ws.getRange(r ,modelColumn);
applyValidationToCell(listToApply, cell);
}
}
if(wsName == wsVerkoop && c == merkColumn && r > 1){
if(val === ""){
wVerkoop.getRange(r ,modelColumn).clearContent();
wVerkoop.getRange(r ,modelColumn).clearDataValidations();
wVerkoop.getRange(r ,categorieColumn).clearContent();
} else{
wVerkoop.getRange(r ,modelColumn).clearContent();
var filterarraySittard = arraySittard.filter(function(o){return o[0] === val});
var listToApply = filterarraySittard.map(function(o){ return o[1]});
var cell = wVerkoop.getRange(r ,modelColumn);
applyValidationToCell(listToApply, cell);
}
}
} // end onEdit
function applyValidationToCell(list,cell){
var rule = SpreadsheetApp.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule);
}
解决方案
尝试这个:
代码:
var dstName = 'Sheet1';
var srcName = 'Sheet2';
var sprdSheet = SpreadsheetApp.getActiveSpreadsheet();
var dstSheet = sprdSheet.getSheetByName(dstName);
var srcSheet = sprdSheet.getSheetByName(srcName);
function populateMerk() {
var merk = dstSheet.getRange("D2:D");
var merkList = getData("", "A");
var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(merkList);
merk.setDataValidation(rangeRule);
}
function populateModel(id, row, sheetName) {
var model = dstSheet.getRange("E" + row);
var modelList = getData(id, "B");
var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(modelList);
var category = model.offset(0, 1);
var mValue = model.getValue();
model.clearDataValidations();
model.clearContent();
model.setDataValidation(rangeRule);
if (sheetName == dstName) {
category.clearDataValidations();
category.clearContent();
}
else if (sheetName == srcName) {
model.setValue(mValue);
}
}
function populateCategory(id, row, sheetName) {
var category = dstSheet.getRange("F" + row);
var categoryList = getData(id, "C");
var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(categoryList);
var cValue = category.getValue();
category.clearDataValidations();
category.clearContent();
category.setDataValidation(rangeRule);
if (sheetName == srcName) {
category.setValue(cValue);
}
}
function getData(srcId, endCol) {
var srcLastRow = srcSheet.getLastRow();
var srcData = srcSheet.getRange("A2:" + endCol + srcLastRow).getValues();
var dataList = [];
if (!srcId) {
// Get unique items for merk
dataList = srcData.flat().filter((item, i, ar) => ar.indexOf(item) === i);
}
else {
srcData.forEach(function(row) {
// Only add unique models
if (srcId == row[0] && !dataList.includes(row[1]) && !row[2]) {
dataList.push(row[1]);
}
// Only add unique categories
else if (srcId == (row[0] + row[1]) && !dataList.includes(row[2])) {
dataList.push(row[2]);
}
});
}
return dataList;
}
function onEdit(e) {
var sheetName = e.source.getActiveSheet().getName();
var range = e.range;
var col = range.getColumn();
var row = range.getRow();
if (row > 1) {
if (sheetName == dstName) {
var merk = dstSheet.getRange(row, 4).getValue();
var model = dstSheet.getRange(row, 5).getValue();
if (col == 4) {
populateModel(merk, row, sheetName);
}
else if (col == 5) {
populateCategory(merk + model, row);
}
}
else if (sheetName == srcName) {
switch (col) {
case 1:
populateMerk();
break;
case 2:
var lastE = dstSheet.getRange("E2:E").getValues().filter(String).length;
var merk;
for (var i = 0; i < lastE; i++) {
merk = dstSheet.getRange("D" + (i + 2)).getValue();
populateModel(merk, i + 2, sheetName);
}
break;
case 3:
var lastF = dstSheet.getRange("F2:F").getValues().filter(String).length;
var merk, model, row;
for (var i = 0; i < lastF; i++) {
row = i + 2;
merk = dstSheet.getRange("D" + row).getValue();
model = dstSheet.getRange("E" + row).getValue();
populateCategory(merk + model, row, sheetName);
}
break;
}
}
}
}
Sheet2 中的示例数据:
Sheet1 中的输出:
笔记:
- 该代码还会在您编辑源工作表时进行注释。如果更新,将更新目标工作表中该列的所有下拉列表。而且由于发生这种情况时会有很多电话,所以会有点慢。你仍然可以优化它,但现在,它仍然有效。
另外,请参阅下面的参考资料,并按照 Cooper 的上述评论了解我们如何在 StackOverflow 中做事:
参考:
推荐阅读
- javascript - 在javascript上合并对象给了我嵌套对象,如何解决?
- python - 为什么在 python 中使用线性回归模型预测股票价格时,我得到了 99% 的准确率?
- mysql - 在sql中合并查询
- javascript - 在 Nunjucks 中获取指定范围的数组
- cygwin - 如何让 cygwin 禁用 fortran?
- c# - Pdf 导出已损坏
- control-m - 为控件 M 中的每个文件名添加时间和增量 1 秒
- json - 将 JSON 解析为多个 Typescript 对象
- php - 如何以选项卡作为链接的形式在所有子域中共享多站点主域的非主菜单?
- python - QMediaPlayer setPlaybackRate > 1 导致高音音频