自動完成多個相關下拉串列

在這張表中，我有多個相關的下拉串列。這些下拉串列評估它們是否有多個或只有一個選項可用。如果我只有一個選項，它將使用單個可能的值自動完成。

這是作業表的一個示例：

我使用的代碼：

var mainWsName = "Page 1";
var optionsWsName = "Data";
var firstLevelColumn = 1; 
var secondLevelColumn = 2;
var thirdLevelColumn = 5;

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var options = wsOptions.getRange(2,1,wsOptions.getLastRow()-1,7).getValues(); 


function onEdit(e){
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
  var row = e.range.getRow();
  var col = e.range.getColumn();
  
  var activeCell = e.range;
  var val = activeCell.getValue();                
  var r = activeCell.getRow();                   
  var c = activeCell.getColumn();                
  var wsName = activeCell.getSheet().getName()

  if(wsName === mainWsName && c === firstLevelColumn && r > 3){
    applyFirstLevelValidation(val,r)
  }else if(wsName === mainWsName && c === secondLevelColumn && r > 3){
    applySecondLevelValidation(val,r)
  }
  
  if(c === 1 && r > 3 && wsName === mainWsName && val === "intern"){    
    e.source.getActiveSheet().getRange(row,6).setValue("Not");
  }else if(c === 1 && r > 3 && wsName === mainWsName && val !== ""){
    e.source.getActiveSheet().getRange(row,6).setValue("Yes");
  }else if(c === 1 && r > 3 && wsName === mainWsName && val === ""){
    e.source.getActiveSheet().getRange(row,6).clearContent();
    ws.getRange(r,secondLevelColumn).clearDataValidations();
  }
}

function applyFirstLevelValidation(val,r){

  if(val === ""){
  ws.getRange(r,secondLevelColumn).clearContent();
  ws.getRange(r,secondLevelColumn).clearDataValidations();
  ws.getRange(r,thirdLevelColumn).clearContent();
  ws.getRange(r,thirdLevelColumn).clearDataValidations();
  } else{
  ws.getRange(r,secondLevelColumn).clearContent();
  ws.getRange(r,secondLevelColumn).clearDataValidations();
  ws.getRange(r,thirdLevelColumn).clearContent();
  ws.getRange(r,thirdLevelColumn).clearDataValidations();
  var filteredOptions = options.filter(function(o){ return o[0] === val });
  var listToApply = filteredOptions.map(function(o){ return o[1] });
  var cell = ws.getRange(r,secondLevelColumn);
  

  applyValidationToCell(listToApply,cell);

  
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(listToApply).build();//creo el dropdown
  if(listToApply[1] || null){
    applyValidationToCell(listToApply,cell);
  }else{
    cell.setDataValidation(rule).setValue(listToApply[0] || null);
  }
  }
}

function applySecondLevelValidation(val,r){

  if(val === ""){
  ws.getRange(r,thirdLevelColumn).clearContent();
  ws.getRange(r,thirdLevelColumn).clearDataValidations();
  } else{
  ws.getRange(r,thirdLevelColumn).clearContent();
  var firstLevelColValue = ws.getRange(r,firstLevelColumn).getValue();
  var filteredOptions = options.filter(function(o){ return o[0] === firstLevelColValue && o[1] === val });
  var listToApply = filteredOptions.map(function(o){ return o[2] });
  var cell = ws.getRange(r,thirdLevelColumn);
  

  applyValidationToCell(listToApply,cell);

  
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(listToApply).build();
  cell.setDataValidation(rule).setValue(listToApply[0] || null);
  }
}

function applyValidationToCell(list,cell) {

  var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();

  cell.setDataValidation(rule);




}

uj5u.com熱心網友回復：

雖然我不確定我是否能正確理解你的問題，例如，下面的修改是你期望的結果嗎？在本次修改中，請修改applyFirstLevelValidation如下功能。

從：

if(listToApply[1] || null){
  applyValidationToCell(listToApply,cell);
}else{
  cell.setDataValidation(rule).setValue(listToApply[0] || null);
}

到：

if (listToApply[1] || null) {
  applyValidationToCell(listToApply, cell);
} else {
  cell.setDataValidation(rule).setValue(listToApply[0] || null);
  applySecondLevelValidation(listToApply[0], r); // <--- Added
}

標籤：谷歌应用脚​​本 谷歌表格

上一篇：使用谷歌表查找不同投資的XIRR

下一篇：如何將符合條件的每一行的日期值從C列復制到F列？