coinsandsteeldev · September 8, 2024 11:18 · OnodOfTheNorth · Feb 21, 2021 · OnodOfTheNorth · Feb 21, 2021
diff --git a/dialog.html b/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>
diff --git a/multi-select.gs b/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)
 }
	<!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)
	}