Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 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);
  }
}
share|improve this answer
1  
Can you explain the usage of the phrase "use strict"; in your code? –  Jacob Jan Tuinstra Oct 22 '13 at 7:48
    
JS Hint got bossy, so I looked around and discovered JS Strict Mode. It's part of my arsenal now. –  Tom Horwood Oct 22 '13 at 15:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.