Pregunta ¿Cómo se hacen los menús desplegables dinámicos / dependientes en Hojas de cálculo de Google?


¿Cómo se obtiene una columna de subcategoría para rellenar un menú desplegable según el valor seleccionado en el menú desplegable de la categoría principal en hojas de Google?

Busqué en Google y no pude encontrar ninguna buena solución, por lo tanto, quería compartir la mía. Por favor, mira mi respuesta a continuación.


32
2018-02-13 03:23


origen


Respuestas:


Puede comenzar con una hoja de google configurada con una página principal y una página de origen desplegable como se muestra a continuación.

Puede configurar el menú desplegable de la primera columna a través de las indicaciones normales del menú Datos> Validaciones.

Pagina principal

Main Page with the drop down for the first column already populated.

Página de origen desplegable

Source page for all of the sub-categories needed

Después de eso, necesitas configurar un script con el nombre  onEdit. (Si no usa ese nombre, getActiveRange () no hará más que devolver la celda A1)

Y usa el código provisto 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, configure un disparador en la pantalla del editor de scripts yendo a Edición> Desencadenantes de proyectos actuales. Esto abrirá una ventana para que selecciones varios menús desplegables para finalmente terminar en esto:

Trigger set up

¡Deberías ser bueno para ir después de eso!


21
2018-02-13 03:23



Nota

Los scripts tienen un límite: maneja hasta 500 valores en una sola lista desplegable.

Nueva secuencia de comandos. 201801

El guión fue lanzado en enero de 2018. Por favor vea:

  1. La página principal con instrucciones y demostración, donde puede hacer una pregunta.
  2. Página de GitHub con instrucciones y código fuente

Mejoras:

  1. Acelerar
  2. Maneja múltiples reglas en 1 hoja
  3. Enlace otras hojas como fuente de datos.
  4. Orden de columnas personalizadas de listas desplegables

Old Script. <201801

Versiones de script

  1. v.1.
  2. v.2. 2016-03. Mejorado: funciona con duplicados en cualquier categoría. Por ejemplo, si tenemos list1 con modelos de automóviles y list2 con colores. El color se puede repetir en cualquier modelo.
  3. v3. 2017-01. Mejorado: no hay error cuando se ingresa el único valor.
  4. La versión más nueva: 2018-02. Ver el artículo aquí.

Esta solución no es perfecta, pero ofrece algunos beneficios:

  1. Le permite hacer múltiples listas desplegables
  2. Da más control
  3. Los datos de origen se colocan en la única hoja, por lo que es fácil de editar

Primero que nada, aquí está ejemplo de trabajo, entonces puedes probarlo antes de ir más allá.

When you choose one option, script makes new validation rule

Mi plan:

  1. Preparar datos
  2. Haga la primera lista como de costumbre: Data > Validation
  3. Agregar script, establecer algunas variables
  4. ¡Hecho!

Preparar datos

Los datos se ven como una sola tabla con todas las variantes posibles dentro de ella. Debe estar ubicado en una hoja separada, para que pueda ser utilizado por el script. Mira este ejemplo:

Sourse Data 

Aquí tenemos cuatro niveles, cada valor se repite. Tenga en cuenta que 2 columnas a la derecha de los datos están reservadas, por lo que no debe escribir / pegar ningún dato.


Primera validación simple de datos (DV)

Prepare una lista de valores únicos. En nuestro ejemplo, es una lista de Planetas. Encuentre espacio libre en la hoja con datos y pegue fórmula: =unique(A:A) En su hoja principal seleccione la primera columna, donde comenzará DV. Vaya a Datos> Validación y seleccione rango con una lista única.

4 columns right from data


Guión

Pegue este código en el editor de scripts:

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);
  
}

Aquí hay un conjunto de variables que se deben cambiar, las encontrará en el 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

Sugiero a todos, que conozcan bien las secuencias de comandos, envíen sus ediciones a este código. Supongo que hay una forma más simple de encontrar la lista de validación y hacer que el script se ejecute más rápido.


