Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Sheets script to allow multi-select in cells with data-validation (adapted from https://www.youtube.com/watch?v=dm4z9l26O0I)
<!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>
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)
}
@coinsandsteeldev

This comment has been minimized.

Copy link
Owner Author

coinsandsteeldev commented May 16, 2018

Selection support several rows - very first (left) row is an actual value which will be used in the cell, the rest will create checkbox label.

@coinsandsteeldev

This comment has been minimized.

Copy link
Owner Author

coinsandsteeldev commented May 16, 2018

Possible workaround with validation errors - provide validation range for dialog from special cell which contains validation data. If someone wants this approach to be implemented - just select special cell which is validated, then run the script. Opened form you can you anywhere you want. Select other cell with validation data and click refresh validation to update form.

@coinsandsteeldev

This comment has been minimized.

Copy link
Owner Author

coinsandsteeldev commented May 16, 2018

Set button will override whole cell with new value
Update button will update cell and will remove duplicated values

@legreco

This comment has been minimized.

Copy link

legreco commented May 18, 2018

works great! thanks

@ducminhn

This comment has been minimized.

Copy link

ducminhn commented Jul 9, 2018

This is awesome. Thanks, @coinandsteeldev

@ElieNamias

This comment has been minimized.

Copy link

ElieNamias commented Nov 8, 2018

Great script! Thanks

@nachtigal

This comment has been minimized.

Copy link

nachtigal commented Jun 12, 2019

how do you set the new values for this script? I edit the first cell in the doc, but the values still don't show up.

@coinsandsteeldev

This comment has been minimized.

Copy link
Owner Author

coinsandsteeldev commented Jun 12, 2019

Hi! Here is good instruction for setting up the script https://stackoverflow.com/a/53310932/1223007

@cwlind

This comment has been minimized.

Copy link

cwlind commented Jun 13, 2019

Works great! Adding data validation to multiple cells and adding additional columns of input options works great:
Click on cell. Refresh Validation. Make selections.
Click on other cell. Refresh Validation. Make selections.
Click on other cell...

Thanks!

@Syed-Aleem

This comment has been minimized.

Copy link

Syed-Aleem commented Jul 15, 2019

Hi everyone,
I was recently working with this script for multi-cell selection and it worked perfectly fine- till few days back until i saw a random change which effected my data validation.
What happened actually is, once i made a project for multi-selection, i also have had used reject input option(from data validation) which limited my end users to just select options from the drop-down and bounded them with no other option to be typed in validated cells.
Can anyone please recommend a solution to this or any workaround available for this would be highly appreciated.
Best.
Syed

@MagTun

This comment has been minimized.

Copy link

MagTun commented Jul 17, 2019

Some idea to improve this script:

● we need a way to recognize cells with data validation. We can't keep the arrow from Google Sheet (since it points to the wrong dropmenu limited to single selection, and will delete the entries added via the script). So it's better to hide the data validation arrow, but then you don't know where the cells with the data validation are.

● each time you want to edit a cell with different data validation, you must on refresh data validation. I would be better if the list is updated automatically, according to the list linked to the selected cell

@DaTigerYT

This comment has been minimized.

Copy link

DaTigerYT commented Jul 29, 2019

There seems to be no values in the sidebar when I select a cell.
image

@Caffeine-Please

This comment has been minimized.

Copy link

Caffeine-Please commented Jul 30, 2019

@DaTigerYT I believe I have setup that screen above as you have but I cannot get the Side Panel to show me a Multi-select (showing options and checkboxes) after Saving it. Is there another step to do that? If you know. Thanks

@duguru

This comment has been minimized.

Copy link

duguru commented Aug 4, 2019

Thanks a lot for your wonderful scripts, they work perfectly

@dave-w-morse

This comment has been minimized.

Copy link

dave-w-morse commented Aug 13, 2019

This is a LIFE SAVER! Thank you

@madz25

This comment has been minimized.

Copy link

madz25 commented Aug 26, 2019

Been doing some workarounds on google sheets, this solution made my day.

@eiseauton

This comment has been minimized.

Copy link

eiseauton commented Sep 3, 2019

Hi Everyone! I've tried this script and the forks: they're wonderful. I've tried it into a worksheet with two sheets: the first where I run the script in column C which contains my friends' surname and name, the second which collects the same range of values in column A. I need to improve the script in order to search and match for each of the multiple values selected in the first sheet (friend's names) by the box, with the values associated and correspondant in the same line in column B of the second sheet (email addresses). I think I need another array. I've found some code here with the function search and match: https://stackoverflow.com/questions/42044903/compare-two-columns-and-if-match-paste-matching-value, i've explained better here: https://gist.github.com/paulorenanmelo/f656800e7b97e3f4489bec733b52dd94 .... but i'm not able to adapt it to this script! Can anyone improve this script? As it is It just works perfectly, but if somebody can help me It will work better.

@eiseauton

This comment has been minimized.

Copy link

eiseauton commented Sep 3, 2019

In addition to my last commentation, I've also found this script in order to search and match: https://stackoverflow.com/questions/41968207/google-apps-script-performing-index-match-function-between-two-separate-google (similar to a VLookup function).

@tushar-anand

This comment has been minimized.

Copy link

tushar-anand commented Sep 5, 2019

Better if we add margin-top style in line number 70 in the html file for the form with id='form'.
style="margin-top: 30px;"

Adding this would display the form elements below the button.
As of now, the buttons are overlapping the form elements.

@Manda-FFA

This comment has been minimized.

Copy link

Manda-FFA commented Oct 22, 2019

If I'm reading through this correctly, then the only way for the script to pick up the List of options from Data Validation is if it's reading from a designated cell range and can't be a List of Items, in the Criteria section of Data Validation?

DaTigerYT posted an image that that shows the Criteria being set as a List of items instead of List from range -- and that's why the script isn't working as intended, right? because the Criteria needs to be "List from range"

can the script also allow for multiple selections if pulling options from this List of items?

@luismartinezs

This comment has been minimized.

Copy link

luismartinezs commented Feb 14, 2020

It works handsomely! Thanks

@lianlaughsalot

This comment has been minimized.

Copy link

lianlaughsalot commented May 12, 2020

@DaTigerYT I believe I have setup that screen above as you have but I cannot get the Side Panel to show me a Multi-select (showing options and checkboxes) after Saving it. Is there another step to do that? If you know. Thanks

I also have a similar issue :( wondering if any of you manage to find a solution yet!

edit: typo

@lianlaughsalot

This comment has been minimized.

Copy link

lianlaughsalot commented May 12, 2020

If I'm reading through this correctly, then the only way for the script to pick up the List of options from Data Validation is if it's reading from a designated cell range and can't be a List of Items, in the Criteria section of Data Validation?

DaTigerYT posted an image that that shows the Criteria being set as a List of items instead of List from range -- and that's why the script isn't working as intended, right? because the Criteria needs to be "List from range"

can the script also allow for multiple selections if pulling options from this List of items?

Tried shifting to "list from range" still didn't work. Having the same problem with @DaTigerYT

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.