-
-
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) | |
} |
@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 🤘
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.`
`
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.