相關下拉串列自動完成-谷歌表格腳本編輯器

我有一張有多個相關下拉串列的作業表。我從教程中獲取了基本代碼。現在我需要當我在下拉串列中有一個可用的選項時（例如在“列任務”中）它會自動完成。

這是一個示例： https ://docs.google.com/spreadsheets/d/13fIO3OgAALDVx5PmX6mx0RMdES4PUdsSBU9aBOQhwuk/edit?usp=sharing

我使用的代碼：

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("No");
  }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();
  }
}//end onEdit

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);
  }
}

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);
  }
}

function applyValidationToCell(list,cell) {

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

  cell.setDataValidation(rule);


}

uj5u.com熱心網友回復：

在您的情況下，如何進行以下修改？

修改后的腳本：

在本次修改中，applySecondLevelValidation修改如下。

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);
    
    // I modified below script.
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(listToApply).build();
    cell.setDataValidation(rule).setValue(listToApply[0] || null);
  }
}

• 在這個修改中，資料驗證被放置后，第一個元素listToApply被設定。

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

上一篇：通過Python更改資料框中某些單元格的值-將它們更改為與前一行相同的值

下一篇：如何創建輪班時鐘AppScript