¿Cómo se consigue que una columna de subcategoría rellene un desplegable en función del valor seleccionado en el desplegable de categoría principal en google sheets?
He buscado en Google y no he encontrado ninguna solución buena, así que quería compartir la mía. Por favor, vea mi respuesta a continuación.
Puedes empezar con una hoja de google configurada con una página principal y una página de origen desplegable como la que se muestra a continuación.
Puede configurar la primera columna desplegable a través de los datos normales > Validaciones indicaciones del menú.
**Página principal
Página principal con el desplegable de la primera columna ya configurado]1.
Página fuente desplegable
Después de eso, necesitas configurar un script con el nombre onEdit
. (Si no usas ese nombre, getActiveRange() no hará nada más que devolver la celda A1)
Y usa el código proporcionado aquí:
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();
}
}
}
Después de eso, configurar un disparador en la pantalla del editor de secuencias de comandos por ir a Editar > Activadores del proyecto actual. Esto abrirá una ventana para que usted seleccione varios desplegables para terminar finalmente en esto:
¡Usted debe ser bueno para ir después de eso!
Edición: La respuesta que figura a continuación puede ser satisfactoria, pero tiene algunos inconvenientes:
Hay una pausa notable para la ejecución de la secuencia de comandos. I'm en una latencia de 160 ms, y it's suficiente para ser molesto.
Funciona construyendo un nuevo rango cada vez que se edita una fila determinada. Esto da un 'contenido no válido' a las entradas anteriores algunas veces.
Espero que otros puedan limpiar esto un poco.
Aquí'hay otra forma de hacerlo, que te ahorra un montón de nombrar rangos:
Tres hojas en la hoja de trabajo: llámalas Principal, Lista y DRange (para rango dinámico.) En la hoja Principal, la columna 1 contiene una marca de tiempo. Esta marca de tiempo se modifica al editar.
En Lista sus categorías y subcategorías se organizan como una simple lista. Estoy utilizando esto para el inventario de plantas en mi granja de árboles, por lo que mi lista se parece a esto:
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
...
Donde | indica la separación en columnas.
Para mayor comodidad, también utilicé los encabezados como nombres para los rangos con nombre.
El rango A1 tiene la fórmula
=Max(Main!A2:A1000)
Esto devuelve la marca de tiempo más reciente.
A2 a A4 tienen variaciones:
=vlookup($A$1,Inventory!$A$1:$E$1000,2,False)
con el 2 se incrementa para cada celda a la derecha.
De A2 a A4 aparecerán el grupo, el género y la especie seleccionados.
Debajo de cada uno de estos, hay un comando de filtro algo como esto:
=unique(filter(Nombre_bot,REGEXMATCH(Nombre_bot,C1)))
Estos filtros rellenarán un bloque inferior con entradas que coincidan con el contenido de la celda superior.
Los filtros pueden modificarse para adaptarlos a sus necesidades y al formato de su lista.
Volver a Principal: La validación de datos en Principal se realiza utilizando rangos de DRange.
El script que uso:
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
}
}
}
}
Presentación original de Youtube que me dio la mayor parte del componente timestamp de onEdit: [
Aquí tienes otra solución basada en la aportada por @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() );
}
Tiene algunas ventajas sobre el otro enfoque:
Así que, cómo usarlo:
¡Que aproveche!