时间戳

首页 > 解决方案 > 谷歌表格：“如果项目“缺货”，则从数据验证列表中删除项目

javascript - 谷歌表格：“如果项目“缺货”，则从数据验证列表中删除项目

问题描述

我一直在尝试找到解决我的 google sheet 问题的方法，并且到目前为止已经尝试了很多解决方法，但无济于事。我想我可能需要使用 javascript 来执行操作，但是，我是 javascript 的初学者。

这是我希望工作表执行的操作。

  1. 检查'sheet#2'并查看指定范围内的项目是否设置为“缺货”

表#2

在此处输入图像描述

  1. 如果范围中的项目设置为“缺货”，则从“工作表#1”上的数据验证列表中删除该项目，如果它是“有库存”，则在数据验证列表中显示该项目.

表#1

在此处输入图像描述

以下是我迄今为止尝试过的一些事情（如果有帮助的话）：

  1. 在工作表#2 上，当项目设置为缺货时，对其进行条件格式化，以使单元格文本变为白色（不可见）。这适用于该特定工作表。但是，数据验证列表不会完全按照它的外观显示它提取的数据（因此文本仍然显示在下拉列表中）

  2. 如果声明。我已经尝试了整个 LOTTA IF 和 IFS 语句。这不起作用，因为您不能在与数据验证单元格相同的单元格中使用 if 语句，并且 b/c 将来可能需要不断更改单元格名称。（但是，我确实找到了一个解决方法来解决我现在试图解决一天的另一个问题 WHOOP WHOOP！）

  3. 哦，我还尝试对单元格进行条件格式设置，这样如果 = 缺货，单元格就会变白...但是我收到一个错误，即条件格式不适用于 2 张纸

......所以是的，如果有人可能知道解决方案来解决这个问题，我将永远感激它！与此同时，我会继续在谷歌上搜索解决方案。

标签： javascriptexcelgoogle-apps-scriptgoogle-sheets

解决方案

为简单起见，我创建了一个包含两张表的示例电子表格：Sheet 1 和“Stock”。

表 1

表 1

库存

库存

解决方案

我创建了一个 Apps 脚本函数来设置您指定的数据验证。

我使用 Javascriptfilter和map函数来简化获取数据，但基本上：

  1. 我从 Stock 表中获取所有行并将它们加载到“内存”中
  2. 然后我从“内存”中丢弃第二列 ( row[1]) 不等于“库存”的行。
  3. 然后我得到剩余的行并只抓取它们的第一列（row[0]）。

然后我创建一个新的DataValidation对象（使用它的builder），它使用我们上面得到的列表并将其分配给我想要验证的范围。

//VARIABLES
  var rangeToValidate = "B2:B";
  var validateSheet = "Sheet1";
  var optionSheet = "Stock";

function refreshDataValidation() {
  
  var inStockOptions = SpreadsheetApp.getActive().getSheetByName(optionSheet).getDataRange().getValues()
  .filter(function (row) { return (row[1]=="In Stock")})
  .map(function(row) {return row[0]});
  
  SpreadsheetApp.getActive().getSheetByName(validateSheet).getRange(rangeToValidate)
  .setDataValidation(
    SpreadsheetApp.newDataValidation().requireValueInList(inStockOptions).build()
  );
  
}

此外，通过使用 Apps 脚本添加一些代码，该工作表将确保您的下拉菜单始终是最新的。

每次打开工作表以及每次对 B 列上的 Stock 工作表进行编辑时，我都会刷新下拉列表。

//When the sheet is opened, refresh the data validation
function onOpen(e) {
  refreshDataValidation();
}

//When a change is made to the Stock sheet, also refresh validation
function onEdit(e) {
  var range = e.range;
  if (range.getSheet() == SpreadsheetApp.getActive().getSheetByName(optionSheet) && range.getColumn() == 2) {
    refreshDataValidation();
  }
}

希望这可以帮助！

推荐阅读