Last active
May 27, 2021 06:21
-
-
Save Max-Makhrov/1d0c56ba244be53d8f0e61adad2114b3 to your computer and use it in GitHub Desktop.
2D Dependent Dropdowns for Google Sheets. ~70 lines of code. Classy sample
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Copy sample file here: | |
// https://docs.google.com/spreadsheets/d/1hUOuDZcfXxaLvJbEA-SxzF4oGGU3lW3iJyS4wAV8YWA/copy | |
function onEdit(e) { | |
dvl0_(e); | |
} | |
function dvl0_sets_() { | |
return [ | |
{ | |
lib: { | |
'Materials': "'lists'!A2:A", | |
'Services': "'lists'!B2:B", | |
'Outsource': "'lists'!C2:C", | |
'Production costs': "'lists'!D2:D" | |
}, | |
sheet: "main", | |
row: 2, | |
col1: 3, | |
col2: 4 | |
} | |
]; | |
} | |
function dvl0_(e) { | |
var sets = dvl0_sets_(); | |
var res = [], res0; | |
for (var i = 0; i < sets.length; i++) { | |
res0 = dvl0_run_(sets[i], e); | |
res.push(res0); | |
} | |
console.log(res); | |
} | |
function dvl0_run_(ini, e) { | |
var f = SpreadsheetApp.getActive(); | |
var r = e.range; | |
var s = r.getSheet(); | |
if (s.getName() !== ini.sheet) { | |
return 'wrong sheet'; | |
} | |
var val = e.value; | |
if (!val) { | |
return 'no value'; | |
} | |
var col = r.getColumn(); | |
if (col !== ini.col1) { | |
return 'wrong column'; | |
} | |
var row = r.getRow(); | |
if (row < ini.row) { | |
return 'wrong row'; | |
} | |
var lib = ini.lib; | |
var rA1 = lib[val]; | |
if (!rA1) { | |
var message = 'not found in lib' | |
f.toast(message); | |
return message; | |
} | |
var rTo = s.getRange(row, ini.col2); | |
var rule = SpreadsheetApp.newDataValidation(). | |
requireValueInRange(f.getRange(rA1)); | |
rTo.setDataValidation(rule); | |
return 'success!' | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment