jmiguelv
11/21/2019 - 9:24 AM

Google Sheets script to allow multi-select in cells with data-validation (adapted from https://www.youtube.com/watch?v=dm4z9l26O0I)

Google Sheets script to allow multi-select in cells with data-validation (adapted from https://www.youtube.com/watch?v=dm4z9l26O0I)

dialog.html
content_copyfile_download
<!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>
multi-select.gs
content_copyfile_download
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)
}