google_sheets_linked_dropdowns.gs

/**
 * Allow dependent drop downs in Google Sheets
 *
 * - Allows any number of nested dropdown columns.
 * - Nested dropdowns can occur anywhere in the sheet.
 * - All options for the dropdowns live in a unique separate sheet
 * - Nesting works only for adjacent columns.
 * - Works also when editing a range of cells.
 *
 * Updated code at https://gist.github.com/0bf358bafc2b4d143a0e33dfb912020c
 * Inspiration from https://stackoverflow.com/a/28213597/2972183
 */


/**
 * Maximum number of rows for option ranges
 */
MAX_NUMBER_OF_OPTION_ROWS = 100;


/**
 * Apply function crateDropdownBasedOnSheet() to each cell
 * in active range.
 */
function onEdit() {

    // Change here 
    var sheetsWithDropdownsNames = ["Abbigliamento", "Calzature"];
    var sheetsWithOptionsNames = ["Opzioni Abbigliamento", "Opzioni Calzature"];

    // Don't change from here on
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var activeSheet = SpreadsheetApp.getActiveSheet();

    // Take note of start time
    console.time( 'script' );

    // To avoid mistakes, all string comparisons will be lowercase
    sheetsWithDropdownsNames = sheetsWithDropdownsNames.map( function(x) { return x.toLowerCase(); } );
    sheetsWithOptionsNames = sheetsWithOptionsNames.map( function(x) { return x.toLowerCase(); } );

    // Proceed only if on the right sheet
    var sheetWithDropdownsIndex = sheetsWithDropdownsNames.indexOf( activeSheet.getName().toLowerCase() );
    if ( sheetWithDropdownsIndex < 0 ) {
        Logger.log( "Not in sheet '" + sheetsWithDropdownsNames + "', exiting..." );
        return;
    }
    
    // Proceed only if we correctly got the name of option sheet
    var sheetWithOptionsName = sheetsWithOptionsNames[ sheetWithDropdownsIndex ];
    if ( ! sheetWithOptionsName ) {
        Logger.log( "Could not find sheet containing options, exiting..." );
        return;
    }
    // Logger.log( "Will get options from sheet '" + sheetWithOptionsName + "'" );

    // Get range that has been edited
    var activeRange = SpreadsheetApp.getActiveRange();

    // Loop through range and apply main function
    for (var i = 1; i <= activeRange.getNumRows(); i++) {
        for (var j = 1; j <= activeRange.getNumColumns(); j++) {

            var activeCell = activeRange.getCell(i,j);
            // Logger.log( "Processing cell " + activeCell.getA1Notation() + " with value '" + activeCell.getValue() + "'" );

            // Insert a new dropdown in the cell immediately to the right
            // of the current cell
            var targetCell = activeSheet.getRange( activeCell.getRow(), activeCell.getColumn() + 1 );            
            crateDropdownBasedOnSheet( activeCell, targetCell, sheetWithOptionsName );

        }
    }
    
    // Script ended
    console.timeEnd( 'script' );

}


/**
 * Create a dropdown list in targetCell, based on the value in
 * controlCell.
 *
 * The options appearing in the dropdown will be picked from a separate
 * sheet.
 */
function crateDropdownBasedOnSheet( controlCell, targetCell, sheetWithOptionsName ) {

    // Proceed only if the control cell is a dropdown
    if ( ! controlCell.getDataValidation() ) {
        Logger.log( "Control cell is not a dropdwon, exiting...." );
        return;
    }

    // Proceed only if there's something in the control cell
    if ( controlCell.getValue().length <= 0 ) {
        Logger.log( "Control cell is empty, exiting...." );
        return;
    }

    // Get column title of target cell
    var targetColumn = targetCell.getColumn();
    var targetColumnTitle = getColumnName( targetColumn ).toLowerCase();
    if ( ! targetColumnTitle ) {
        Logger.log( "Title empty or missing for edited cell, exiting..." );
        return;
    }
    
    // Get column where to look for the options
    var columnWithOptionsTitle = targetColumnTitle + ' - ' + controlCell.getValue();
    columnWithOptionsTitle = columnWithOptionsTitle.toLowerCase();
    // Logger.log( "Will look for options in column '" + columnWithOptionsTitle + "' in sheet '" + sheetWithOptionsName + "'" );

    // Check that sheet with option values exists
    var sheetWithOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName( sheetWithOptionsName );
    if ( sheetWithOptions == null ) {
        Logger.log( "Sheet NOT found, exiting..." );
        return;
    }

    // Find column containing options for the dropdown
    var titleRowValues = sheetWithOptions.getSheetValues( 1, 1, 1, -1 );
    titleRowValues = titleRowValues[0].map( function(x) { return x.toLowerCase(); } );
    var columnWithOptions = titleRowValues.indexOf( columnWithOptionsTitle ) + 1;
    if ( columnWithOptions == 0 ) {
        Logger.log( "Could not find column '" + columnWithOptionsTitle + "' in sheet '" + sheetWithOptionsName + "'" );
        return;
    }
    // Logger.log( "Found option list for '" + columnWithOptionsTitle + "' in column " + columnWithOptions + " of sheet '" + sheetWithOptionsName + "'" );

    // Extract options
    var columnWithOptionsRange = sheetWithOptions.getRange(
      2, // start from 2nd row because 1st row is header
      columnWithOptions, // column with the options for the edited cell
      MAX_NUMBER_OF_OPTION_ROWS, // ideally we need all values in the column, and then leave some room
      1 // we need just that column
    );
    // Logger.log( "Options for '" + columnWithOptionsTitle + "': " + columnWithOptionsRange.getValues() );

    // Create a validation object where the only available options are those we just obtained
    var possibleValuesValidation = SpreadsheetApp.newDataValidation();
    possibleValuesValidation.setAllowInvalid( true );
    possibleValuesValidation.requireValueInRange( columnWithOptionsRange, true );

    // Apply the validation object to the target cell
    // Logger.log( "Applying validation to cell " + targetCell.getA1Notation() + "..." );
    targetCell.setDataValidation( possibleValuesValidation.build() );
    // Logger.log( "Validation ok!" );

}