Skip to content

Instantly share code, notes, and snippets.

@carlosleonam
Forked from coinsandsteeldev/dialog.html
Last active February 21, 2021 23:25
Show Gist options
  • Save carlosleonam/5b7248f5a0a99d881feb333f3baddbcc to your computer and use it in GitHub Desktop.
Save carlosleonam/5b7248f5a0a99d881feb333f3baddbcc to your computer and use it in GitHub Desktop.
Google Sheets script to allow multi-select in cells with data-validation, and auto check previous options selected in current cell (adapted from https://www.youtube.com/watch?v=dm4z9l26O0I)
<!DOCTYPE html>
<html>
<head>
<script>
var data
var formId = 'form'
var content
var checked
function drawForm() {
if (!data) return
var outputEl = document.getElementById(formId);
var output = ''
if (Object.prototype.toString.call(data) === '[object Array]') {
for (var i = 0; i < data.length; i++) {
if (data[i].length == 1 ) {
if (data[i][0] == '') continue;
checked = ''
if ( content.includes( data[i][0] ) ) checked = ' checked'
output += '<input type=\'checkbox\' name=\''+data[i][0]+'\' value=\''+data[i][0]+'\''+ checked +' >'
output += data[i][0]
output += '<br>'
} else if (data[i].length > 1) {
if (data[i][0] == '') continue;
// left will be used as value
// the rest is title
output += '<input type=\'checkbox\' name=\''+data[i][0]+'\' value=\''+data[i][0]+'\'>'
output += data[i][0] + ' &ndash; '
for (var j = 1; j < data[i].length; j++) {
if (data[i][j] == '') continue
output += data[i][j] + '; '
}
output += '<br>'
}
}
} else {
output += '<p>This cell has no <a href="https://support.google.com/drive/answer/139705?hl=en">Data validation</a>.</p>';
}
outputEl.innerHTML = output
}
var onData = function(result) {
data = result[0]
content = result[1]
content = content.split(',')
drawForm()
}
google.script.run.withSuccessHandler(onData).getValidationData();
function set() {
google.script.run.withSuccessHandler(x=>{
}).fillCell(document.getElementById(formId))
}
function update() {
google.script.run.withSuccessHandler(x=>{
}).updateCell(document.getElementById(formId))
}
function reset() {
drawForm()
}
</script>
</head>
<body>
<div style='position:fixed; padding-top: 10px; background-color: white; height: 30px; width: 100%; top: 0;'>
<input type="button" value="Set" onclick="set()" />
<input type="button" value="Update" onclick="update()" />
<input type="button" value="Reset" onclick="reset()" />
<input type="button" value="Refresh validation" onclick="google.script.run.showDialog()" />
</div>
<div style="font-family: sans-serif; padding-top: 30px;">
<form id="form" name="form">
</form>
</div>
</body>
<html>
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Scripts')
.addItem('Multi-select for this cell...', 'showDialog')
.addToUi();
}
function showDialog() {
var html = HtmlService.createHtmlOutputFromFile('dialog').setSandboxMode(HtmlService.SandboxMode.IFRAME);
SpreadsheetApp.getUi()
.showSidebar(html);
}
function getValidationData(){
try {
// return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues();
return [
SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues(),
SpreadsheetApp.getActiveRange().getValue()
];
} catch(e) {
return null
}
}
function setValues_(e, update) {
var selectedValues = [];
for (var i in e) {
selectedValues.push(i);
}
var separator = ','
var total = selectedValues.length
if (total > 0) {
var range = SpreadsheetApp.getActiveRange()
var value = selectedValues.join(separator)
if (update) {
var values = range.getValues()
// check every cell in range
for (var row = 0; row < values.length; ++row) {
for (var column = 0; column < values[row].length; ++column) {
var currentValues = values[row][column].split(separator);//typeof values[row][column] === Array ? values[row][column].split(separator) : [values[row][column]+'']
// find same values and remove them
var newValues = []
for (var j = 0; j < currentValues.length; ++j) {
var uniqueValue = true
for(var i = 0; i < total; ++i) {
if (selectedValues[i] == currentValues[j]) {
uniqueValue = false
break
}
}
if (uniqueValue && currentValues[j].trim() != '') {
newValues.push(currentValues[j])
}
}
if (newValues.length > 0) {
range.getCell(row+1, column+1).setValue(newValues.join(separator)+separator+value)
} else {
range.getCell(row+1, column+1).setValue(value);
}
}
}
} else {
range.setValue(value);
}
}
}
function updateCell(e) {
return setValues_(e, true)
}
function fillCell(e) {
setValues_(e)
}
@OnodOfTheNorth
Copy link

if you're here and this code isn't working for you I made a fork that addresses some of the issues and functions properly.

https://gist.github.com/GeorgeNavarre/fdaeb3c3b49e246b9fae8aefe9c30448

@OnodOfTheNorth
Copy link

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