Skip to content

Instantly share code, notes, and snippets.

@Max-Makhrov
Last active May 27, 2021 06:21
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Max-Makhrov/1d0c56ba244be53d8f0e61adad2114b3 to your computer and use it in GitHub Desktop.
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
// 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