Skip to content

Instantly share code, notes, and snippets.

@coinsandsteeldev

coinsandsteeldev/dialog.html

Forked from arthurattwell/dialog.html
Last active February 7, 2024 11:23
Show Gist options
  • Star 92 You must be signed in to star a gist
  • Fork 31 You must be signed in to fork a gist
  • Save coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7 to your computer and use it in GitHub Desktop.
Save coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7 to your computer and use it in GitHub Desktop.
Download ZIP
Google Sheets script to allow multi-select in cells with data-validation (adapted from https://www.youtube.com/watch?v=dm4z9l26O0I)
Raw
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 += '<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] + ' &ndash; '
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>
Raw
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(){
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
Copy link

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.

@l-yusya
Copy link

l-yusya commented Feb 28, 2023

@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.
image

@facine
Copy link

facine commented Sep 19, 2023

@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. image

https://gist.github.com/arthurattwell/aa5afc178624bdd6f46c2d0d28d06136?permalink_comment_id=3805444#gistcomment-3805444

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