Skip to content

Instantly share code, notes, and snippets.

@simon88pl
Forked from coinsandsteeldev/dialog.html
Last active June 3, 2024 10:33
Show Gist options
  • Save simon88pl/609cf5ee665117fc8597a7dee4afb598 to your computer and use it in GitHub Desktop.
Save simon88pl/609cf5ee665117fc8597a7dee4afb598 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, and sorting values alphabetically, and clickable options, and a bit more pleasant look & feel (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() {
console.log('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 += '<label class="_cs_multiple-select__form__label"><input class=\'_cs_multiple-select__form__checkbox\' type=\'checkbox\' name=\''+data[i][0]+'\' value=\''+data[i][0]+'\''+ checked +' >'
output += data[i][0]
output += '</label>'
} else if (data[i].length > 1) {
if (data[i][0] == '') continue;
// left will be used as value
// the rest is title
output += '<label class="_cs_multiple-select__form__label"><input class=\'_cs_multiple-select__form__checkbox\' 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 += '</label>'
}
}
} 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
google.script.run.withSuccessHandler(onCheck).getValidationDataCheck();
}
var onCheck = function(result) {
content = result
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()
}
function refresh() {
google.script.run.withSuccessHandler(onCheck).getValidationDataCheck();
}
</script>
<style>
._cs_multiple-select__buttons { position:fixed; top: 0; left:0; padding: 10px 0 0 10px; width: 100%; height: 40px; background-color: white; box-shadow: 0 4px 8px -3px rgb(60 64 67 / 15%); }
._cs_multiple-select__buttons__element { border: 1px solid transparent!important; border-radius: 4px; box-sizing: border-box; font-family: "Google Sans",Roboto,RobotoDraft,Helvetica,Arial,sans-serif; font-weight: 500; font-size: 14px; height: 30px; letter-spacing: 0.25px; line-height: 16px; padding: 6px 14px 8px 14px; background: white; border: 1px solid #dadce0!important; color: #188038; cursor:pointer; }
._cs_multiple-select__buttons__element:hover { background: #f8fcf9; border: 1px solid #c8e7d1!important; }
._cs_multiple-select__buttons__element--h { color: #fff; background: #2a8947; }
._cs_multiple-select__buttons__element--h:hover { color: #fff; background: #2b8a48; }
._cs_multiple-select__buttons__element--a { color: #fff; background: #1a73e8; }
._cs_multiple-select__buttons__element--a:hover { color: #fff; background: #1b66ca; }
._cs_multiple-select__form-container { font-family: sans-serif; padding-top: 50px; }
._cs_multiple-select__form__label { display: block; }
._cs_multiple-select__form__checkbox { cursor: pointer; }
</style>
</head>
<body>
<div class="_cs_multiple-select__buttons">
<input class="_cs_multiple-select__buttons__element _cs_multiple-select__buttons__element--a" type="button" value="Load" onclick="refresh()" />
<input class="_cs_multiple-select__buttons__element _cs_multiple-select__buttons__element--h" type="button" value="Set" onclick="set()" />
<!-- <input class="_cs_multiple-select__buttons__element" type="button" value="Update" onclick="update()" /> -->
<input class="_cs_multiple-select__buttons__element" type="button" value="Reset" onclick="reset()" />
</div>
<div class="_cs_multiple-select__form-container" style="">
<form id="form" name="form">
</form>
</div>
</body>
<html>
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Scripts')
.addItem('Multi-select in 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();
} catch(e) {
return null
}
}
function getValidationDataCheck(){
try {
return SpreadsheetApp.getActiveSheet().getActiveCell().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.sort((a, b) => a.localeCompare(b)).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)
}
@megaroeny
Copy link

megaroeny commented Nov 5, 2021

Do I need to uncheck V8 runtime for this? When I do, it throws an error

image

Also, not seeing anything appear in the sidebar (dialog). The three buttons are at the top, and that's it. Trying to use a list of values for the buttons. Is that possible?

image

image

@1nathanliang
Copy link

@megaroeny The script, as is, is currently only written to allow for the criteria of "List from a range." I'm currently trying to rewrite a version to fit what you're trying to do...

So, if you want to use this, you'll need to set up a range of values somewhere in your sheets file from which to draw the checkboxes.

@mary-loulou
Copy link

Hi @simon88pl,
It's been a while since you posted the code. It works brilliantly.
The only thing I would like to change is the sorting. It would be amazing if the elements were not sorted alphabetically but in the original order of the selection list.
Unfortunately, all scripts I have tested seem to sort them randomly, yours does a much nicer job, but for our purposes the original order as in the column specified in the validation range would be brilliant.
Could anyone help me adapt the script?

@simon88pl
Copy link
Author

@mary-loulou removing the sorting in line #37 should help you achieve the effect you want:

var value = selectedValues.join(separator)

But I haven't checked it now, because it's been a long time since I used the script and I don't have it used anywhere at the moment. Test it :).

@mary-loulou
Copy link

@simon88pl
Thank you! I think I tried that but I will check again.
It would be brilliant if you could add two buttons:

  • one to sort ABC
  • one to read the current cell content and preselect it.

Any chance you may be able to do that?

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