Dropdown fill from dataset making range

Question

TLDR: script to fill dropdown creating integers that go from 0 to the corresponding Stock Quantity from dataset.

What I am trying to make is a workbook that users can select from a dropdown of a group of items (Sheet1, Column A) and then Row B lookup that selected item in sheet "Dataset" and return that value with integers that go from 0 to the corresponding stock quantity total in (Sheet "Dataset" column C)

here is a Sample spreadsheet

this is all working thanks to @iamblichus and @Rafa Guillermo

but a also have items that do not require users to select the item from a dropdown.

I have tried to write some code (line 39 down) but i am stuck.

Any help would be very appreciated.

    function generateDropdowns() {

//sets all the dropdowns from items non selected

  // Get the different values in column C (stock quantities):
  var firstRow = 3;
  var firstCol = 3;
  var numRows = dataSetSheet.getLastRow() - firstRow + 1;
  var numRowsfill = fillSheet.getLastRow() - firstRow + 1; 
  var stockQuantities = dataSetSheet.getRange(firstRow, firstCol, numRows).getValues();
  var stockNames = dataSetSheet.getRange(firstRow, firstCol - 1, numRows).getValues();
  var itemName = fillSheet.getRange(3, 1, numRowsfill).getValues();

  // Iterate through all values in volumn:
  for (var i = 0; i < stockQuantities.length; i++) {
    Logger.log(stockNames);
    Logger.log(stockQuantities);
    var stockQuantity = stockQuantities[i][0];
    var values = [];

    // Create the different options for the dropdown based on the value in column C:
    if (stockNames[i] == itemName[i]) {
      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 = fillSheet.getRange(i + firstRow, 2).setDataValidation(rule);

   }    
  }
}

Past posts Dropdown auto generating a range based on a total enter link description here

Iamblichus Iamblichus · Accepted Answer · 2020-02-26T14:34:53

Generating dropdown from edited cell:

In the sample provided by @Rafa Guillermo, items that are simply written, and not selected from a dropdown, follow the same workflow than items selected from a dropdown. The script just looks at which value the edited cell has, disregarding whether it was selected from a dropdown or not. You can notice this if you write a value in column A (from Sheet 1): the corresponding dropdown in column B will show up.

So the code provided in this answer already does what you say you want to accomplish.

Generating all dropdowns:

If what you want to do instead is automatically generating all the dropdowns from the values in Sheet 1, and not only the one corresponding to the cell that was edited, you can do this instead (check inline comments):

function generateDropdowns() {
  var firstRow = 3;
  var firstCol = 3;
  var numRows = dataSetSheet.getLastRow() - firstRow + 1;
  var numRowsfill = fillSheet.getLastRow() - firstRow + 1; 
  var stockQuantities = dataSetSheet.getRange(firstRow, firstCol, numRows).getValues().map(function(quantity) {
    return quantity[0]; // 1-D array of stock quantities
  });
  var stockNames = dataSetSheet.getRange(firstRow, firstCol - 1, numRows).getValues().map(function(stockName) {
    return stockName[0]; // 1-D array of stock names
  });
  var itemNames = fillSheet.getRange(3, 1, numRowsfill).getValues().map(function(itemName) {
    return itemName[0]; // 1-D array of items
  });
  for (var i = 0; i < itemNames.length; i++) { // Iterate through all items in Sheet 1
    if (stockNames.indexOf(itemNames[i]) !== -1) { // Check if item shows up in stock names
      var stockQuantity = stockQuantities[stockNames.indexOf(itemNames[i])]; // Get the stock quantity corresponding to this item
      var values = [];
      for (var j = 0; j <= stockQuantity; j++) {
        values.push(j); // Generate possible values for dropdown
      }
      var rule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
      var dropdownCell = fillSheet.getRange(i + firstRow, 2).setDataValidation(rule);      
    }
  }
}

Dropdown fill from dataset making range

1 Answers

Generating dropdown from edited cell:

Generating all dropdowns: