How do you do dynamic dependent drop downs in Google Sheets

Question

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

User · Answer

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_
 * "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_

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.

User · Answer

Edit   The answer below may be satisfactory  but it has some drawbacks    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  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  gt   lcol  amp  amp  scol  lt   rcol          s getRange r getRow    tcol  setValue new Date           for var looper scol 1  looper lt  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

User · Answer

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    lt  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    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 first 3 variables 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    Enjoy

User · Answer

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   Article Video Last version of the script on GitHub     This solution is not perfect  but it gives some benefits    Let you make multiple dropdown lists Gives more control 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     Installation    Prepare Data Make the first list as usual  Data  gt  Validation Add Script  set some variables 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      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       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   lt  lcol    return -2        columns     x000D  if  srow  lt  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  gt  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    gt    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                                                                                    gt  loop  gt  x000D  var CurrLevelBase   CurrentLevel     remember the first current level x000D   x000D   x000D   x000D  for  var j   1  j  lt   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  lt  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  lt  ColNum  i     x000D    x000D   column   br offset 0  i  getColumn    x000D   level   columnsLevels indexOf column    colPlus  x000D   if  level  gt  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  lt   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  lt   NumOfLevels   x000D    x000D    for  var f   0  f  lt  X  f     x000D      x000D      var cell   ts getRange currentRow  columnsLevels CurrentLevel   f - 1        x000D         clean  amp  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  gt   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  lt   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  lt  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  lt  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   amp  amp  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  gt   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

User · Answer

You can start with a google sheet set up with a main page and drop down source page like shown below   You can set up the first column drop down through the normal Data   Validations menu prompts   Main Page    Drop Down Source Page    After that  you need to set up a script with the name onEdit   If you don t use that name  the getActiveRange   will do nothing but return cell A1   And use the code provided here   function onEdit       var ss   SpreadsheetApp getActiveSpreadsheet      var sheet   SpreadsheetApp getActiveSheet      var myRange   SpreadsheetApp getActiveRange      var dvSheet   SpreadsheetApp getActiveSpreadsheet   getSheetByName  Categories      var option   new Array      var startCol   0     if sheet getName       Front Page   amp  amp  myRange getColumn      1  amp  amp  myRange getRow    gt  1       if myRange getValue       Category 1          startCol   1        else if myRange getValue       Category 2          startCol   2        else if myRange getValue       Category 3          startCol   3        else if myRange getValue       Category 4          startCol   4        else         startCol   10          if startCol  gt  0  amp  amp  startCol  lt  10       option   dvSheet getSheetValues 3 startCol 10 1       var dv   SpreadsheetApp newDataValidation        dv setAllowInvalid false           dv setHelpText  Some help text here        dv requireValueInList option  true       sheet getRange myRange getRow   myRange getColumn     1  setDataValidation dv build             if startCol    10       sheet getRange myRange getRow   myRange getColumn     1  clearDataValidations                 After that  set up a trigger in the script editor screen by going to Edit   Current Project Triggers  This will bring up a window to have you select various drop downs to eventually end up at this      You should be good to go after that

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

Examples related to validation

Examples related to google-apps-script

Examples related to google-sheets