Skip to content

Instantly share code, notes, and snippets.

@coinsandsteeldev

coinsandsteeldev/dialog.html

Forked from arthurattwell/dialog.html
Last active September 8, 2024 11:18
Show Gist options
  • 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)
}
@brew-guy
Copy link

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] + ' &ndash; '
            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>

@Joshben88
Copy link

Thanks @brew-guy, awesome work - exactly what I was hoping for!

@brew-guy
Copy link

brew-guy commented Jul 19, 2024 •
edited
Loading

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