Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
// 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;
}
@tatiana1808

This comment has been minimized.

Copy link

@tatiana1808 tatiana1808 commented Feb 9, 2020

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

@Max-Makhrov

This comment has been minimized.

Copy link
Owner Author

@Max-Makhrov Max-Makhrov commented Feb 10, 2020

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

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

@tatiana1808

This comment has been minimized.

Copy link

@tatiana1808 tatiana1808 commented Feb 10, 2020

@Gigses

This comment has been minimized.

Copy link

@Gigses Gigses commented Feb 21, 2020

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

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

@anubasa

This comment has been minimized.

Copy link

@anubasa anubasa commented Oct 4, 2020

image

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

@Max-Makhrov

This comment has been minimized.

Copy link
Owner Author

@Max-Makhrov Max-Makhrov commented Oct 5, 2020

image

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

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

@tatiana1808

This comment has been minimized.

Copy link

@tatiana1808 tatiana1808 commented Apr 8, 2021

@Max-Makhrov

This comment has been minimized.

Copy link
Owner Author

@Max-Makhrov Max-Makhrov commented Apr 13, 2021

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment