Google-sheets – Dependent validation in Google Spreadsheets

google sheetsgoogle-apps-script

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?

Best Answer

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);
  }
}
Related Topic