Dropdown auto generating a range based on a total

Question

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.

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.

Example Sheet

I don't understand what you're trying to accomplish. Is there a certain amount of dropdowns in column B? How does the number specified in there modify the different Stock Quantities in column F, considering that the are many more items there than there are dropdowns? Also, how to track which dropdowns should modify which Stock Quantities? Please consider making all this clear so that people can help you. — Iamblichus
Thanks for your reply. Have a look at sheet "Working with script" 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 hopes this helps clarify — Joel Davidson

Iamblichus Iamblichus · Accepted Answer · 2020-01-28T10:21:22

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):

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.

I hope this is of any help.

Dropdown auto generating a range based on a total

1 Answers