Dependent dynamically populated dropdown list Google Sheet

Question

I would simply like to autopopulate the Point Segment with all the segments linked to a specific Street, when the Street name is inputted. When Street name is inputted into column C, Column D should have a dropdown containing only the point segments of that street. While I realize this can simply be achieved by creating a filter in the Data tab, I am trying to create a form which does not allow this and therefore need to script it.

This is the Google sheet:

https://docs.google.com/spreadsheets/d/1QbTqPegE_GLj9V6x5uCNNXAoi0v12Pmaelhc7uaMknE/edit?usp=sharing

I have written this code, however I am having trouble filtering by Street.

function setDataValid_(range, sourceRange) {
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, 
true).build();
range.setDataValidation(rule);
}
function onEdit() {
var aSheet = SpreadsheetApp.getActiveSheet();
var aCell = aSheet.getActiveCell();
var aColumn = aCell.getColumn();

if (aColumn == 3 && aSheet.getName() == 'Sheet1') {
    var range = aSheet.getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = aSheet.getRange('Sheet1!B2:B5131');
    setDataValid_(range, sourceRange)
  }
}

Any help would be much appreciated.

Answer 1

You're close, but you should use requireValueInList instead of requireValueInRange. You were passing sourceRange, which is equal to all of the point segments.

To accomplish the filtering, you need to look at all of the street values. If the street value matches the selection, then save the adjacent point segment to a separate list. Once you've saved all those point segments, then pass it to requireValueInList. To do this, you need to take advantage of getValues() to get the range values as an array and loop through it.

I've made a few other modifications:

• In onEdit, you should use the an event object
• Changed your variable names so they would be easier to understand
• Added a check to make sure that the Street cell isn't blank (no need to trigger the action when you delete a value from the cell)
• Added a function getPointSegments that does the filtering

• Removed the setDataValid_ function as it made your code less readable, and in my opinion, wasn't worthy of being its own function

  function onEdit(event) {
    var eventSheet = event.range.getSheet();
    var eventCell = event.range;
    var eventColumn = eventCell.getColumn();
    var eventValue = eventCell.getValue();
    if (eventColumn == 3 && eventSheet.getName() == "Sheet1" && eventValue != "") {
      var pointRange = eventSheet.getRange(eventCell.getRow(), eventColumn + 1);
      var pointSegments = getPointSegments_(eventSheet, eventValue);
      var rule = SpreadsheetApp.newDataValidation().requireValueInList(pointSegments, true).build();
      pointRange.setDataValidation(rule);
    }
  }
  
  function getPointSegments_(sheet, selectedStreet) {
    var streetsAndPoints = sheet.getRange("A2:B").getValues();
    var pointSegments = [];
    for (var i=0; i<streetsAndPoints.length; i++) {
      var street = streetsAndPoints[i][0];
      var pointSegment = streetsAndPoints[i][1];
      if (street === selectedStreet)
        pointSegments.push(pointSegment);
    }
    return pointSegments;
  }
  

Lastly, be sure that your data validations in the Street field look like this (and I would actually suggest "Reject input" on invalid data).