Google表格-依赖下拉列表 [英] Google Sheets - Dependent drop-down lists

问题描述

我正在重新创建和扩展以前制作的文档。我已经引入了最初使用的脚本，并在我认为合适的地方对其进行了调整，以使其能够在本工作表中使用，但我一定错过了一些东西。可以在此处中找到涉及的3个电子表格文件的可编辑示例。 。这些文件是示例价格表，目录（汇总所有价格表中的制造商名称，并且还具有目录选项卡，用于显示我的主要供应商之一未出售的其他项目）和附录B（其中是我需要帮助的文件。

I am recreating and expanding on a doc I had previously made. I have already brought in the script I had used originally, and tweaked it where I believed appropriate to get it working in this sheet, but I must have missed something. Editable samples of the 3 spreadsheet files involved can be found here. These files are a sample "Price List", "Catalog"(which aggregates manufacturer names from all price lists, and also has a "Catalog" tab for misc items not sold by one of my primary vendors), and "Addendum B" which is the file I require assistance with.

此文档是我的合同的附录，其中列出了作为该合同一部分出售的所有设备。它有两张纸（附录B和 XREF），附录B有几个相关的列：供应商，制造商，型号，说明和价格。它们之间的依赖关系如下：

This document is an addendum to my contracts which lists all equipment being sold as part of that contract. It has 2 sheets in it ("Addendum B" and "XREF"), and "Addendum B" has several dependent columns: Vendor, Manufacturer, Model, Description, and Price. Their dependencies are as follows:

当前正在工作

• 供应商：从XREF！A2：A获取基本数据验证。

不起作用，脚本在文件中

• 制造商：根据所选的供应商，应该是一个下拉的
  列表，该列表由在
  XREF上带有该供应商名称的列生成。

现在，这比我以前做的要复杂得多。

• 型号：我希望此列为下拉列表，列出与该制造商关联的所有型号
  ，并从提供的完全独立的价格
  清单中我由我的供应商。 （我共享了一个示例价目表，该价目表反映了所有这些文件中出现的列位置。

• Model: I want this column to be a drop-down listing all model numbers associated with that manufacturer, from a completely separate price list provided to me by my vendor. (I have shared a sample price list which reflects column positions as they appear in all such files.

说明：从中显示所选模型的相应说明。

Description: Displays the corresponding description for the Model selected, from the price list selected in the Vendor column.

价格：显示在供应商列中选择的价格列表中所选模型的相应加价。 / p>

Price: Displays the corresponding markup price for the Model selected, from the price list selected in the Vendor column.

这就是概述我的目标以及我正在努力的目标。

And that about summarizes my goals and what I'm struggling with.

推荐答案

所以我在附录B中调查了您的脚本文件。
我进行了很少的编辑，并且现在应该可以使用，修改后的代码：

So I looked into your script file in sheet Addendum B. I have made few edits and it should be working now, the modified code:

    function onEdit() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getActiveSheet(),
      name = sheet.getName();
  if (name != 'Addendum B') return;
  var range = sheet.getActiveRange(),
      col = range.getColumn();
  if (col != 6) return;  //You col was set to 5 changed it to 6!
  var val = range.getValue(),
      dv = ss.getSheetByName('XREF'),
      data = dv.getDataRange().getValues(),
      catCol = data[0].indexOf(val),
      list = [];
  Logger.log(catCol)
  for (var i = 1, len = 100; i < len; i++) // Problem is here, you have too many items in list! Cannot have more 500 items for validation
      list.push(data[i][catCol]);
  var listRange = dv.getRange(2,catCol +1,dv.getLastRow() - 1, 1)
  Logger.log(list)
  var cell = sheet.getRange(range.getRow(), col-1)
  var rule = SpreadsheetApp.newDataValidation()
  .requireValueInRange(listRange)   // Use requireValueIn Range instead to fix the problem
  .build();
  cell.setDataValidation(rule);
  Logger.log(cell.getRow())
}

原因如果您的数据验证列表中有500多个项目，则您的验证不起作用。我只是对其进行了修改，以取而代之的是相同的值。希望对您有所帮助！

The reason your validation was not working was you had more than 500 items in your data validation list. I just modified it to take the same values from range instead. Hope you find that helpful!

现在剩下的3个问题，这是我对此的评论和想法：

Now for the remaining 3 questions, here are my comments and thoughts on it:

1）我没有找不到与您在问题中提到的问题相关的任何代码。因此，我假设您正在寻求有关如何实现此目标的一般想法？

1) I didn't find any code related to the problem you mentioned in your question. So, I am gonna assume you are asking for general ideas on how to achieve this?

2）与上述代码一样，您基本上可以解决问题！选择制造商后，脚本将在工作表中查找该制造商，并在相应的模型列中更新数据验证。
您将像这样修改代码

2) You basically approach the problem the same as you did with the above code! Once a manufacturer is selected, the script looks for that manufacturer in the sheet and update the Data validation in the corresponding model column. You will modify the code like so

var ss = SpreadsheetApp.openById("1nbCJOkpIQxnn71sJPj6X4KaahROP5cMg1SI9xIeJdvY")
//The above code with select the catalog sheet.
dv = ss.getSheetByName('Misc_Catalog')
//The above code will open the Misc_Catalog tab. 

3）更好的方法是使用补充工具栏/对话框来验证您的输入，然后将其添加到最后的工作表。 （看起来更干净，还可以防止工作表中的编辑触发器不必要，这可能需要一段时间才能更新。）
您可以在此处找到更多详细信息： https://developers.google.com/apps-script/guides/dialogs

3) A better approach would be to use Sidebar/Dialog Box to validate your input then add it to the sheet at the end. (Looks Cleaner and also prevents unnecessary on edit trigger in the sheet, which can take a while to update.) You find more details here: https://developers.google.com/apps-script/guides/dialogs

这篇关于Google表格-依赖下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！