amadeomano/Multiselect.gs

## README.md

      
    Raw
  

              README.md
            
          
    Add (hacky) Multiselection to Google Sheet

Copy the Multiselect.gs and dialog.html files to the Apps Script and refresh your sheet.
Validating multi-selection

One possible solution is to have a sheet for the possible values. E.g. Types:


Type


Tp 1


And also a table which uses these values like


Name
Types


Nm 1
Tp 1, Tp 2


Nm 2
Tp 1, Tp 3


The validation type for the Types column could be as follows:
=REDUCE(1, SPLIT(B2, ", ", FALSE), LAMBDA(acc, val, IF(acc = 0, 0, IF(ISNA(QUERY(Types!$A$2:$A, "select A where A matches '^"&val&"$'")), 0, 1))))=1

Acknowledgement

This script is heavily inspired from the other gist here which implementes the idea of the youtube video here.

  
## dialog.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <script>
  let options = undefined
  let selection = [];
  let filter = ''

  const drawForm = function() {
    const outputEl = document.getElementById('form');
    let html = options.reduce((list, itm) => {
      if (!(new RegExp(filter, 'gi')).test(itm)) return list;
      return list += `<label style="display: block;">
        <input
          type="checkbox"
          name="values"
          value="${itm}"
          onchange="handleCheck(this)"
          ${selection.includes(itm) ? 'checked' : ''} />
        <span>${itm}</span>
      </label>`;
    }, '');
    outputEl.innerHTML = html;
  }

  const onOptions = function(data) {
    options = data.map(itm => itm[0]);
    google.script.run.withSuccessHandler(onSelection).getCellValues();
  }

  const onSelection = function(data) {
    selection = data.split(', ');
    drawForm();
  }

  const handleUpdate = function() {
    const formData = new FormData(document.getElementById('form'))
    const values = [...formData.entries()].map(itm => itm[1])
    google.script.run.updateCellValue(values.join(', '));
  }

  const handleSearch = function(target) {
    filter = target.value;
    drawForm();
  }

  const handleCheck = function(target) {
    if (target.checked) selection.push(target.value);
    else selection = selection.filter(e => e != target.value)
  }

  const handleLoadCell = function() {
    const outputEl = document.getElementById('form');
    outputEl.innerHTML = `<p>Reloading Cell</p>${outputEl.innerHTML}`;
    google.script.run.withSuccessHandler(onSelection).getCellValues();
  }

  const handleLoad = function() {
    const outputEl = document.getElementById('form');
    outputEl.innerHTML = '<label>Loading data...</label>';
    google.script.run.withSuccessHandler(onOptions).getData();
  }
  google.script.run.withSuccessHandler(onOptions).getData();

  </script>
  </head>
  <body style="margin: 0">
    <div style='padding: 10px; background: white; position: sticky; top: 0'>
      <input type="button" value="Update Cell" onclick="handleUpdate()" />
      <input type="button" value="Load Cell" onclick="handleLoadCell()" />
      <input type="button" value="Reload All" onclick="handleLoad()" />
      <p><input type="text" placeholder="lookup..." onkeyup="handleSearch(this)" /></p>
    </div>
    <div style="overflow-y: auto">
      <form id="form" name="form">
        <label>Loading data...</label>
      </form>
    </div>
  </body>
<html>

## Multiselect.gs
const SOURCE = 'Components!A2:A';

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 getValidation() {
  try {
    const validation = SpreadsheetApp.getActiveSheet().getActiveCell().getDataValidation();
    return `${validation.getCriteriaValues()[0]}`.match(/.*QUERY\((.+?),/)[1];
  } catch(e) {
    return null
  }
}
function getData(){
  try {
    const src = getValidation() ?? SOURCE;
    return SpreadsheetApp.getActiveSpreadsheet().getRange(src).getValues();
  } catch(e) {
    return null
  }
}
function getCellValues(){
  try {
    return SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
  } catch(e) {
    return null
  }
}
function updateCellValue(value){
    SpreadsheetApp.getActiveSheet().getActiveCell().setValue(value);
}
	<!DOCTYPE html>
	<html>
	<head>
	<base target="_top">
	</head>
	<script>
	let options = undefined
	let selection = [];
	let filter = ''

	const drawForm = function() {
	const outputEl = document.getElementById('form');
	let html = options.reduce((list, itm) => {
	if (!(new RegExp(filter, 'gi')).test(itm)) return list;
	return list += `<label style="display: block;">
	<input
	type="checkbox"
	name="values"
	value="${itm}"
	onchange="handleCheck(this)"
	${selection.includes(itm) ? 'checked' : ''} />
	<span>${itm}</span>
	</label>`;
	}, '');
	outputEl.innerHTML = html;
	}

	const onOptions = function(data) {
	options = data.map(itm => itm[0]);
	google.script.run.withSuccessHandler(onSelection).getCellValues();
	}

	const onSelection = function(data) {
	selection = data.split(', ');
	drawForm();
	}

	const handleUpdate = function() {
	const formData = new FormData(document.getElementById('form'))
	const values = [...formData.entries()].map(itm => itm[1])
	google.script.run.updateCellValue(values.join(', '));
	}

	const handleSearch = function(target) {
	filter = target.value;
	drawForm();
	}

	const handleCheck = function(target) {
	if (target.checked) selection.push(target.value);
	else selection = selection.filter(e => e != target.value)
	}

	const handleLoadCell = function() {
	const outputEl = document.getElementById('form');
	outputEl.innerHTML = `<p>Reloading Cell</p>${outputEl.innerHTML}`;
	google.script.run.withSuccessHandler(onSelection).getCellValues();
	}

	const handleLoad = function() {
	const outputEl = document.getElementById('form');
	outputEl.innerHTML = '<label>Loading data...</label>';
	google.script.run.withSuccessHandler(onOptions).getData();
	}
	google.script.run.withSuccessHandler(onOptions).getData();

	</script>
	</head>
	<body style="margin: 0">
	<div style='padding: 10px; background: white; position: sticky; top: 0'>
	<input type="button" value="Update Cell" onclick="handleUpdate()" />
	<input type="button" value="Load Cell" onclick="handleLoadCell()" />
	<input type="button" value="Reload All" onclick="handleLoad()" />
	<p><input type="text" placeholder="lookup..." onkeyup="handleSearch(this)" /></p>
	</div>
	<div style="overflow-y: auto">
	<form id="form" name="form">
	<label>Loading data...</label>
	</form>
	</div>
	</body>
	<html>
	const SOURCE = 'Components!A2:A';

	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 getValidation() {
	try {
	const validation = SpreadsheetApp.getActiveSheet().getActiveCell().getDataValidation();
	return `${validation.getCriteriaValues()[0]}`.match(/.*QUERY\((.+?),/)[1];
	} catch(e) {
	return null
	}
	}
	function getData(){
	try {
	const src = getValidation() ?? SOURCE;
	return SpreadsheetApp.getActiveSpreadsheet().getRange(src).getValues();
	} catch(e) {
	return null
	}
	}
	function getCellValues(){
	try {
	return SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
	} catch(e) {
	return null
	}
	}
	function updateCellValue(value){
	SpreadsheetApp.getActiveSheet().getActiveCell().setValue(value);
	}