根据相关的下拉列表/多个条件更新单元格值 [英] Update cell values based on dependent drop down lists/multiple criteria

问题描述

我已基于此教程.这是我创建的工作表的副本.创建一个类似于主表C列中当前存在的下拉列表的方法，我的目标是使用下拉列表中可用的每个唯一值自动填充C列中的单元格.我已经尝试通过VLOOKUP ，但仅在响应中出现错误，这可能是由于缺少验证或我不熟悉的某种其他形式的逻辑所致.如果对A列或B列的选项进行了修改(类似于当前脚本的工作方式)，我也对C列中的填充选项clear(clearContent)感兴趣.在解决这个问题上的任何帮助将不胜感激.

I've created a google sheet with dependent drop down lists based on this script/tutorial. Here's a copy of the sheet I've created. Instead of creating a drop down list like the one currently present in column C of the master sheet, my goal is to auto populate cells in column C with each unique value available in the drop down list. I've attempted this via VLOOKUP but only got errors in response, likely due to a lack of validation or some other form of logic I'm not familiar with. I'm also interested in having the populated options in column C clear (clearContent) if either column A or column B options are modified, similar to how the current script works. Any help in figuring this out would be greatly appreciated.

推荐答案

• 当更改"A"列和"B"列的下拉列表时，您要将选项"表中"C"列的相关值放入"C"列的单元格中.
  • 例如，当列"A"和列"B"的下拉列表为 apple 和 1_dormant 时，您要放置疫霉菌，欧洲红螨，鳞片到"C"列的单元格.
  • When the drop down lists of the column "A" and column "B" are changed, you want to put the related values of the column "C" in the sheet of "options" to the cells of column "C".
    • For example, when the drop down lists of the column "A" and column "B" are apple and 1_dormant, you want to put the values of Phytophthora collar rot, European red mite, Scale to the cells of column "C".

    如果我的理解是正确的，那么这个答案如何?请认为这只是几个可能的答案之一.

    If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

    • 在您的脚本中，我认为可以使用 applySecondLevelValidation 函数的 listToApply .使用此功能，可以将值放在"C"列中.
    • 关于清除列"C"的脚本，我认为可以使用 ws.getRange(2，3，ws.getLastRow()-1，1).clearContent();
    • In your script, I think that listToApply of the function of applySecondLevelValidation can be used. Using this, the values can be put to the column "C".
    • About the script for clearing the column "C", I think that ws.getRange(2, 3, ws.getLastRow() - 1, 1).clearContent(); can be used.

    修改脚本后，请按以下方式修改 applyFirstLevelValidation 和 applySecondLevelValidation 的功能.

    When your script is modified, please modify the functions of applyFirstLevelValidation and applySecondLevelValidation as follows.

    function applyFirstLevelValidation(val,r){
        ws.getRange(2, 3, ws.getLastRow() - 1, 1).clearContent(); // Added
    
        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){
        ws.getRange(2, 3, ws.getLastRow() - 1, 1).clearContent(); // Added
    
        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]]}); // Modified
          ws.getRange(r,thirdLevelColumn,listToApply.length, 1).setValues(listToApply); // Modified
        }
    }
    

    注意:

    • 如果只想将值放入单元格"C2"，请按如下所示修改上面的脚本.

      Note:

      • If you want to put the values to only the cell "C2", please modify above script as follows.

        • 来自

        var listToApply = filteredOptions.map(function(o){return [o[2]]});
        ws.getRange(r,thirdLevelColumn,listToApply.length, 1).setValues(listToApply);
        

      • 收件人

      • To

        var listToApply = filteredOptions.map(function(o){return o[2]});
        ws.getRange(r,thirdLevelColumn).setValue(listToApply.join(","));
        

      • getRange(row，列，numRows，numColumns
      • setValues(values)

      如果我误解了你的问题，而这不是你想要的方向，我深表歉意.

      If I misunderstood your question and this was not the direction you want, I apologize.

      这篇关于根据相关的下拉列表/多个条件更新单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！