-
-
Save coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7 to your computer and use it in GitHub Desktop.
Google Sheets script to allow multi-select in cells with data-validation (adapted from https://www.youtube.com/watch?v=dm4z9l26O0I)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <script> | |
| var data | |
| var formId = 'form' | |
| 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; | |
| output += '<input type=\'checkbox\' name=\''+data[i][0]+'\' value=\''+data[i][0]+'\'>' | |
| 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] + ' – ' | |
| 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 | |
| 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> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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(); | |
| } 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) | |
| } |
@devinhelgeson even with this addition the items still don't render for me. Could I have missed something? I've tried all the versions of the script mentioned here, but none of them worked.
Trying to allow for multiple reoccurences.
For context - I'm using this to track products I send out to customers (sometimes multiple of the same product), so wondered if there was a way to delete the code that dissallows double entries without removing functionality. Any help would be appreciated! @coinsandsteeldev
Hi @Joshben88. Here's a take on adding multiple of the same values. There's a little plus button next to each value that allows you to add more of each:
multi-select-gs:
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() {
const cell = SpreadsheetApp.getActive().getActiveRange();
const rule = cell.getDataValidation();
if (rule != null) {
var criteria = rule.getCriteriaType();
var args = rule.getCriteriaValues();
if (criteria == 'VALUE_IN_LIST') {
return args[0].map(el => [el]);
} else if (criteria == 'VALUE_IN_RANGE') {
let values = args[0].getValues();
if (values.length > 1) {
return values;
} else {
return values[0].map(el => [el]);
}
}
} else {
return null
}
}
function setValues_(selectedValues, update) {
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);
range.getCell(row + 1, column + 1).setValue(currentValues.concat(selectedValues).join(separator));
}
}
} else {
range.setValue(value);
}
}
}
function updateCell(selectedValues) {
return setValues_(selectedValues, true)
}
function fillCell(selectedValues) {
setValues_(selectedValues, false)
}
dialog.html:
<!DOCTYPE html>
<html>
<head>
<script>
var data
var formId = 'form'
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;
output += '<div>'
output += '<input type="checkbox" name="' + data[i][0] + '" value="' + data[i][0] + '">'
output += data[i][0]
output += ' <button type="button" onclick="addValue(\'' + data[i][0] + '\')">+</button>'
output += ' <span id="count_' + data[i][0] + '"></span>'
output += '</div>'
} else if (data[i].length > 1) {
if (data[i][0] == '') continue;
output += '<div>'
output += '<input type="checkbox" name="' + data[i][0] + '" value="' + data[i][0] + '">'
output += data[i][0] + ' – '
for (var j = 1; j < data[i].length; j++) {
if (data[i][j] == '') continue
output += data[i][j] + '; '
}
output += ' <button type="button" onclick="addValue(\'' + data[i][0] + '\')">+</button>'
output += ' <span id="count_' + data[i][0] + '"></span>'
output += '</div>'
}
}
} 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
drawForm()
}
google.script.run.withSuccessHandler(onData).getValidationData();
function getSelectedValues() {
var form = document.getElementById(formId);
var checkboxes = form.querySelectorAll('input[type="checkbox"]:checked');
var selectedValues = [];
checkboxes.forEach(function (checkbox) {
var count = parseInt(document.getElementById('count_' + checkbox.value).textContent) || 1;
for (var i = 0; i < count; i++) {
selectedValues.push(checkbox.value);
}
});
return selectedValues;
}
function set() {
google.script.run.withSuccessHandler(x => {
}).fillCell(getSelectedValues())
}
function update() {
google.script.run.withSuccessHandler(x => {
}).updateCell(getSelectedValues())
}
function reset() {
drawForm()
}
function addValue(value) {
var countSpan = document.getElementById('count_' + value);
var count = parseInt(countSpan.textContent) || 0;
countSpan.textContent = count + 1;
}
</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>
Thanks @brew-guy, awesome work - exactly what I was hoping for!
All credit due to Claude I showed it the existing code pieces and told it what we needed and its first shot is what you got 🤘
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
@devinhelgeson even with this addition the items still don't render for me.
Could I have missed something? I've tried all the versions of the script mentioned here, but none of them worked.