[validation] How do you do dynamic / dependent drop downs in Google Sheets?

How do you get a sub-category column to populate a drop down based on the value selected in the main category drop down in google sheets?

I googled around and couldn't find any good solutions, therefore I wanted to share my own. Please see my answer below.

This question is related to validation google-apps-script google-sheets

The answer is


Caution! The scripts have a limit: it handles up to 500 values in a single drop-down list.

Multi-line, multi-Level, multi-List, multi-Edit-Line Dependent Drop-Down Lists in Google Sheets. Script

More Info


This solution is not perfect, but it gives some benefits:

  1. Let you make multiple dropdown lists
  2. Gives more control
  3. Source Data is placed on the only sheet, so it's simple to edit

First of all, here's working example, so you can test it before going further.

When you choose one option, script makes new validation rule

Installation:

  1. Prepare Data
  2. Make the first list as usual: Data > Validation
  3. Add Script, set some variables
  4. Done!

Prepare Data

Data looks like a single table with all possible variants inside it. It must be located on a separate sheet, so it can be used by the script. Look at this example:

Sourse Data

Here we have four levels, each value repeats. Note that 2 columns on the right of data are reserved, so don't type/paste there any data.


First simple Data Validation (DV)

Prepare a list of unique values. In our example, it is a list of Planets. Find free space on sheet with data, and paste formula: =unique(A:A) On your mainsheet select first column, where DV will start. Go to Data > Validation and select range with a unique list.

4 columns right from data


Script

Paste this code into script editor:

_x000D_
_x000D_
function onEdit(event) _x000D_
{_x000D_
_x000D_
  // Change Settings:_x000D_
  //--------------------------------------------------------------------------------------_x000D_
  var TargetSheet = 'Main'; // name of sheet with data validation_x000D_
  var LogSheet = 'Data1'; // name of sheet with data_x000D_
  var NumOfLevels = 4; // number of levels of data validation_x000D_
  var lcol = 2; // number of column where validation starts; A = 1, B = 2, etc._x000D_
  var lrow = 2; // number of row where validation starts_x000D_
  var offsets = [1,1,1,2]; // offsets for levels_x000D_
  //                   ^ means offset column #4 on one position right._x000D_
  _x000D_
  // =====================================================================================_x000D_
  SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets);_x000D_
  _x000D_
  // Change Settings:_x000D_
  //--------------------------------------------------------------------------------------_x000D_
  var TargetSheet = 'Main'; // name of sheet with data validation_x000D_
  var LogSheet = 'Data2'; // name of sheet with data_x000D_
  var NumOfLevels = 7; // number of levels of data validation_x000D_
  var lcol = 9; // number of column where validation starts; A = 1, B = 2, etc._x000D_
  var lrow = 2; // number of row where validation starts_x000D_
  var offsets = [1,1,1,1,1,1,1]; // offsets for levels_x000D_
  // =====================================================================================  _x000D_
  SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets);_x000D_
_x000D_
  _x000D_
}_x000D_
_x000D_
_x000D_
_x000D_
function SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets) _x000D_
{_x000D_
  //--------------------------------------------------------------------------------------_x000D_
  // The event handler, adds data validation for the input parameters_x000D_
  //--------------------------------------------------------------------------------------_x000D_
  _x000D_
  var FormulaSplitter = ';'; // depends on regional setting, ';' or ',' works for US_x000D_
  //--------------------------------------------------------------------------------------_x000D_
  _x000D_
  // ===================================   key variables  =================================_x000D_
  //_x000D_
  //  ss   sheet we change (TargetSheet)_x000D_
  //   br    range to change_x000D_
  //   scol   number of column to edit_x000D_
  //   srow   number of row to edit _x000D_
  //   CurrentLevel level of drop-down, which we change_x000D_
  //   HeadLevel  main level_x000D_
  //   r    current cell, which was changed by user_x000D_
  //   X           number of levels could be checked on the right_x000D_
  //_x000D_
  //  ls   Data sheet (LogSheet)_x000D_
  //_x000D_
  //    ======================================================================================_x000D_
_x000D_
// Checks_x000D_
var ts = event.source.getActiveSheet();_x000D_
var sname = ts.getName(); _x000D_
if (sname !== TargetSheet) { return -1;  } // not main sheet_x000D_
// Test if range fits_x000D_
var br = event.range;_x000D_
var scol = br.getColumn(); // the column number in which the change is made_x000D_
var srow = br.getRow() // line number in which the change is made_x000D_
var ColNum = br.getWidth();_x000D_
_x000D_
if ((scol + ColNum - 1) < lcol) { return -2; }  // columns... _x000D_
if (srow < lrow) { return -3; } // rows_x000D_
// Test range is in levels_x000D_
var columnsLevels = getColumnsOffset_(offsets, lcol); // Columns for all levels _x000D_
var CurrentLevel = getCurrentLevel_(ColNum, br, scol, columnsLevels);_x000D_
if(CurrentLevel === 1) { return -4; } // out of data validations_x000D_
if(CurrentLevel > NumOfLevels) { return -5; } // last level _x000D_
_x000D_
_x000D_
/*_x000D_
 ts - sheet with validation, sname = name of sheet_x000D_
 _x000D_
      NumOfLevels = 4                     _x000D_
      offsets = [1,1,1,2] - last offset is 2 because need to skip 1 column_x000D_
      columnsLevels = [4,5,6,8] - Columns of validation_x000D_
      _x000D_
          Columns 7 is skipped_x000D_
          |_x000D_
    1 2  3   4    5    6    7    8    9    _x000D_
 |----+----+----+----+----+----+----+----+----+_x000D_
1 |  |    |    |    |    |    |  x |    |    |_x000D_
 |----+----+----+----+----+----+----+----+----+_x000D_
2 |  |    |    |  v |  V |  ? |  x |  ? |    | lrow = 2 - number of row where validation starts_x000D_
 |----+----+----+----+----+----+----+----+----+_x000D_
3 |  |    |    |    |    |    |  x |    |    |_x000D_
 |----+----+----+----+----+----+----+----+----+_x000D_
4 |  |    |    |    |    |    |  x |    |    |_x000D_
 |----+----+----+----+----+----+----+----+----+_x000D_
       |  |   |     |           |_x000D_
       |  |   |     | Currentlevel = 3 - the number of level to change_x000D_
       |  |   |                 |_x000D_
       |  |   | br - cell, user changes: scol - column, srow - row,_x000D_
       |  |          ColNum = 1 - width   _x000D_
       |__|________   _.....____|_x000D_
       |         v_x000D_
       |  Drop-down lists     _x000D_
       |_x000D_
       | lcol = 4 - number of column where validation starts_x000D_
*/_x000D_
// Constants_x000D_
var ReplaceCommas = getDecimalMarkIsCommaLocals(); // // ReplaceCommas = true if locale uses commas to separate decimals_x000D_
var ls = SpreadsheetApp.getActive().getSheetByName(LogSheet); // Data sheet                    _x000D_
var RowNum = br.getHeight();_x000D_
/*  Adjust the range 'br' _x000D_
    ???       !_x000D_
 xxx       x_x000D_
 xxx       x _x000D_
 xxx  =>   x_x000D_
 xxx       x_x000D_
 xxx       x_x000D_
*/ _x000D_
br = ts.getRange(br.getRow(), columnsLevels[CurrentLevel - 2], RowNum); _x000D_
// Levels_x000D_
var HeadLevel = CurrentLevel - 1; // main level_x000D_
var X = NumOfLevels - CurrentLevel + 1; // number of levels left       _x000D_
// determine columns on the sheet "Data"_x000D_
var KudaCol = NumOfLevels + 2;_x000D_
var KudaNado = ls.getRange(1, KudaCol);  // 1 place for a formula_x000D_
var lastRow = ls.getLastRow();_x000D_
var ChtoNado = ls.getRange(1, KudaCol, lastRow, KudaCol); // the range with list, returned by a formula_x000D_
_x000D_
// ============================================================================= > loop >_x000D_
var CurrLevelBase = CurrentLevel; // remember the first current level_x000D_
_x000D_
_x000D_
_x000D_
for (var j = 1; j <= RowNum; j++) // [01] loop rows start_x000D_
{    _x000D_
  // refresh first val  _x000D_
  var currentRow = br.getCell(j, 1).getRow();      _x000D_
  loopColumns_(HeadLevel, X, currentRow, NumOfLevels, CurrLevelBase, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts);_x000D_
} // [01] loop rows end_x000D_
_x000D_
       _x000D_
_x000D_
}_x000D_
_x000D_
_x000D_
function getColumnsOffset_(offsets, lefColumn)_x000D_
{_x000D_
// Columns for all levels_x000D_
var columnsLevels = [];_x000D_
var totalOffset = 0; _x000D_
for (var i = 0, l = offsets.length; i < l; i++)_x000D_
{ _x000D_
 totalOffset += offsets[i];_x000D_
 columnsLevels.push(totalOffset + lefColumn - 1);_x000D_
} _x000D_
_x000D_
return columnsLevels;_x000D_
_x000D_
}_x000D_
_x000D_
function test_getCurrentLevel()_x000D_
{_x000D_
  var br = SpreadsheetApp.getActive().getActiveSheet().getRange('A5:C5');_x000D_
  var scol = 1;_x000D_
  _x000D_
  _x000D_
  /*_x000D_
        |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |_x000D_
  range |xxxxx| _x000D_
   dv range |xxxxxxxxxxxxxxxxx|_x000D_
 levels    1     2     3_x000D_
  level          2_x000D_
  _x000D_
  */_x000D_
  Logger.log(getCurrentLevel_(1, br, scol, [1,2,3])); // 2_x000D_
  _x000D_
  /*_x000D_
        |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |_x000D_
  range |xxxxxxxxxxx| _x000D_
   dv range |xxxxx|     |xxxxx|     |xxxxx|_x000D_
 levels    1           2           3_x000D_
  level                2_x000D_
  _x000D_
  */  _x000D_
  Logger.log(getCurrentLevel_(2, br, scol, [1,3,5])); // 2_x000D_
  _x000D_
  /*_x000D_
        |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |_x000D_
  range |xxxxxxxxxxxxxxxxx| _x000D_
   dv range |xxxxx|                 |xxxxxxxxxxx| _x000D_
 levels    1                       2     3_x000D_
  level                            2_x000D_
  _x000D_
  */    _x000D_
  Logger.log(getCurrentLevel_(3, br, scol, [1,5,6])); // 2_x000D_
  _x000D_
  _x000D_
  /*_x000D_
        |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |_x000D_
  range |xxxxxxxxxxxxxxxxx| _x000D_
   dv range |xxxxxxxxxxx|                             |xxxxx| _x000D_
 levels    1     2                                   3_x000D_
  level                                              3_x000D_
  _x000D_
  */    _x000D_
  Logger.log(getCurrentLevel_(3, br, scol, [1,2,8])); // 3_x000D_
  _x000D_
  _x000D_
  /*_x000D_
        |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |_x000D_
  range |xxxxxxxxxxxxxxxxx| _x000D_
   dv range |xxxxxxxxxxxxxxxxx|_x000D_
 levels    1     2     3_x000D_
  level                      4 (error)_x000D_
  _x000D_
  */    _x000D_
  Logger.log(getCurrentLevel_(3, br, scol, [1,2,3]));_x000D_
  _x000D_
  _x000D_
  /*_x000D_
        |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |_x000D_
  range |xxxxxxxxxxxxxxxxx| _x000D_
   dv range                         |xxxxxxxxxxxxxxxxx|_x000D_
 levels    _x000D_
  level    1 (error)                      _x000D_
  _x000D_
  */    _x000D_
  Logger.log(getCurrentLevel_(3, br, scol, [5,6,7])); // 1 _x000D_
  _x000D_
}_x000D_
_x000D_
_x000D_
function getCurrentLevel_(ColNum, br, scol, columnsLevels)_x000D_
{_x000D_
var colPlus = 2; // const_x000D_
if (ColNum === 1) { return columnsLevels.indexOf(scol) + colPlus; }_x000D_
var CurrentLevel = -1;_x000D_
var level = 0;_x000D_
var column = 0;_x000D_
for (var i = 0; i < ColNum; i++ )_x000D_
{_x000D_
 column = br.offset(0, i).getColumn();_x000D_
 level = columnsLevels.indexOf(column) + colPlus;_x000D_
 if (level > CurrentLevel) { CurrentLevel = level; }_x000D_
}_x000D_
return CurrentLevel;_x000D_
}_x000D_
_x000D_
_x000D_
_x000D_
function loopColumns_(HeadLevel, X, currentRow, NumOfLevels, CurrentLevel, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts)_x000D_
{_x000D_
  for (var k = 1; k <= X; k++)_x000D_
  {   _x000D_
HeadLevel = HeadLevel + k - 1; _x000D_
CurrentLevel = CurrLevelBase + k - 1;_x000D_
var r = ts.getRange(currentRow, columnsLevels[CurrentLevel - 2]);_x000D_
var SearchText = r.getValue(); // searched text _x000D_
X = loopColumn_(X, SearchText, HeadLevel, HeadLevel, currentRow, NumOfLevels, CurrentLevel, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts);_x000D_
  } _x000D_
}_x000D_
_x000D_
_x000D_
function loopColumn_(X, SearchText, HeadLevel, HeadLevel, currentRow, NumOfLevels, CurrentLevel, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts)_x000D_
{_x000D_
_x000D_
_x000D_
  // if nothing is chosen!_x000D_
  if (SearchText === '') // condition value =''_x000D_
  {_x000D_
// kill extra data validation if there were _x000D_
// columns on the right_x000D_
if (CurrentLevel <= NumOfLevels) _x000D_
{_x000D_
  for (var f = 0; f < X; f++) _x000D_
  {_x000D_
    var cell = ts.getRange(currentRow, columnsLevels[CurrentLevel + f - 1]);    _x000D_
    // clean & get rid of validation_x000D_
    cell.clear({contentsOnly: true});              _x000D_
    cell.clear({validationsOnly: true});_x000D_
    // exit columns loop  _x000D_
  }_x000D_
}_x000D_
return 0; // end loop this row _x000D_
  }_x000D_
  _x000D_
  _x000D_
  // formula for values_x000D_
  var formula = getDVListFormula_(CurrentLevel, currentRow, columnsLevels, lastRow, ReplaceCommas, FormulaSplitter, ts);  _x000D_
  KudaNado.setFormula(formula);_x000D_
_x000D_
  _x000D_
  // get response_x000D_
  var Response = getResponse_(ChtoNado, lastRow, ReplaceCommas);_x000D_
  var Variants = Response.length;_x000D_
_x000D_
_x000D_
  // build data validation rule_x000D_
  if (Variants === 0.0) // empty is found_x000D_
  {_x000D_
return;_x000D_
  }  _x000D_
  if(Variants >= 1.0) // if some variants were found_x000D_
  {_x000D_
_x000D_
var cell = ts.getRange(currentRow, columnsLevels[CurrentLevel - 1]);_x000D_
var rule = SpreadsheetApp_x000D_
.newDataValidation()_x000D_
.requireValueInList(Response, true)_x000D_
.setAllowInvalid(false)_x000D_
.build();_x000D_
// set validation rule_x000D_
cell.setDataValidation(rule);_x000D_
  }    _x000D_
  if (Variants === 1.0) // // set the only value_x000D_
  {      _x000D_
cell.setValue(Response[0]);_x000D_
SearchText = null;_x000D_
Response = null;_x000D_
return X; // continue doing DV_x000D_
  } // the only value_x000D_
  _x000D_
  return 0; // end DV in this row_x000D_
  _x000D_
}_x000D_
_x000D_
_x000D_
function getDVListFormula_(CurrentLevel, currentRow, columnsLevels, lastRow, ReplaceCommas, FormulaSplitter, ts)_x000D_
{_x000D_
  _x000D_
  var checkVals = [];_x000D_
  var Offs = CurrentLevel - 2;_x000D_
  var values = [];_x000D_
  // get values and display values for a formula_x000D_
  for (var s = 0; s <= Offs; s++)_x000D_
  {_x000D_
var checkR = ts.getRange(currentRow, columnsLevels[s]);_x000D_
values.push(checkR.getValue());_x000D_
  }     _x000D_
  _x000D_
  var LookCol = colName(CurrentLevel-1); // gets column name "A,B,C..."_x000D_
  var formula = '=unique(filter(' + LookCol + '2:' + LookCol + lastRow; // =unique(filter(A2:A84_x000D_
_x000D_
  var mathOpPlusVal = ''; _x000D_
  var value = '';_x000D_
_x000D_
  // loop levels for multiple conditions  _x000D_
  for (var i = 0; i < CurrentLevel - 1; i++) {            _x000D_
formula += FormulaSplitter; // =unique(filter(A2:A84;_x000D_
LookCol = colName(i);_x000D_
  _x000D_
value = values[i];_x000D_
_x000D_
mathOpPlusVal = getValueAndMathOpForFunction_(value, FormulaSplitter, ReplaceCommas); // =unique(filter(A2:A84;B2:B84="Text"_x000D_
_x000D_
if ( Array.isArray(mathOpPlusVal) )_x000D_
{_x000D_
  formula += mathOpPlusVal[0];_x000D_
  formula += LookCol + '2:' + LookCol + lastRow; // =unique(filter(A2:A84;ROUND(B2:B84_x000D_
  formula += mathOpPlusVal[1];_x000D_
}_x000D_
else_x000D_
{_x000D_
  formula += LookCol + '2:' + LookCol + lastRow; // =unique(filter(A2:A84;B2:B84_x000D_
  formula += mathOpPlusVal;_x000D_
}_x000D_
_x000D_
_x000D_
  }  _x000D_
  _x000D_
  formula += "))"; //=unique(filter(A2:A84;B2:B84="Text"))_x000D_
_x000D_
  return formula;_x000D_
}_x000D_
_x000D_
_x000D_
function getValueAndMathOpForFunction_(value, FormulaSplitter, ReplaceCommas)_x000D_
{_x000D_
  var result = '';_x000D_
  var splinter = ''; _x000D_
_x000D_
  var type = typeof value;_x000D_
  _x000D_
 _x000D_
  // strings_x000D_
  if (type === 'string') return '="' + value + '"';_x000D_
  // date_x000D_
  if(value instanceof Date)_x000D_
  {_x000D_
return ['ROUND(', FormulaSplitter +'5)=ROUND(DATE(' + value.getFullYear() + FormulaSplitter + (value.getMonth() + 1) + FormulaSplitter + value.getDate() + ')' + '+' _x000D_
      + 'TIME(' + value.getHours() + FormulaSplitter + value.getMinutes() + FormulaSplitter + value.getSeconds() + ')' + FormulaSplitter + '5)'];   _x000D_
  }  _x000D_
  // numbers_x000D_
  if (type === 'number')_x000D_
  {_x000D_
if (ReplaceCommas)_x000D_
{_x000D_
 return '+0=' + value.toString().replace('.', ',');  _x000D_
}_x000D_
else_x000D_
{_x000D_
 return '+0=' + value;_x000D_
}_x000D_
  }_x000D_
  // booleans_x000D_
  if (type === 'boolean')_x000D_
  {_x000D_
  return '=' + value;_x000D_
  }  _x000D_
  // other_x000D_
  return '=' + value;_x000D_
_x000D_
}_x000D_
_x000D_
_x000D_
function getResponse_(allRange, l, ReplaceCommas)_x000D_
{_x000D_
  var data = allRange.getValues();_x000D_
  var data_ = allRange.getDisplayValues();_x000D_
  _x000D_
  var response = [];_x000D_
  var val = '';_x000D_
  for (var i = 0; i < l; i++)_x000D_
  {_x000D_
val = data[i][0];_x000D_
if (val !== '') _x000D_
{_x000D_
  var type = typeof val;_x000D_
  if (type === 'boolean' || val instanceof Date) val = String(data_[i][0]);_x000D_
  if (type === 'number' && ReplaceCommas) val = val.toString().replace('.', ',')_x000D_
  response.push(val);  _x000D_
}_x000D_
  }_x000D_
  _x000D_
  return response;  _x000D_
}_x000D_
_x000D_
_x000D_
_x000D_
_x000D_
function colName(n) {_x000D_
var ordA = 'a'.charCodeAt(0);_x000D_
var ordZ = 'z'.charCodeAt(0);_x000D_
_x000D_
var len = ordZ - ordA + 1;_x000D_
_x000D_
var s = "";_x000D_
while(n >= 0) {_x000D_
    s = String.fromCharCode(n % len + ordA) + s;_x000D_
    n = Math.floor(n / len) - 1;_x000D_
}_x000D_
return s; _x000D_
}_x000D_
_x000D_
_x000D_
function getDecimalMarkIsCommaLocals() {_x000D_
_x000D_
_x000D_
// list of Locals Decimal mark = comma_x000D_
var LANGUAGE_BY_LOCALE = {_x000D_
af_NA: "Afrikaans (Namibia)",_x000D_
af_ZA: "Afrikaans (South Africa)",_x000D_
af: "Afrikaans",_x000D_
sq_AL: "Albanian (Albania)",_x000D_
sq: "Albanian",_x000D_
ar_DZ: "Arabic (Algeria)",_x000D_
ar_BH: "Arabic (Bahrain)",_x000D_
ar_EG: "Arabic (Egypt)",_x000D_
ar_IQ: "Arabic (Iraq)",_x000D_
ar_JO: "Arabic (Jordan)",_x000D_
ar_KW: "Arabic (Kuwait)",_x000D_
ar_LB: "Arabic (Lebanon)",_x000D_
ar_LY: "Arabic (Libya)",_x000D_
ar_MA: "Arabic (Morocco)",_x000D_
ar_OM: "Arabic (Oman)",_x000D_
ar_QA: "Arabic (Qatar)",_x000D_
ar_SA: "Arabic (Saudi Arabia)",_x000D_
ar_SD: "Arabic (Sudan)",_x000D_
ar_SY: "Arabic (Syria)",_x000D_
ar_TN: "Arabic (Tunisia)",_x000D_
ar_AE: "Arabic (United Arab Emirates)",_x000D_
ar_YE: "Arabic (Yemen)",_x000D_
ar: "Arabic",_x000D_
hy_AM: "Armenian (Armenia)",_x000D_
hy: "Armenian",_x000D_
eu_ES: "Basque (Spain)",_x000D_
eu: "Basque",_x000D_
be_BY: "Belarusian (Belarus)",_x000D_
be: "Belarusian",_x000D_
bg_BG: "Bulgarian (Bulgaria)",_x000D_
bg: "Bulgarian",_x000D_
ca_ES: "Catalan (Spain)",_x000D_
ca: "Catalan",_x000D_
tzm_Latn: "Central Morocco Tamazight (Latin)",_x000D_
tzm_Latn_MA: "Central Morocco Tamazight (Latin, Morocco)",_x000D_
tzm: "Central Morocco Tamazight",_x000D_
da_DK: "Danish (Denmark)",_x000D_
da: "Danish",_x000D_
nl_BE: "Dutch (Belgium)",_x000D_
nl_NL: "Dutch (Netherlands)",_x000D_
nl: "Dutch",_x000D_
et_EE: "Estonian (Estonia)",_x000D_
et: "Estonian",_x000D_
fi_FI: "Finnish (Finland)",_x000D_
fi: "Finnish",_x000D_
fr_BE: "French (Belgium)",_x000D_
fr_BJ: "French (Benin)",_x000D_
fr_BF: "French (Burkina Faso)",_x000D_
fr_BI: "French (Burundi)",_x000D_
fr_CM: "French (Cameroon)",_x000D_
fr_CA: "French (Canada)",_x000D_
fr_CF: "French (Central African Republic)",_x000D_
fr_TD: "French (Chad)",_x000D_
fr_KM: "French (Comoros)",_x000D_
fr_CG: "French (Congo - Brazzaville)",_x000D_
fr_CD: "French (Congo - Kinshasa)",_x000D_
fr_CI: "French (Côte d’Ivoire)",_x000D_
fr_DJ: "French (Djibouti)",_x000D_
fr_GQ: "French (Equatorial Guinea)",_x000D_
fr_FR: "French (France)",_x000D_
fr_GA: "French (Gabon)",_x000D_
fr_GP: "French (Guadeloupe)",_x000D_
fr_GN: "French (Guinea)",_x000D_
fr_LU: "French (Luxembourg)",_x000D_
fr_MG: "French (Madagascar)",_x000D_
fr_ML: "French (Mali)",_x000D_
fr_MQ: "French (Martinique)",_x000D_
fr_MC: "French (Monaco)",_x000D_
fr_NE: "French (Niger)",_x000D_
fr_RW: "French (Rwanda)",_x000D_
fr_RE: "French (Réunion)",_x000D_
fr_BL: "French (Saint Barthélemy)",_x000D_
fr_MF: "French (Saint Martin)",_x000D_
fr_SN: "French (Senegal)",_x000D_
fr_CH: "French (Switzerland)",_x000D_
fr_TG: "French (Togo)",_x000D_
fr: "French",_x000D_
gl_ES: "Galician (Spain)",_x000D_
gl: "Galician",_x000D_
ka_GE: "Georgian (Georgia)",_x000D_
ka: "Georgian",_x000D_
de_AT: "German (Austria)",_x000D_
de_BE: "German (Belgium)",_x000D_
de_DE: "German (Germany)",_x000D_
de_LI: "German (Liechtenstein)",_x000D_
de_LU: "German (Luxembourg)",_x000D_
de_CH: "German (Switzerland)",_x000D_
de: "German",_x000D_
el_CY: "Greek (Cyprus)",_x000D_
el_GR: "Greek (Greece)",_x000D_
el: "Greek",_x000D_
hu_HU: "Hungarian (Hungary)",_x000D_
hu: "Hungarian",_x000D_
is_IS: "Icelandic (Iceland)",_x000D_
is: "Icelandic",_x000D_
id_ID: "Indonesian (Indonesia)",_x000D_
id: "Indonesian",_x000D_
it_IT: "Italian (Italy)",_x000D_
it_CH: "Italian (Switzerland)",_x000D_
it: "Italian",_x000D_
kab_DZ: "Kabyle (Algeria)",_x000D_
kab: "Kabyle",_x000D_
kl_GL: "Kalaallisut (Greenland)",_x000D_
kl: "Kalaallisut",_x000D_
lv_LV: "Latvian (Latvia)",_x000D_
lv: "Latvian",_x000D_
lt_LT: "Lithuanian (Lithuania)",_x000D_
lt: "Lithuanian",_x000D_
mk_MK: "Macedonian (Macedonia)",_x000D_
mk: "Macedonian",_x000D_
naq_NA: "Nama (Namibia)",_x000D_
naq: "Nama",_x000D_
pl_PL: "Polish (Poland)",_x000D_
pl: "Polish",_x000D_
pt_BR: "Portuguese (Brazil)",_x000D_
pt_GW: "Portuguese (Guinea-Bissau)",_x000D_
pt_MZ: "Portuguese (Mozambique)",_x000D_
pt_PT: "Portuguese (Portugal)",_x000D_
pt: "Portuguese",_x000D_
ro_MD: "Romanian (Moldova)",_x000D_
ro_RO: "Romanian (Romania)",_x000D_
ro: "Romanian",_x000D_
ru_MD: "Russian (Moldova)",_x000D_
ru_RU: "Russian (Russia)",_x000D_
ru_UA: "Russian (Ukraine)",_x000D_
ru: "Russian",_x000D_
seh_MZ: "Sena (Mozambique)",_x000D_
seh: "Sena",_x000D_
sk_SK: "Slovak (Slovakia)",_x000D_
sk: "Slovak",_x000D_
sl_SI: "Slovenian (Slovenia)",_x000D_
sl: "Slovenian",_x000D_
es_AR: "Spanish (Argentina)",_x000D_
es_BO: "Spanish (Bolivia)",_x000D_
es_CL: "Spanish (Chile)",_x000D_
es_CO: "Spanish (Colombia)",_x000D_
es_CR: "Spanish (Costa Rica)",_x000D_
es_DO: "Spanish (Dominican Republic)",_x000D_
es_EC: "Spanish (Ecuador)",_x000D_
es_SV: "Spanish (El Salvador)",_x000D_
es_GQ: "Spanish (Equatorial Guinea)",_x000D_
es_GT: "Spanish (Guatemala)",_x000D_
es_HN: "Spanish (Honduras)",_x000D_
es_419: "Spanish (Latin America)",_x000D_
es_MX: "Spanish (Mexico)",_x000D_
es_NI: "Spanish (Nicaragua)",_x000D_
es_PA: "Spanish (Panama)",_x000D_
es_PY: "Spanish (Paraguay)",_x000D_
es_PE: "Spanish (Peru)",_x000D_
es_PR: "Spanish (Puerto Rico)",_x000D_
es_ES: "Spanish (Spain)",_x000D_
es_US: "Spanish (United States)",_x000D_
es_UY: "Spanish (Uruguay)",_x000D_
es_VE: "Spanish (Venezuela)",_x000D_
es: "Spanish",_x000D_
sv_FI: "Swedish (Finland)",_x000D_
sv_SE: "Swedish (Sweden)",_x000D_
sv: "Swedish",_x000D_
tr_TR: "Turkish (Turkey)",_x000D_
tr: "Turkish",_x000D_
uk_UA: "Ukrainian (Ukraine)",_x000D_
uk: "Ukrainian",_x000D_
vi_VN: "Vietnamese (Vietnam)",_x000D_
vi: "Vietnamese"_x000D_
}_x000D_
_x000D_
_x000D_
var SS = SpreadsheetApp.getActiveSpreadsheet();_x000D_
var LocalS = SS.getSpreadsheetLocale();_x000D_
_x000D_
_x000D_
if (LANGUAGE_BY_LOCALE[LocalS] == undefined) {_x000D_
  return false;_x000D_
  _x000D_
}_x000D_
  //Logger.log(true);_x000D_
  return true;_x000D_
}_x000D_
_x000D_
/*_x000D_
function ReplaceDotsToCommas(dataIn) {_x000D_
  var dataOut = dataIn.map(function(num) {_x000D_
  if (isNaN(num)) {_x000D_
    return num;_x000D_
  }    _x000D_
  num = num.toString();_x000D_
  return num.replace(".", ",");_x000D_
  });_x000D_
  return dataOut;_x000D_
}_x000D_
*/
_x000D_
_x000D_
_x000D_

Here's set of variables that are to be changed, you'll find them in script:

  var TargetSheet = 'Main'; // name of sheet with data validation
  var LogSheet = 'Data2'; // name of sheet with data
  var NumOfLevels = 7; // number of levels of data validation
  var lcol = 9; // number of column where validation starts; A = 1, B = 2, etc.
  var lrow = 2; // number of row where validation starts
  var offsets = [1,1,1,1,1,1,1]; // offsets for levels

I suggest everyone, who knows scripts well, send your edits to this code. I guess, there's simpler way to find validation list and make script run faster.


Here you have another solution based on the one provided by @tarheel

function onEdit() {
    var sheetWithNestedSelectsName = "Sitemap";
    var columnWithNestedSelectsRoot = 1;
    var sheetWithOptionPossibleValuesSuffix = "TabSections";

    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = SpreadsheetApp.getActiveSheet();

    // If we're not in the sheet with nested selects, exit!
    if ( activeSheet.getName() != sheetWithNestedSelectsName ) {
        return;
    }

    var activeCell = SpreadsheetApp.getActiveRange();

    // If we're not in the root column or a content row, exit!
    if ( activeCell.getColumn() != columnWithNestedSelectsRoot || activeCell.getRow() < 2 ) {
        return;
    }

    var sheetWithActiveOptionPossibleValues = activeSpreadsheet.getSheetByName( activeCell.getValue() + sheetWithOptionPossibleValuesSuffix );

    // Get all possible values
    var activeOptionPossibleValues = sheetWithActiveOptionPossibleValues.getSheetValues( 1, 1, -1, 1 );

    var possibleValuesValidation = SpreadsheetApp.newDataValidation();
    possibleValuesValidation.setAllowInvalid( false );
    possibleValuesValidation.requireValueInList( activeOptionPossibleValues, true );

    activeSheet.getRange( activeCell.getRow(), activeCell.getColumn() + 1 ).setDataValidation( possibleValuesValidation.build() );
}

