Create a gist now

Instantly share code, notes, and snippets.

@rheajt /Code.gs
Last active May 29, 2017

What would you like to do?
create data validation cells with either named ranges or columns from a sheet in Google Sheets
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('MULTI DATA VALIDATION')
.addItem('Open', 'openSidebar')
.addToUi();
}
function onInstall() {
installEditTrigger();
onOpen();
}
function openSidebar() {
var html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('Multi Data Validation');
return SpreadsheetApp.getUi().showSidebar(html);
}
/**
* Used to fill the select field in the sidebar
*/
function getSheets() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var sheetNames = [];
for(var i = 0; i < sheets.length; i++) {
sheetNames.push(sheets[i].getName());
}
return sheetNames;
}
function getDataVals() {
var dataVals = PropertiesService.getDocumentProperties().getProperty('DATA_VALS');
return JSON.parse(dataVals) || {};
}
function setDataVals(dataVals) {
dataVals = JSON.stringify(dataVals);
PropertiesService.getDocumentProperties().setProperty('DATA_VALS', dataVals);
}
function installEditTrigger() {
var sheet = SpreadsheetApp.openById('17c1fQcEiJXRmWhdLJu7MjMfuqw3nVSsus1_jF7SBWPo');
// var sheet = SpreadsheetApp.getActive();
ScriptApp.newTrigger('onEdit').forSpreadsheet(sheet).onEdit().create();
}
function onEdit(e) {
if(e.range.getDataValidation()) {
var dataVals = getDataVals();
var column = e.range.getColumn() + 1;
var row = e.range.getRow();
var adjCell = columnToLetter(column, row);
var subCats = dataVals[e.value.toString()];
var rule = SpreadsheetApp.newDataValidation()
.requireValueInList(subCats, true)
.build();
SpreadsheetApp.getActiveSheet().getRange(adjCell).setDataValidation(rule);
}
}
//turn numbers into "A1" format for cell value
function columnToLetter(column, row) {
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter + row;
}
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<style>
label {display: block;}
section.settings {
display: none;
}
</style>
<body class="sidebar">
<div class="block">
<button class="action" onclick="show('named-range')">Named Ranges</button>
<button class="action" onclick="show('multi-column')">Multiple Columns</button>
</div>
<section class="settings" id="named-range">
<div class="block">
<h1>Named Range Categories</h1>
<p>This method requires that you manually define the named ranges of your categories.</p>
<p>Once you have defined your named ranges click create below.</p>
</div>
<hr/>
<div class="block">
<button class="create" onclick="createDataValidation('named')">Create</button>
</div>
</section>
<section class="settings" id="multi-column">
<div class="block">
<h1>Multiple Columns</h1>
<p>This method takes all of the columns from the sheet you select and creates data validation from them.</p>
<p>Once you have selected the sheet that contains your categories in separate colums, click create below.</p>
</div>
<div class="block">
<label>Sheet with categories</label>
<select id="multi-category-sheet"></select>
</div>
<hr/>
<div class="block">
<button class="create" onclick="createDataValidation('multi')">Create</button>
</div>
</section>
</body>
<script>
function main() {
google.script.run
.withSuccessHandler(placeSheets)
.getSheets();
}
function placeSheets(sheets) {
var multiSheet = document.getElementById('multi-category-sheet');
for(var i = 0; i < sheets.length; i++) {
multiSheet.options.add(new Option(sheets[i], sheets[i]));
}
}
function createDataValidation(type) {
switch(type) {
case 'named':
google.script.run
.withSuccessHandler(google.script.host.close)
.namedRangeCategories();
break;
case 'multi':
var sheet = document.getElementById('multi-category-sheet').value;
google.script.run
.withSuccessHandler(google.script.host.close)
.multiColumnCategories(sheet);
break;
default: break;
}
// google.script.run
// .withSuccessHandler(google.script.host.close)
// .createDataValidation(data);
}
/**
* show the correct inputs
*/
function show(settings) {
var sections = document.getElementsByClassName('settings');
for(var i = 0; i < sections.length; i++) {
sections[i].style.display = 'none';
}
document.getElementById(settings).style.display = 'block';
}
window.onload = main;
</script>
getMultiCategories
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment