Dynamic-Dependent-Drop-Down-Lists.js

// please refer here for more info:
// https://sheetswithmaxmakhrov.wordpress.com/2018/01/03/smart-data-validation-create-dependent-drop-down-lists-in-goole-sheets/

function onEdit(event) 
{
  var maxRows = false;
	
  // Change Settings:
  //--------------------------------------------------------------------------------------
  var TargetSheet = 'Main'; // name of sheet with data validation
  var LogSheet = 'Data1'; // name of sheet with data
  var NumOfLevels = 4; // number of levels of data validation
  var lcol = 2; // 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,2]; // offsets for levels
  //                   ^ means offset column #4 on one position right.	
  // var maxRows = 500; // to set the last row of validation; delete this row if not needed

  
  // =====================================================================================
	
  SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets, maxRows);
  
  // Change Settings:
  //--------------------------------------------------------------------------------------
  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
  // var maxRows = 500; // to set the last row of validation, delete this row if not needed
  // =====================================================================================  
  SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets, maxRows);

  
}



function SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets, maxRows) 
{
  //--------------------------------------------------------------------------------------
  // The event handler, adds data validation for the input parameters
  //--------------------------------------------------------------------------------------
  
  var FormulaSplitter = ';'; // depends on regional setting, ';' or ',' works for US
  //--------------------------------------------------------------------------------------
  
  //	===================================   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)
  //
  //    ======================================================================================

	// Checks
	var ts = event.source.getActiveSheet();
	var sname = ts.getName(); 
	if (sname !== TargetSheet) { return -1;  } // not main sheet
    // Test if range fits
    var br = event.range;
    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
	var ColNum = br.getWidth();
    
	if ((scol + ColNum - 1) < lcol) { return -2; }  // columns... 
	if (srow < lrow) { return -3; } // rows
	
	if (maxRows)
	{
		if (srow > maxRows)	 { return -3.5; }	// max rows
	}
	
	// Test range is in levels
	var columnsLevels = getColumnsOffset_(offsets, lcol); // Columns for all levels	
	var CurrentLevel = getCurrentLevel_(ColNum, br, scol, columnsLevels);
	if(CurrentLevel === 1) { return -4; } // out of data validations
    if(CurrentLevel > NumOfLevels) { return -5; } // last level	


/*
		ts - sheet with validation, sname = name of sheet
		
						 NumOfLevels = 4                     
						 offsets = [1,1,1,2] - last offset is 2 because need to skip 1 column
						 columnsLevels = [4,5,6,8] - Columns of validation
						 
										 Columns 7 is skipped
										 |
		   1	2	 3	  4    5    6    7    8    9    
		|----+----+----+----+----+----+----+----+----+
	1	|	 |    |    |    |    |    |  x |    |    |
		|----+----+----+----+----+----+----+----+----+
	2	|	 |    |    |  v |  V |  ? |  x |  ? |    | lrow = 2 - number of row where validation starts
		|----+----+----+----+----+----+----+----+----+
	3	|	 |    |    |    |    |    |  x |    |    |
		|----+----+----+----+----+----+----+----+----+
	4	|	 |    |    |    |    |    |  x |    |    |
		|----+----+----+----+----+----+----+----+----+
					   |  |	  |	    |           |
					   |  |	  |	    | Currentlevel = 3 - the number of level to change
					   |  |	  |	                |
					   |  |   | br - cell, user changes: scol - column, srow - row,
					   |  |          ColNum = 1 - width   
					   |__|________   _.....____|
						  |         v
						  |  Drop-down lists					
						  |
						  | lcol = 4 - number of column where validation starts
*/
	// Constants
	var ReplaceCommas = getDecimalMarkIsCommaLocals(); // // ReplaceCommas = true if locale uses commas to separate decimals
	var ls = SpreadsheetApp.getActive().getSheetByName(LogSheet); // Data sheet       				         
    var RowNum = br.getHeight();
	/* 	Adjust the range 'br' 
	    ???       !
		xxx       x
		xxx       x 
		xxx  =>   x
		xxx       x
		xxx       x
	*/	
    br = ts.getRange(br.getRow(), columnsLevels[CurrentLevel - 2], RowNum); 
	// Levels
    var HeadLevel = CurrentLevel - 1; // main level
    var X = NumOfLevels - CurrentLevel + 1; // number of levels left       
    // determine columns on the sheet "Data"
    var KudaCol = NumOfLevels + 2;
    var KudaNado = ls.getRange(1, KudaCol);  // 1 place for a formula
    var lastRow = ls.getLastRow();
    var ChtoNado = ls.getRange(1, KudaCol, lastRow, KudaCol); // the range with list, returned by a formula
    
    // ============================================================================= > loop >
    var CurrLevelBase = CurrentLevel; // remember the first current level
    
    

    for (var j = 1; j <= RowNum; j++) // [01] loop rows start
    {    
      // refresh first val  
      var currentRow = br.getCell(j, 1).getRow();      
      loopColumns_(HeadLevel, X, currentRow, NumOfLevels, CurrLevelBase, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts);
    } // [01] loop rows end

           

}


function getColumnsOffset_(offsets, lefColumn)
{
	// Columns for all levels
	var columnsLevels = [];
	var totalOffset = 0;	
	for (var i = 0, l = offsets.length; i < l; i++)
	{	
		totalOffset += offsets[i];
		columnsLevels.push(totalOffset + lefColumn - 1);
	}	
	
	return columnsLevels;
	
}

function test_getCurrentLevel()
{
  var br = SpreadsheetApp.getActive().getActiveSheet().getRange('A5:C5');
  var scol = 1;
  
  
  /*
            |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |
      range |xxxxx| 
   dv range |xxxxxxxxxxxxxxxxx|
     levels    1     2     3
      level          2
  
  */
  Logger.log(getCurrentLevel_(1, br, scol, [1,2,3])); // 2
  
  /*
            |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |
      range |xxxxxxxxxxx| 
   dv range |xxxxx|     |xxxxx|     |xxxxx|
     levels    1           2           3
      level                2
  
  */  
  Logger.log(getCurrentLevel_(2, br, scol, [1,3,5])); // 2
  
  /*
            |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |
      range |xxxxxxxxxxxxxxxxx| 
   dv range |xxxxx|                 |xxxxxxxxxxx| 
     levels    1                       2     3
      level                            2
  
  */    
  Logger.log(getCurrentLevel_(3, br, scol, [1,5,6])); // 2
  
  
  /*
            |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |
      range |xxxxxxxxxxxxxxxxx| 
   dv range |xxxxxxxxxxx|                             |xxxxx| 
     levels    1     2                                   3
      level                                              3
  
  */    
  Logger.log(getCurrentLevel_(3, br, scol, [1,2,8])); // 3
  
  
  /*
            |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |
      range |xxxxxxxxxxxxxxxxx| 
   dv range |xxxxxxxxxxxxxxxxx|
     levels    1     2     3
      level                      4 (error)
  
  */    
  Logger.log(getCurrentLevel_(3, br, scol, [1,2,3]));
  
  
  /*
            |  1  |  2  |  3  |  4  |  5  |  6  |  7  |  8  |
      range |xxxxxxxxxxxxxxxxx| 
   dv range                         |xxxxxxxxxxxxxxxxx|
     levels    
      level    1 (error)                      
  
  */    
  Logger.log(getCurrentLevel_(3, br, scol, [5,6,7])); // 1 
  
}


function getCurrentLevel_(ColNum, br, scol, columnsLevels)
{
	var colPlus = 2; // const
	if (ColNum === 1) { return columnsLevels.indexOf(scol) + colPlus; }
	var CurrentLevel = -1;
	var level = 0;
	var column = 0;
	for (var i = 0; i < ColNum; i++ )
	{
		column = br.offset(0, i).getColumn();
		level = columnsLevels.indexOf(column) + colPlus;
		if (level > CurrentLevel) { CurrentLevel = level; }
	}
	return CurrentLevel;
}



function loopColumns_(HeadLevel, X, currentRow, NumOfLevels, CurrentLevel, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts)
{
  for (var k = 1; k <= X; k++)
  {   
    HeadLevel = HeadLevel + k - 1; 
    CurrentLevel = CurrLevelBase + k - 1;
	var r = ts.getRange(currentRow, columnsLevels[CurrentLevel - 2]);
	var SearchText = r.getValue(); // searched text 
	X = loopColumn_(X, SearchText, HeadLevel, HeadLevel, currentRow, NumOfLevels, CurrentLevel, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts);
  } 
}


function loopColumn_(X, SearchText, HeadLevel, HeadLevel, currentRow, NumOfLevels, CurrentLevel, lastRow, FormulaSplitter, CurrLevelBase, columnsLevels, br, KudaNado, ChtoNado, ReplaceCommas, ts)
{
    

  // if nothing is chosen!
  if (SearchText === '') // condition value =''
  {
    // kill extra data validation if there were 
    // columns on the right
    if (CurrentLevel <= NumOfLevels) 
    {
      for (var f = 0; f < X; f++) 
      {
        var cell = ts.getRange(currentRow, columnsLevels[CurrentLevel + f - 1]);		  
        // clean & get rid of validation
        cell.clear({contentsOnly: true});              
        cell.clear({validationsOnly: true});
        // exit columns loop  
      }
    }
    return 0;	// end loop this row	
  }
  
  
  // formula for values
  var formula = getDVListFormula_(CurrentLevel, currentRow, columnsLevels, lastRow, ReplaceCommas, FormulaSplitter, ts);  
  KudaNado.setFormula(formula);
    
  
  // get response
  var Response = getResponse_(ChtoNado, lastRow, ReplaceCommas);
  var Variants = Response.length;
    

  // build data validation rule
  if (Variants === 0.0) // empty is found
  {
    return;
  }  
  if(Variants >= 1.0) // if some variants were found
  {
    
    var cell = ts.getRange(currentRow, columnsLevels[CurrentLevel - 1]);
    var rule = SpreadsheetApp
    .newDataValidation()
    .requireValueInList(Response, true)
    .setAllowInvalid(false)
    .build();
    // set validation rule
    cell.setDataValidation(rule);
  }    
  if (Variants === 1.0) // // set the only value
  {      
    cell.setValue(Response[0]);
    SearchText = null;
    Response = null;
    return X; // continue doing DV
  } // the only value
  
  return 0; // end DV in this row
  
}


function getDVListFormula_(CurrentLevel, currentRow, columnsLevels, lastRow, ReplaceCommas, FormulaSplitter, ts)
{
  
  var checkVals = [];
  var Offs = CurrentLevel - 2;
  var values = [];
  // get values and display values for a formula
  for (var s = 0; s <= Offs; s++)
  {
    var checkR = ts.getRange(currentRow, columnsLevels[s]);
    values.push(checkR.getValue());
  } 		  
  
  var LookCol = colName(CurrentLevel-1); // gets column name "A,B,C..."
  var formula = '=unique(filter(' + LookCol + '2:' + LookCol + lastRow; // =unique(filter(A2:A84

  var mathOpPlusVal = ''; 
  var value = '';

  // loop levels for multiple conditions  
  for (var i = 0; i < CurrentLevel - 1; i++) {            
    formula += FormulaSplitter; // =unique(filter(A2:A84;
    LookCol = colName(i);
    		
    value = values[i];

    mathOpPlusVal = getValueAndMathOpForFunction_(value, FormulaSplitter, ReplaceCommas); // =unique(filter(A2:A84;B2:B84="Text"
    
    if ( Array.isArray(mathOpPlusVal) )
    {
      formula += mathOpPlusVal[0];
      formula += LookCol + '2:' + LookCol + lastRow; // =unique(filter(A2:A84;ROUND(B2:B84
      formula += mathOpPlusVal[1];
    }
    else
    {
      formula += LookCol + '2:' + LookCol + lastRow; // =unique(filter(A2:A84;B2:B84
      formula += mathOpPlusVal;
    }
    
    
  }  
  
  formula += "))"; //=unique(filter(A2:A84;B2:B84="Text"))

  return formula;
}


