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.
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" && myRange.getColumn() == 1 && myRange.getRow() > 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 > 0 && startCol < 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!
Note
The scripts have a limit: it handles up to 500 values in a single drop-down list.
The script was released in January, 2018. Please see:
Improvements:
Versions of script
This solution is not perfect, but it gives some benefits:
First of all, here's working example, so you can test it before going further.
My plan:
Data > ValidationPrepare 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:
function SmartDataValidation(event)
{
//--------------------------------------------------------------------------------------
// The event handler, adds data validation for the input parameters
//--------------------------------------------------------------------------------------
// Declare some variables:
//--------------------------------------------------------------------------------------
var TargetSheet = 'Main' // name of the sheet where you want to verify the data
var LogSheet = 'Data' // name of the sheet with information
var NumOfLevels = 4 // number of associated drop-down list levels
var lcol = 2; // number of the leftmost column, in which the changes are checked; A = 1, B = 2, etc.
var lrow = 2; // line number from which the rule will be valid
//--------------------------------------------------------------------------------------
// =================================== key variables =================================
//
// ss sheet we change (TargetSheet)
// br range to change
// scol number of column to edit
// srow number of row to edit
// CurrentLevel level of drop-down, which we change
// HeadLevel main level
// r current cell, which was changed by user
// X number of levels could be checked on the right
//
// ls Data sheet (LogSheet)
//
// ======================================================================================
// [ 01 ].Track sheet on which an event occurs
var ts = event.source.getActiveSheet();
var sname = ts.getName();
if (sname == TargetSheet)
{
// ss -- is the current book
var ss = SpreadsheetApp.getActiveSpreadsheet();
// [ 02 ]. If the sheet name is the same, you do business...
var ls = ss.getSheetByName(LogSheet); // data sheet
// [ 03 ]. Determine the level
//-------------- The changing sheet --------------------------------
var br = event.source.getActiveRange();
var scol = br.getColumn(); // the column number in which the change is made
var srow = br.getRow() // line number in which the change is made
// Test if column fits
if (scol >= lcol)
{
// Test if row fits
if (srow >= lrow)
{
var CurrentLevel = scol-lcol+2;
// adjust the level to size of
// range that was changed
var ColNum = br.getLastColumn() - scol + 1;
CurrentLevel = CurrentLevel + ColNum - 1;
// also need to adjust the range 'br'
if (ColNum > 1)
{
br = br.offset(0,ColNum-1);
} // wide range
var HeadLevel = CurrentLevel - 1; // main level
// split rows
var RowNum = br.getLastRow() - srow + 1;
var X = NumOfLevels - CurrentLevel + 1;
// the current level should not exceed the number of levels, or
// we go beyond the desired range
if (CurrentLevel <= NumOfLevels )
{
// determine columns on the sheet "Data"
var KudaCol = NumOfLevels + 2
var KudaNado = ls.getRange(1, KudaCol);
var lastRow = ls.getLastRow(); // get the address of the last cell
var ChtoNado = ls.getRange(1, KudaCol, lastRow, KudaCol);
// ============================================================================= > loop >
for (var j = 1; j <= RowNum; j++)
{
for (var k = 1; k <= X; k++)
{
HeadLevel = HeadLevel + k - 1; // adjust parent level
CurrentLevel = CurrentLevel + k - 1; // adjust current level
var r = br.getCell(j,1).offset(0,k-1,1);
var SearchText = r.getValue(); // searched text
// if anything is choosen!
if (SearchText != '')
{
//-------------------------------------------------------------------
// [ 04 ]. define variables to costumize data
// for future data validation
//--------------- Sheet with data --------------------------
// combine formula
// repetitive parts
var IndCodePart = 'INDIRECT("R1C' + HeadLevel + ':R' + lastRow + 'C';
IndCodePart = IndCodePart + HeadLevel + '",0)';
// the formula
var code = '=UNIQUE(INDIRECT("R" & MATCH("';
code = code + SearchText + '",';
code = code + IndCodePart;
code = code + ',0) & "C" & "' + CurrentLevel
code = code + '" & ":" & "R" & COUNTIF(';
code = code + IndCodePart;
code = code + ',"' + SearchText + '") + MATCH("';
code = code + SearchText + '";';
code = code + IndCodePart;
code = code + ',0) - 1';
code = code + '& "C" & "' ;
code = code + CurrentLevel + '",0))';
// Got it! Now we have to paste formula
KudaNado.setFormulaR1C1(code);
// get required array
var values = [];
for (var i = 1; i <= lastRow; i++)
{
var currentValue = ChtoNado.getCell(i,1).getValue();
if (currentValue != '')
{
values.push(currentValue);
}
else
{
var Variants = i-1; // number of possible values
i = lastRow; // exit loop
}
}
//-------------------------------------------------------------------
// [ 05 ]. Build daya validation rule
var cell = r.offset(0,1);
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(values, true)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule);
if (Variants == 1)
{
cell.setValue(KudaNado.getValue());
} // the only value
else
{
k = X+1;
} // stop the loop through columns
} // not blanc cell
else
{
// kill extra data validation if there were
// columns on the right
if (CurrentLevel <= NumOfLevels )
{
for (var i = 1; i <= NumOfLevels; i++)
{
var cell = r.offset(0,i);
// clean
cell.clear({contentsOnly: true});
// get rid of validation
cell.clear({validationsOnly: true});
}
} // correct level
} // empty row
} // loop by cols
} // loop by rows
// ============================================================================= < loop <
} // wrong level
} // rows
} // columns...
} // main sheet
}
function onEdit(event)
{
SmartDataValidation(event);
}Here's set of variables that are to be changed, you'll find them in script:
var TargetSheet = 'Main' // name of the sheet where you want to verify the data
var LogSheet = 'Data' // name of the sheet with information
var NumOfLevels = 4 // number of associated drop-down list levels
var lcol = 2; // leftmost column, in which the changes are checked; A = 1, B = 2, etc.
var lrow = 2; // line number from which the rule will be valid
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.
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 >= 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
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:
So, how to use it:
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).
/**
* "on edit" event handler
*
* Based on JavierCane's answer in
*
* http://stackoverflow.com/questions/21744547/how-do-you-do-dynamic-dependent-drop-downs-in-google-sheets
*
* Each set of options has it own sheet named after the option. The
* values in this sheet are used to populate the drop-down.
*
* The top row is assumed to be a header.
*
* The sub-category column is assumed to be the next column to the right.
*
* If there are no sub-categories the next column along is cleared in
* case the previous selection did have options.
*/
function onEdit() {
var NESTED_SELECTS_SHEET_NAME = "Sitemap"
var NESTED_SELECTS_ROOT_COLUMN = 1
var SUB_CATEGORY_COLUMN = NESTED_SELECTS_ROOT_COLUMN + 1
var NUMBER_OF_ROOT_OPTION_CELLS = 3
var OPTION_POSSIBLE_VALUES_SHEET_SUFFIX = ""
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
var activeSheet = SpreadsheetApp.getActiveSheet()
if (activeSheet.getName() !== NESTED_SELECTS_SHEET_NAME) {
// Not in the sheet with nested selects, exit!
return
}
var activeCell = SpreadsheetApp.getActiveRange()
// Top row is the header
if (activeCell.getColumn() > SUB_CATEGORY_COLUMN ||
activeCell.getRow() === 1 ||
activeCell.getRow() > NUMBER_OF_ROOT_OPTION_CELLS + 1) {
// Out of selection range, exit!
return
}
var sheetWithActiveOptionPossibleValues = activeSpreadsheet
.getSheetByName(activeCell.getValue() + OPTION_POSSIBLE_VALUES_SHEET_SUFFIX)
if (sheetWithActiveOptionPossibleValues === null) {
// There are no further options for this value, so clear out any old
// values
activeSheet
.getRange(activeCell.getRow(), activeCell.getColumn() + 1)
.clearDataValidations()
.clearContent()
return
}
// 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())
} // onEdit()As Javier says:
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.
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://i.stack.imgur.com/CF6Xv.png', '推荐 三岁会撩人 的文章《How do you do dynamic / dependent drop downs in Go》','https://www.manongdao.com/article-1758047.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}