-
-
Save coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7 to your computer and use it in GitHub Desktop.
| <!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> |
| 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) | |
| } |
Just tried this script and it does the trick nicely, thanks.
The reason I wanted something like this was because I need to be able to concatenate certain values, to be used as descriptions after further processing.
Now I get every selected value separated by a comma and a space. But what would I need to modify, to concatenate alle values like that except the last selected a value? The last selected value should be preceded by " and ". So that I get values like:
"red, white, bue and yellow".
And a second question...
What if I wanted the concatenated values to be translated?
My spreadsheet is designed to be gathering data in 3 languages. If a user selects multiple values from the dropdown, they should only be selecting them in one language. I want the data in the columns for the other two languages to be populated automatically based on the selection in the first...
When working just with single values this can easily be done using INDEX or Vertical Lookup from arrays, but the combinations with multiple select are arbitrary and I don't want to create a translation array with all possible combinations.
I tried out the script a bit more and something doesn't quite work out yet.
If I select a number of items from my list in a specific order and press the "set" button in the Apps Script-app
I get the concatenated result allright, but the order is different from the order in which I selected them. (of course 25 kg should be last in line)
Furthermore.... if I press the "set" button once more, the order in the cell changes.
What should I do to make sure the set button will add individual values to the concatenation in the order of selection?
For those experiencing that the validation options don't render with Chrome V8 runtime, try and remove .getValues() from line 14 in the multi-select.gs file.
I still get a warning for cells with multiple values, is there a way to make the data validation accept cells containing a list?
This is awesome, thanks!
Apologies if this can be found elsewhere, but do you know if there Is there any way to change it so that the user would experience the validation choices in the menu as refreshing automatically upon moving to a cell selection with a different list range, rather than having to click the "refresh validation" to do it?
I've updated this script so that when you navigate back to a cell that already has multiple entries selected, the UI displays these options as checked so that its easier to work with large lists.
https://gist.github.com/GeorgeNavarre/fdaeb3c3b49e246b9fae8aefe9c30448
Reguarding the V8 runtime issue, I came up with this sollution. as @brew-guy suggested, getValues() is the troublemaker here. However, simply removing it does not format the list correctly. Here's some code to go inside the try in the getValidationData() function.
`
// return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues();
const arr = SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0];
const newArr = [];
while(arr.length) newArr.push(arr.splice(0,1));
return newArr;
`
The logic the html file implements requires an array of an array (one element only) of strings. This code above (and I assume getValues() formally as well) transforms the 1D array to a 2D array, without changing the 1D shape (basically a flat 2D array).
Hope this helps anyone still trying to use this script.
@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.
@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 🤘
I've made a fork with:
https://gist.github.com/simon88pl/609cf5ee665117fc8597a7dee4afb598