It has some benefits over the other approach:

  • You don't need to edit the script every time you add a "root option". You only have to create a new sheet with the nested options of this root option.
  • I've refactored the script providing more semantic names for the variables and so on. Furthermore, I've extracted some parameters to variables in order to make it easier to adapt to your specific case. You only have to set the first 3 values.
  • There's no limit of nested option values (I've used the getSheetValues method with the -1 value).

So, how to use it:

  1. Create the sheet where you'll have the nested selectors
  2. Go to the "Tools" > "Script Editor…" and select the "Blank project" option
  3. Paste the code attached to this answer
  4. Modify the first 3 variables of the script setting up your values and save it
  5. Create one sheet within this same document for each possible value of the "root selector". They must be named as the value + the specified suffix.

Enjoy!


Continuing the evolution of this solution I've upped the ante by adding support for multiple root selections and deeper nested selections. This is a further development of JavierCane's solution (which in turn built on tarheel's).

_x000D_
_x000D_
/**_x000D_
 * "on edit" event handler_x000D_
 *_x000D_
 * Based on JavierCane's answer in _x000D_
 * _x000D_
 *   http://stackoverflow.com/questions/21744547/how-do-you-do-dynamic-dependent-drop-downs-in-google-sheets_x000D_
 *_x000D_
 * Each set of options has it own sheet named after the option. The _x000D_
 * values in this sheet are used to populate the drop-down._x000D_
 *_x000D_
 * The top row is assumed to be a header._x000D_
 *_x000D_
 * The sub-category column is assumed to be the next column to the right._x000D_
 *_x000D_
 * If there are no sub-categories the next column along is cleared in _x000D_
 * case the previous selection did have options._x000D_
 */_x000D_
_x000D_
function onEdit() {_x000D_
_x000D_
  var NESTED_SELECTS_SHEET_NAME = "Sitemap"_x000D_
  var NESTED_SELECTS_ROOT_COLUMN = 1_x000D_
  var SUB_CATEGORY_COLUMN = NESTED_SELECTS_ROOT_COLUMN + 1_x000D_
  var NUMBER_OF_ROOT_OPTION_CELLS = 3_x000D_
  var OPTION_POSSIBLE_VALUES_SHEET_SUFFIX = ""_x000D_
  _x000D_
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()_x000D_
  var activeSheet = SpreadsheetApp.getActiveSheet()_x000D_
  _x000D_
  if (activeSheet.getName() !== NESTED_SELECTS_SHEET_NAME) {_x000D_
  _x000D_
    // Not in the sheet with nested selects, exit!_x000D_
    return_x000D_
  }_x000D_
  _x000D_
  var activeCell = SpreadsheetApp.getActiveRange()_x000D_
  _x000D_
  // Top row is the header_x000D_
  if (activeCell.getColumn() > SUB_CATEGORY_COLUMN || _x000D_
      activeCell.getRow() === 1 ||_x000D_
      activeCell.getRow() > NUMBER_OF_ROOT_OPTION_CELLS + 1) {_x000D_
_x000D_
    // Out of selection range, exit!_x000D_
    return_x000D_
  }_x000D_
  _x000D_
  var sheetWithActiveOptionPossibleValues = activeSpreadsheet_x000D_
    .getSheetByName(activeCell.getValue() + OPTION_POSSIBLE_VALUES_SHEET_SUFFIX)_x000D_
  _x000D_
  if (sheetWithActiveOptionPossibleValues === null) {_x000D_
  _x000D_
    // There are no further options for this value, so clear out any old_x000D_
    // values_x000D_
    activeSheet_x000D_
      .getRange(activeCell.getRow(), activeCell.getColumn() + 1)_x000D_
      .clearDataValidations()_x000D_
      .clearContent()_x000D_
      _x000D_
    return_x000D_
  }_x000D_
  _x000D_
  // Get all possible values_x000D_
  var activeOptionPossibleValues = sheetWithActiveOptionPossibleValues_x000D_
    .getSheetValues(1, 1, -1, 1)_x000D_
  _x000D_
  var possibleValuesValidation = SpreadsheetApp.newDataValidation()_x000D_
  possibleValuesValidation.setAllowInvalid(false)_x000D_
  possibleValuesValidation.requireValueInList(activeOptionPossibleValues, true)_x000D_
  _x000D_
  activeSheet_x000D_
    .getRange(activeCell.getRow(), activeCell.getColumn() + 1)_x000D_
    .setDataValidation(possibleValuesValidation.build())_x000D_
    _x000D_
} // onEdit()
_x000D_
_x000D_
_x000D_

As Javier says:

  • Create the sheet where you'll have the nested selectors
  • Go to the "Tools" > "Script Editor…" and select the "Blank project" option
  • Paste the code attached to this answer
  • Modify the constants at the top of the script setting up your values and save it
  • Create one sheet within this same document for each possible value of the "root selector". They must be named as the value + the specified suffix.

And if you wanted to see it in action I've created a demo sheet and you can see the code if you take a copy.


Edit: The answer below may be satisfactory, but it has some drawbacks:

  1. There is a noticeable pause for the running of the script. I'm on a 160 ms latency, and it's enough to be annoying.

  2. It works by building a new range each time you edit a given row. This gives an 'invalid contents' to previous entries some of the time

I hope others can clean this up somewhat.

Here's another way to do it, that saves you a ton of range naming:

Three sheets in the worksheet: call them Main, List, and DRange (for dynamic range.) On the Main sheet, column 1 contains a timestamp. This time stamp is modified onEdit.

On List your categories and subcategories are arranged as a simple list. I'm using this for plant inventory at my tree farm, so my list looks like this:

Group   | Genus | Bot_Name
Conifer | Abies | Abies balsamea
Conifer | Abies | Abies concolor
Conifer | Abies | Abies lasiocarpa var bifolia
Conifer | Pinus | Pinus ponderosa
Conifer | Pinus | Pinus sylvestris
Conifer | Pinus | Pinus banksiana
Conifer | Pinus | Pinus cembra
Conifer | Picea | Picea pungens
Conifer | Picea | Picea glauca
Deciduous | Acer | Acer ginnala
Deciduous | Acer | Acer negundo
Deciduous | Salix | Salix discolor
Deciduous | Salix | Salix fragilis
...

Where | indicates separation into columns.
For convenience I also used the headers as names for named ranges.

DRrange A1 has the formula

=Max(Main!A2:A1000)

This returns the most recent timestamp.

A2 to A4 have variations on:

=vlookup($A$1,Inventory!$A$1:$E$1000,2,False) 

with the 2 being incremented for each cell to the right.

On running A2 to A4 will have the currently selected Group, Genus and Species.

Below each of these, is a filter command something like this:

=unique(filter(Bot_Name,REGEXMATCH(Bot_Name,C1)))

These filters will populate a block below with matching entries to the contents of the top cell.

The filters can be modified to suit your needs, and to the format of your list.

Back to Main: Data validation in Main is done using ranges from DRange.

The script I use:

function onEdit(event) {

  //SETTINGS
  var dynamicSheet='DRange'; //sheet where the dynamic range lives
  var tsheet = 'Main'; //the sheet you are monitoring for edits
  var lcol = 2; //left-most column number you are monitoring; A=1, B=2 etc
  var rcol = 5; //right-most column number you are monitoring
  var tcol = 1; //column number in which you wish to populate the timestamp
  //

  var s = event.source.getActiveSheet();
  var sname = s.getName();
  if (sname == tsheet) {
    var r = event.source.getActiveRange();
    var scol = r.getColumn();  //scol is the column number of the edited cell
    if (scol >= lcol && scol <= rcol) {
      s.getRange(r.getRow(), tcol).setValue(new Date());
      for(var looper=scol+1; looper<=rcol; looper++) {
         s.getRange(r.getRow(),looper).setValue(""); //After edit clear the entries to the right
      }
    }
  }
}

Original Youtube presentation that gave me most of the onEdit timestamp component: https://www.youtube.com/watch?v=RDK8rjdE85Y


Examples related to validation

Rails 2.3.4 Persisting Model on Validation Failure Input type number "only numeric value" validation How can I manually set an Angular form field as invalid? Laravel Password & Password_Confirmation Validation Reactjs - Form input validation Get all validation errors from Angular 2 FormGroup Min / Max Validator in Angular 2 Final How to validate white spaces/empty spaces? [Angular 2] How to Validate on Max File Size in Laravel? WebForms UnobtrusiveValidationMode requires a ScriptResourceMapping for jquery

Examples related to google-apps-script

Adding Buttons To Google Sheets and Set value to Cells on clicking How to automatically import data from uploaded CSV or XLS file into Google Sheets Get today date in google appScript How to define global variable in Google Apps Script How to make google spreadsheet refresh itself every 1 minute? How do you do dynamic / dependent drop downs in Google Sheets? Google Forms file upload complete example Google Script to see if text contains a value Is it possible to 'prefill' a google form using data from a google spreadsheet? TypeError: Cannot read property "0" from undefined

Examples related to google-sheets

Import data into Google Colaboratory Is there a Google Sheets formula to put the name of the sheet into a cell? How to label scatterplot points by name? Adding Buttons To Google Sheets and Set value to Cells on clicking How to automatically import data from uploaded CSV or XLS file into Google Sheets How to negate 'isblank' function How can I copy a conditional formatting from one document to another? Multiple IF statements between number ranges Apply formula to the entire column How to define global variable in Google Apps Script