6
2018-02-17 12:30



Editar: la respuesta a continuación puede ser satisfactoria, pero tiene algunos inconvenientes:

  1. Hay una pausa notable para la ejecución del script. Estoy en una latencia de 160 ms, y es suficiente para ser molesto.

  2. Funciona al construir un nuevo rango cada vez que edita una fila determinada. Esto da un 'contenido inválido' a entradas previas Algo de tiempo 

Espero que otros puedan limpiar esto de alguna manera.

Aquí hay otra forma de hacerlo, que le ahorra una tonelada de nombres de rango:

Tres hojas en la hoja de trabajo: llámalas Main, List y DRange (para rango dinámico). En la hoja principal, la columna 1 contiene una marca de tiempo. Esta marca de tiempo se modifica en Edite.

En la lista, sus categorías y subcategorías se organizan como una lista simple. Estoy usando esto para el inventario de plantas en mi granja de árboles, así que mi lista se ve así:

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 separación en columnas.
Para mayor comodidad también usé los encabezados como nombres para rangos con nombre.

DRrange A1 tiene la fórmula

=Max(Main!A2:A1000)

Esto devuelve la marca de tiempo más reciente.

A2 a A4 tienen variaciones en:

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

con los 2 incrementándose para cada celda a la derecha.

Al ejecutar A2 a A4, tendrá el Grupo, el Género y la Especie seleccionados actualmente.

Debajo de cada uno de estos, hay un comando de filtro como este:

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

Estos filtros llenarán un bloque a continuación con entradas coincidentes con los contenidos de la celda superior.

Los filtros se pueden modificar para adaptarse a sus necesidades y al formato de su lista.

Volver a Principal: La validación de datos en Main se hace 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 proporcionó la mayor parte del componente de indicación de fecha y hora onEdit: https://www.youtube.com/watch?v=RDK8rjdE85Y


2
2017-09-30 14:47



Aquí tienes otra solución basada en la proporcionada 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 algunos beneficios sobre el otro enfoque:

  • No necesita editar el script cada vez que agrega una "opción raíz". Solo tiene que crear una nueva hoja con las opciones anidadas de esta opción raíz.
  • Refactore la secuencia de comandos que proporciona más nombres semánticos para las variables y así sucesivamente. Además, he extraído algunos parámetros de las variables para facilitar la adaptación a su caso específico. Solo debe configurar los primeros 3 valores.
  • No hay límite de valores de opción anidados (he usado el método getSheetValues ​​con el valor -1).

Entonces, cómo usarlo:

  1. Crea la hoja donde tendrás los selectores anidados
  2. Vaya a "Herramientas"> "Editor de secuencias de comandos ..." y seleccione la opción "Proyecto en blanco"
  3. Pega el código adjunto a esta respuesta
  4. Modifique las primeras 3 variables del script configurando sus valores y guárdelo
  5. Cree una hoja dentro de este mismo documento para cada valor posible del "selector raíz". Deben nombrarse como el valor + el sufijo especificado.

¡Disfrutar!


2
2018-01-29 11:28



Continuando con la evolución de esta solución, he aumentado la apuesta agregando soporte para múltiples selecciones de raíz y selecciones más anidadas. Este es un desarrollo adicional de la solución de JavierCane (que a su vez se basa en Tarheel).

/**
 * "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()

Como dice Javier:

  • Crea la hoja donde tendrás los selectores anidados
  • Vaya a "Herramientas"> "Editor de scripts ..." y seleccione "Proyecto en blanco" opción
  • Pega el código adjunto a esta respuesta
  • Modifique las constantes en la parte superior del script configurando sus valores y guardarlo
  • Cree una hoja dentro de este mismo documento para cada posible valor de el "selector de raíz". Deben nombrarse como el valor + el especificado sufijo.

Y si quieres verlo en acción, he creado una hoja de demostración y puedes ver el código si tomas una copia.


0
2018-05-30 19:33