Dependent validation in Google Spreadsheets

Question

Here's what I'd like to do. The first selection is Subject (as in a school course). There are 5 subjects. Then there are three sub-selections in drop down lists for each subject:

• Subject
• Test Type
• Practice Method
• Feedback

In Excel, this is done with Indirect. Is there something comparable in Google Spreadsheets?

Answer 1

I've seen a few answers around the web, but they have their limitations, so I fiddled around a bit and made a script that works pretty well.

There is a slight delay between selecting the initial value and the dependent drop down changing, but it does have the advantage that dependencies retain their validation correctly so any errors are clearly marked as invalid.

Here is an example sheet.

Here is the code:

/**
 * @constant {Array.object}
 * A list of trigger definitions - edit to your liking
 **/
var TRIGGERS = [{triggerColumn: 1, targetColumn: 2, lookupSheet: 2},
                {triggerColumn: 2, targetColumn: 3, lookupSheet: 3}
               ] ;
var MAIN_SHEET = "Sheet1";
var ERROR_EMAIL_RECIPIENT = "your email address here";


/**
* Update validation options based on selection
*
* @param {{user: User, source: Spreadsheet, range: Range, value: string}} e The on-edit event
*
**/
function updateOptions(e) {
  "use strict";
  // ignore range changes (only single cell changes will have a value)
  if ((typeof e.value === "undefined") || e.source.getActiveSheet().getName() !== MAIN_SHEET) {
    return;
  }

  var ss = e.source;
  var sheets = ss.getSheets();
  var selectedValue = e.value;
  var r = e.range;

  var location = {row: r.getRow(), column: r.getColumn()};


  var triggerDefn = getTriggerDefn(location.column);
  if (triggerDefn){

    var target_cell = sheets[0].getRange(location.row, triggerDefn.targetColumn);

    var lookupValues = sheets[triggerDefn.lookupSheet].getDataRange().getValues();

    var options = getOptions(selectedValue, lookupValues);

    var validation = SpreadsheetApp.newDataValidation().requireValueInList(options);

    target_cell.setDataValidation(validation);
  }

}
/**
 * Checks if the given column is a trigger column.
 *
 * @param {integer} column
 *
 * @return {object} the TRIGGER definition object whose column matches the given column (or null if none)
 **/
function getTriggerDefn(column){
  "use strict";
  for (var i = 0; i < TRIGGERS.length; i++){
    if (column === TRIGGERS[i].triggerColumn) {
      return TRIGGERS[i];
    }
  }
  return null;
}

/**
* Returns an array of values that represent the dependent options for a particular value.
* The lookup array has indices in the first column, and values in following columns
*
* @param {string}  value  The index you are looking for
* @param {Array.Array.string}  lookup_values  The lookup table as a 2d array
*
**/
function getOptions(value, lookup_values){
  "use strict";
  var options = [];
  for (var i = 0; i < lookup_values.length; i++) {

    if (lookup_values[i][0] === value){
      options = lookup_values[i].slice(1);
      break;
    }
  }
  return options.filter(function(el){return el.length > 0;});
}

/**
 * Just a test function
 **/

function testUpdateOptions(){
  "use strict";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var e = {user: null, source: ss, range: ss.getActiveRange(), value: "Vegetable"};
  updateOptions(e);
}

/**
 * Set an onEdit trigger on this function if you need to debug the trigger,
 * otherwise you can set it on updateOptions
 **/
function debugUpdateOptions(e){
  "use strict";
  try {
    updateOptions(e);
  } catch (err) {
    MailApp.sendEmail(ERROR_EMAIL_RECIPIENT, "Script error", err);
  }
}

Answer 2

I needed something similar recently, and was not satisfied with other approaches. Here's an example file with named ranges and apps script to do the work.

New categories and subcategories may be added to the second sheet, in any order. The first sheet's category data entry depends on a static validation for the entire column. Validations in the subcategory column are created or modified by the script as needed.

function setDataValid_(vRange, sRange) {
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(sRange.getValues(),true).build();
  vRange.setDataValidation(rule);
}

function onEdit() {
  var Sheet = SpreadsheetApp.getActiveSpreadsheet()
  var aCell = Sheet.getActiveCell();
  //add a conditional to stop execution if aCell is blank -- or to delete nextRange validation and value if it is
  if (aCell.getColumn() == 1 && Sheet.getSheetName() == 'Data entry')  //This is the one hardwired reference in the script. Terminates execution in other columns. 
  {
    var sRange = Sheet.getRangeByName('scratch');
    var sCount = Sheet.getRangeByName('count');
    sRange.getCell(1, 1).setFormula("=QUERY(Items,\"SELECT B WHERE A='" + aCell.getValue() + "'\")");
    var vRange = aCell.offset(0,1);
    Logger.log(sCount.getValue())
    if (sCount.getValues() == 1)
    {
      vRange.setValue(sRange.getValue())
    }
    setDataValid_(vRange, sRange)
  }
}