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)
}
@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