On the Fly Data Validation Values in Google Script

I’m helping the School in France do some payment logging. We have five different activity types with different payment increment rules . . . so if you’re teaching a class you have a minimum of 90 mins, a minimum step of 30, and a max of 150 mins. For Tutoring it’s 15 minute minimum, 15 minute increment, and a max of 75 mins. You get the idea. We could do that in a form with conditional logic, but I was trying to keep it within the spreadsheet to make it easier to bulk enter and to see the aggregate data and enter it all in one place.

The following Google Script will read the data validated choice (just a regular drop down) in column B and build the data validation in column C based on that choice. It’ll update it whenever those items are changed as individual items.

I created a hidden sheet that holds our data for dropdowns.

function onEdit(){
 
  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();//get workbook
  const entries = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("entries");//get sheet for activity entries
  const dropDowns = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("dropdowns");//get dropdown data sheet
  const activeCell = entries.getCurrentCell();//what sheet is chosen
  const activity = activeCell.getValue();//what value is there

  if(activeCell.getColumn() == 2 && activeCell.getRow() > 1){
    activeCell.offset(0,1).clearContent().clearDataValidations();//clear existing data validations in column 2 and rows 2 onward
   
 if(activeCell.isBlank()) {
//do nothing
    }

  //set the key and values for the dropdown data you want for each activity type
  const dropDownValues = {
   Conferences: "B2:B10",
   Classes: "C2:C10",
   Excursions: "D2:D10",
   Tutoring: "E2:E10",
   Workshops: "F2:F10"
}; 

  const rangeData = dropDownValues[activity];//get the right range based on the activity
  const getValueRange = dropDowns.getRange(rangeData); //get the data
  const confValidationRule = SpreadsheetApp.newDataValidation().requireValueInRange(getValueRange).build();//make the validation
   
  activeCell.offset(0,1).setDataValidation(confValidationRule);//apply it one column to the right of the currently active cell
   
  }
}