function getValueAndMathOpForFunction_(value, FormulaSplitter, ReplaceCommas)
{
  var result = '';
  var splinter = '';	
	
  var type = typeof value;
  
 
  // strings
  if (type === 'string') return '="' + value.toString().replace(/"/g, '"&""""&"') + '"';
  // date
  if(value instanceof Date)
  {
	return ['ROUND(', FormulaSplitter +'5)=ROUND(DATE(' + value.getFullYear() + FormulaSplitter + (value.getMonth() + 1) + FormulaSplitter + value.getDate() + ')' + '+' 
	      + 'TIME(' + value.getHours() + FormulaSplitter + value.getMinutes() + FormulaSplitter + value.getSeconds() + ')' + FormulaSplitter + '5)'];	  
  }  
  // numbers
  if (type === 'number')
  {
	if (ReplaceCommas)
	{
		return '+0=' + value.toString().replace('.', ',');		
	}
	else
	{
		return '+0=' + value;
	}
  }
  // booleans
  if (type === 'boolean')
  {
	  return '=' + value;
  }  
  // other
  return '=' + value;
	
}


function getResponse_(allRange, l, ReplaceCommas)
{
  var data = allRange.getValues();
  var data_ = allRange.getDisplayValues();
  
  var response = [];
  var val = '';
  for (var i = 0; i < l; i++)
  {
    val = data[i][0];
    if (val !== '') 
    {
      var type = typeof val;
      if (type === 'boolean' || val instanceof Date) val = String(data_[i][0]);
      if (type === 'number' && ReplaceCommas) val = val.toString().replace('.', ',')
      response.push(val);  
    }
  }
  
  return response;  
}




function colName(n) {
    var ordA = 'a'.charCodeAt(0);
    var ordZ = 'z'.charCodeAt(0);
    
    var len = ordZ - ordA + 1;

    var s = "";
    while(n >= 0) {
        s = String.fromCharCode(n % len + ordA) + s;
        n = Math.floor(n / len) - 1;
    }
    return s; 
}


function getDecimalMarkIsCommaLocals() {


// list of Locals Decimal mark = comma
var LANGUAGE_BY_LOCALE = {
    af_NA: "Afrikaans (Namibia)",
    af_ZA: "Afrikaans (South Africa)",
    af: "Afrikaans",
    sq_AL: "Albanian (Albania)",
    sq: "Albanian",
    ar_DZ: "Arabic (Algeria)",
    ar_BH: "Arabic (Bahrain)",
    ar_EG: "Arabic (Egypt)",
    ar_IQ: "Arabic (Iraq)",
    ar_JO: "Arabic (Jordan)",
    ar_KW: "Arabic (Kuwait)",
    ar_LB: "Arabic (Lebanon)",
    ar_LY: "Arabic (Libya)",
    ar_MA: "Arabic (Morocco)",
    ar_OM: "Arabic (Oman)",
    ar_QA: "Arabic (Qatar)",
    ar_SA: "Arabic (Saudi Arabia)",
    ar_SD: "Arabic (Sudan)",
    ar_SY: "Arabic (Syria)",
    ar_TN: "Arabic (Tunisia)",
    ar_AE: "Arabic (United Arab Emirates)",
    ar_YE: "Arabic (Yemen)",
    ar: "Arabic",
    hy_AM: "Armenian (Armenia)",
    hy: "Armenian",
    eu_ES: "Basque (Spain)",
    eu: "Basque",
    be_BY: "Belarusian (Belarus)",
    be: "Belarusian",
    bg_BG: "Bulgarian (Bulgaria)",
    bg: "Bulgarian",
    ca_ES: "Catalan (Spain)",
    ca: "Catalan",
    tzm_Latn: "Central Morocco Tamazight (Latin)",
    tzm_Latn_MA: "Central Morocco Tamazight (Latin, Morocco)",
    tzm: "Central Morocco Tamazight",
    da_DK: "Danish (Denmark)",
    da: "Danish",
    nl_BE: "Dutch (Belgium)",
    nl_NL: "Dutch (Netherlands)",
    nl: "Dutch",
    et_EE: "Estonian (Estonia)",
    et: "Estonian",
    fi_FI: "Finnish (Finland)",
    fi: "Finnish",
    fr_BE: "French (Belgium)",
    fr_BJ: "French (Benin)",
    fr_BF: "French (Burkina Faso)",
    fr_BI: "French (Burundi)",
    fr_CM: "French (Cameroon)",
    fr_CA: "French (Canada)",
    fr_CF: "French (Central African Republic)",
    fr_TD: "French (Chad)",
    fr_KM: "French (Comoros)",
    fr_CG: "French (Congo - Brazzaville)",
    fr_CD: "French (Congo - Kinshasa)",
    fr_CI: "French (Côte d’Ivoire)",
    fr_DJ: "French (Djibouti)",
    fr_GQ: "French (Equatorial Guinea)",
    fr_FR: "French (France)",
    fr_GA: "French (Gabon)",
    fr_GP: "French (Guadeloupe)",
    fr_GN: "French (Guinea)",
    fr_LU: "French (Luxembourg)",
    fr_MG: "French (Madagascar)",
    fr_ML: "French (Mali)",
    fr_MQ: "French (Martinique)",
    fr_MC: "French (Monaco)",
    fr_NE: "French (Niger)",
    fr_RW: "French (Rwanda)",
    fr_RE: "French (Réunion)",
    fr_BL: "French (Saint Barthélemy)",
    fr_MF: "French (Saint Martin)",
    fr_SN: "French (Senegal)",
    fr_CH: "French (Switzerland)",
    fr_TG: "French (Togo)",
    fr: "French",
    gl_ES: "Galician (Spain)",
    gl: "Galician",
    ka_GE: "Georgian (Georgia)",
    ka: "Georgian",
    de_AT: "German (Austria)",
    de_BE: "German (Belgium)",
    de_DE: "German (Germany)",
    de_LI: "German (Liechtenstein)",
    de_LU: "German (Luxembourg)",
    de_CH: "German (Switzerland)",
    de: "German",
    el_CY: "Greek (Cyprus)",
    el_GR: "Greek (Greece)",
    el: "Greek",
    hu_HU: "Hungarian (Hungary)",
    hu: "Hungarian",
    is_IS: "Icelandic (Iceland)",
    is: "Icelandic",
    id_ID: "Indonesian (Indonesia)",
    id: "Indonesian",
    it_IT: "Italian (Italy)",
    it_CH: "Italian (Switzerland)",
    it: "Italian",
    kab_DZ: "Kabyle (Algeria)",
    kab: "Kabyle",
    kk_KZ: "Kazakhstan",
    kl_GL: "Kalaallisut (Greenland)",
    kl: "Kalaallisut",
    lv_LV: "Latvian (Latvia)",
    lv: "Latvian",
    lt_LT: "Lithuanian (Lithuania)",
    lt: "Lithuanian",
    mk_MK: "Macedonian (Macedonia)",
    mk: "Macedonian",
    naq_NA: "Nama (Namibia)",
    naq: "Nama",
    pl_PL: "Polish (Poland)",
    pl: "Polish",
    pt_BR: "Portuguese (Brazil)",
    pt_GW: "Portuguese (Guinea-Bissau)",
    pt_MZ: "Portuguese (Mozambique)",
    pt_PT: "Portuguese (Portugal)",
    pt: "Portuguese",
    ro_MD: "Romanian (Moldova)",
    ro_RO: "Romanian (Romania)",
    ro: "Romanian",
    ru_MD: "Russian (Moldova)",
    ru_RU: "Russian (Russia)",
    ru_UA: "Russian (Ukraine)",
    ru: "Russian",
    seh_MZ: "Sena (Mozambique)",
    seh: "Sena",
    sk_SK: "Slovak (Slovakia)",
    sk: "Slovak",
    sl_SI: "Slovenian (Slovenia)",
    sl: "Slovenian",
    es_AR: "Spanish (Argentina)",
    es_BO: "Spanish (Bolivia)",
    es_CL: "Spanish (Chile)",
    es_CO: "Spanish (Colombia)",
    es_CR: "Spanish (Costa Rica)",
    es_DO: "Spanish (Dominican Republic)",
    es_EC: "Spanish (Ecuador)",
    es_SV: "Spanish (El Salvador)",
    es_GQ: "Spanish (Equatorial Guinea)",
    es_GT: "Spanish (Guatemala)",
    es_HN: "Spanish (Honduras)",
    es_419: "Spanish (Latin America)",
    es_MX: "Spanish (Mexico)",
    es_NI: "Spanish (Nicaragua)",
    es_PA: "Spanish (Panama)",
    es_PY: "Spanish (Paraguay)",
    es_PE: "Spanish (Peru)",
    es_PR: "Spanish (Puerto Rico)",
    es_ES: "Spanish (Spain)",
    es_US: "Spanish (United States)",
    es_UY: "Spanish (Uruguay)",
    es_VE: "Spanish (Venezuela)",
    es: "Spanish",
    sv_FI: "Swedish (Finland)",
    sv_SE: "Swedish (Sweden)",
    sv: "Swedish",
    tr_TR: "Turkish (Turkey)",
    tr: "Turkish",
    uk_UA: "Ukrainian (Ukraine)",
    uk: "Ukrainian",
    vi_VN: "Vietnamese (Vietnam)",
    vi: "Vietnamese"
}


var SS = SpreadsheetApp.getActiveSpreadsheet();
var LocalS = SS.getSpreadsheetLocale();


if (LANGUAGE_BY_LOCALE[LocalS] == undefined) {
  return false;
  
}
  //Logger.log(true);
  return true;
}

У меня ничего не получилось, скопировала скрипт, поменяла те значения которые указаны в видео, но увы, помогите настроить

У меня ничего не получилось, скопировала скрипт, поменяла те значения которые указаны в видео, но увы, помогите настроить

Дайте пример файла

Спасибо огромное!!! за скрип, все получилось, наверное скопировала не так 10.02.2020, 13:15, "Max Makhrov" <[email protected]>:У меня ничего не получилось, скопировала скрипт, поменяла те значения которые указаны в видео, но увы, помогите настроитьДайте пример файла—You are receiving this because you commented.Reply to this email directly, view it on GitHub, or unsubscribe.

поменяла те значения которые указаны в видео

Где посмотреть видео?

Ошибка в строке 37, вроде все сделал по инструкции

Ошибка в строке 37, вроде все сделал по инструкции

Функция onEdit — это специальное название. Ее не запускают из редактора. Она срабатывает сама как триггер на изменения в книге. Поменяйте что-то на листе, и функция onEdit сработает сама. Эту функцию можно тестировать с тела скрипта, так:
https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas

https://docs.google.com/spreadsheets/d/1G1xSC4mUiwBdQrW4eo2Q7qPyRaIL9a1R14VSPRWgCqY/edit?usp=sharingМакси добрый день!Воспользовалась вашим скриптом, но есть проблемаскрипт распространился на все листы. хотя названия я поменялат.е добавляю новый лист в книгу и начинаю вводить данные и автоматом срабатывает проверка данных, хотя я ее не делаю, удаляю проверку данных на всем листе но когда начинаю заносить данные она появляется опять, помогите .....пыталась даже сделать другую проверку данных, но увы скрипт все перебивает не знаю где накосячила, в листе "база" все работает как надоподключено два справочника, проблем нет но на других листах мне это не нужно. называются они по другому, но все равно срабатывает макроскак сделать так чтобы макросы работали только на тех листах на которых я прошуможет в дальнейшем и добавлю на другие листы связанные списки, но хотелось бы чтобы я это могла контролироватьпомогите пожалуйста, замучилась ужеYou are receiving this because you commented.Reply to this email directly, view it on GitHub, or unsubscribe.

https://docs.google.com/spreadsheets/d/1G1xSC4mUiwBdQrW4eo2Q7qPyRaIL9a1R14VSPRWgCqY/edit?usp=sharingМакси добрый день!Воспользовалась вашим скриптом, но есть проблемаскрипт распространился на все листы. хотя названия я поменялат.е добавляю новый лист в книгу и начинаю вводить данные и автоматом срабатывает проверка данных, хотя я ее не делаю, удаляю проверку данных на всем листе но когда начинаю заносить данные она появляется опять, помогите .....пыталась даже сделать другую проверку данных, но увы скрипт все перебивает не знаю где накосячила, в листе "база" все работает как надоподключено два справочника, проблем нет но на других листах мне это не нужно. называются они по другому, но все равно срабатывает макроскак сделать так чтобы макросы работали только на тех листах на которых я прошуможет в дальнейшем и добавлю на другие листы связанные списки, но хотелось бы чтобы я это могла контролироватьпомогите пожалуйста, замучилась ужеYou are receiving this because you commented.Reply to this email directly, view it on GitHub, or unsubscribe.

Татьяна, у вас в файле 2 проекта, и скрипт другой.

Предлагаю начать с изучения простого примера:
https://docs.google.com/spreadsheets/d/19tHKSMuEGeeyZPduRJDahgf8--KUKNue7I-w06GZ9yg/copy

Dear Sir, your script is so wonderful, it helped me a lot. is it possible to make it works on Data 3 or mayabe Data 4?

Hi all, This script is not supported any more. Please check my latest version here: https://max-makhrov.medium.com/dependent-dropdown-lists-in-google-sheets-v2021-eab0bd1d7670

…

On Thu, Oct 7, 2021 at 8:21 PM yoenoess ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ Dear Sir, your script is so wonderful, it helped me a lot. is it possible to make it works on Data 3 or mayabe Data 4? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <https://gist.github.com/060c21da4f280b6faac82337dadb16e5#gistcomment-3919723>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AESUS5GMU6LSGDW7D6ERLQDUFXJKLANCNFSM4KSCVBEQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

-- Sincerely, Max Makhrov Spreadsheets Developer

Hi all, This script is not supported any more. Please check my latest version here: https://max-makhrov.medium.com/dependent-dropdown-lists-in-google-sheets-v2021-eab0bd1d7670

thankyou, you are so awosme