下拉列表会根据总计自动生成一个范围 [英] Dropdown auto generating a range based on a total

问题描述

我正在尝试为用户创建一个工作表，以供用户从下拉列表中选择一定数量的项目，并且该工作表引用了TOTAL库存数量.

I am trying to create a sheet for users to select a quantity of items from a drop down list and it reference a TOTAL stock quantity amount.

我很幸运地找到一个脚本来填充一个引用范围的下拉列表，但是笨拙地不得不生成范围内的所有项目.

I have had some luck finding a script to fill a dropdown that references a range but is clunky having to generate all the items in the range.

我希望下拉列表(B列)引用总袜子(C列)，它填充了下拉列表，而无需在D-Z列中显示范围.

I would like the dropdowns (Column B) to reference the total sock (Column C) and it fill the dropdown without me having to render the range in columns D-Z.

示例表

推荐答案

如果我对您的理解正确，那么您想在B列的每个单元格中创建一个下拉列表，其选项是从0到对应的Stock Quantity的整数C列.

If I understand you correctly, you want to create a dropdown in each cell in column B whose options are integers that go from 0 to the corresponding Stock Quantity in column C.

在这种情况下，您可以将以下函数复制到绑定到电子表格的脚本中:

If that's the case, you can copy the following function to the script bound to your spreadsheet:

function generateDropdowns() {
  var ss = SpreadsheetApp.getActive(); // Get the spreadsheet bound to this script
  var sheet = ss.getSheetByName("Working with script"); // Get the sheet called "Working with script" (change if necessary)
  // Get the different values in column C (stock quantities):
  var firstRow = 2;
  var firstCol = 3;
  var numRows = sheet.getLastRow() - firstRow + 1;
  var stockQuantities = sheet.getRange(firstRow, firstCol, numRows).getValues();
  // Iterate through all values in volumn:
  for (var i = 0; i < stockQuantities.length; i++) {
    var stockQuantity = stockQuantities[i][0];
    var values = [];
    // Create the different options for the dropdown based on the value in column C:
    for (var j = 0; j <= stockQuantity; j++) {
      values.push(j);
    }
    // Create the data validation:
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
    // Add the data validation to the corresponding cell in column B:
    var dropdownCell = sheet.getRange(i + firstRow, 2).setDataValidation(rule);
  }
}

此脚本执行以下操作(请查看嵌入式注释以获取更多详细信息):

This script does the following (check inline comments for more detailed information):

• 首先，它通过 getRange 和 getValues
• 对于每个库存数量，它会为B列中的相应下拉列表创建一个具有不同选项的数组.
• 从每个数组中，它使用创建下拉列表newDataValidation 和 requireValueInList(值) ，然后使用 setDataValidation .

• First, it gets all the stock quantities in column C via getRange and getValues.
• For each stock quantity, it creates an array with the different options for the corresponding dropdown in column B.
• From each array, it creates the dropdown with newDataValidation and requireValueInList(values), and then sets it to the corresponding cell in column B with setDataValidation.

我希望这会有所帮助.

这篇关于下拉列表会根据总计自动生成一个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！