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)
}
@cwlind
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
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
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
Copy link

DaTigerYT commented Jul 29, 2019

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

@Caffeine-Please
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
Copy link

duguru commented Aug 4, 2019

Thanks a lot for your wonderful scripts, they work perfectly

@dave-w-morse
Copy link

dave-w-morse commented Aug 13, 2019

This is a LIFE SAVER! Thank you

@madz25
Copy link

madz25 commented Aug 26, 2019

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

@eiseauton
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
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
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
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
Copy link

luismartinezs commented Feb 14, 2020

It works handsomely! Thanks

@anglilian
Copy link

anglilian 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

@anglilian
Copy link

anglilian 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

@501commons-lucinda
Copy link

501commons-lucinda commented Sep 10, 2020

@lianlaughsalot and @coinsandsteeldev- this script had been working happily for me for a few months and only recently stopped working.

Turns out, the script above is not compatible with V8 runtime. https://developers.google.com/apps-script/guides/v8-runtime
Google updated the runtime for many scripts on the back end without much notification. I was able to get the script working for me again by using the legacy Rhino runtime.

  1. Edit the script
  2. Run -> Disable new Apps Script powered by Chrome V8
  3. Save

@VforVenique
Copy link

VforVenique commented Nov 18, 2020

@coinsandsteeldev @lestroud
Any thoughts on how to update this script so that when you navigate back to a cell that already has multiple options selected, the options that have already been selected display as blue selected checkboxes in the sidebar? This would create a more intuitive way to update the contents of a cell.

Sorry, I'm a noob and don't even know where to start... :-\

@Lartsch
Copy link

Lartsch commented Nov 24, 2020

@VforVenique
Copy link

VforVenique commented Nov 24, 2020

@Lartsch
Thanks so much, can't believe I missed that one.

One more question for you: is it possible to change the text that reads "Apps Script application" label at the top of the Scripts sidebar?

Screen Shot 2020-11-24 at 4 17 23 PM

@VforVenique
Copy link

VforVenique commented Nov 30, 2020

@coinsandsteeldev
Thanks so much for creating this awesome script!

Follow-up question: I'd like to use this script at the nonprofit I work for and we have some pretty sensitive client data in our sheets. Does this script give you or anyone else outside of our Google Suite domain access to our Google Sheets/Drive (and therefore our client data)?

The Authorization prompts to run the script make me a little nervous about data privacy/security. But I'm hoping that it's just a standard Authorization prompt for third-party apps/add-ons and that it doesn't really apply to the multi-select script you've created.
Screen Shot 2020-11-30 at 3 49 36 PM

Thanks again for all of your help!

Many Thanks,
Steve

@msmichls
Copy link

msmichls commented Feb 3, 2021

I am having trouble seeing any of the validation values in the dialog box. How can I check the output values? Also, I am not seeing any activity when I click the buttons. I am working on Google Chrome. Is there something I am missing?

Dialog Box - No Values

@VforVenique
Copy link

VforVenique commented Feb 7, 2021

@GeorgeNavarre
Copy link

GeorgeNavarre commented Feb 21, 2021

I've updated this script so that when you navigate back to a cell that already has multiple entries selected, the UI displays these options as checked so that its easier to work with large lists.

https://gist.github.com/GeorgeNavarre/fdaeb3c3b49e246b9fae8aefe9c30448

@GeorgeNavarre
Copy link

GeorgeNavarre commented Feb 21, 2021

@VforVenique
I've made a fork that does just this. If you, like me, couldn't get carlosleonam's script to function correctly you may have more luck with mine.
https://gist.github.com/GeorgeNavarre/fdaeb3c3b49e246b9fae8aefe9c30448

@noeldelgadom
Copy link

noeldelgadom commented Mar 17, 2021

You may need to disable V8 and revert back to Rhino.

On Tue, Feb 2, 2021 at 7:54 PM msmichls @.> wrote: @.* commented on this gist. ------------------------------ I am having trouble seeing any of the validation values. Also, I am not seeing any activity when I click the buttons. I am working on Google Chrome. Is there something I am missing? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://gist.github.com/4c67dfa5411e8add913273fc5a30f5e7#gistcomment-3617773, or unsubscribe https://github.com/notifications/unsubscribe-auth/ARZZQ5A6QT4UJJJUO2ZICALS5CUEZANCNFSM4HXS23VQ .

Yes, @VforVenique. That worked. I had the same issue as @msmichls and was able to solve by disabling V8, which I did by

  1. Opening the Script Editor (Tools > Script Editor).
  2. Project Settings (left menu).
  3. Uncheck 'Enable Chrome V8 runtime'

@KierstenMc
Copy link

KierstenMc commented Jul 7, 2021

You may need to disable V8 and revert back to Rhino.

On Tue, Feb 2, 2021 at 7:54 PM msmichls @.> wrote: _@**.**_* commented on this gist. ------------------------------ I am having trouble seeing any of the validation values. Also, I am not seeing any activity when I click the buttons. I am working on Google Chrome. Is there something I am missing? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://gist.github.com/4c67dfa5411e8add913273fc5a30f5e7#gistcomment-3617773, or unsubscribe https://github.com/notifications/unsubscribe-auth/ARZZQ5A6QT4UJJJUO2ZICALS5CUEZANCNFSM4HXS23VQ .

Yes, @VforVenique. That worked. I had the same issue as @msmichls and was able to solve by disabling V8, which I did by

  1. Opening the Script Editor (Tools > Script Editor).
  2. Project Settings (left menu).
  3. Uncheck 'Enable Chrome V8 runtime'

Thank you so much! that fixed my issues.

@simon88pl
Copy link

simon88pl commented Aug 20, 2021

I've made a fork with:

  • sorting values alphabetically
  • clickable options
  • a bit more pleasant look & feel

https://gist.github.com/simon88pl/609cf5ee665117fc8597a7dee4afb598

@vehgroshop
Copy link

vehgroshop commented Jan 16, 2022

Just tried this script and it does the trick nicely, thanks.
The reason I wanted something like this was because I need to be able to concatenate certain values, to be used as descriptions after further processing.

Now I get every selected value separated by a comma and a space. But what would I need to modify, to concatenate alle values like that except the last selected a value? The last selected value should be preceded by " and ". So that I get values like:
"red, white, bue and yellow".

And a second question...
What if I wanted the concatenated values to be translated?
My spreadsheet is designed to be gathering data in 3 languages. If a user selects multiple values from the dropdown, they should only be selecting them in one language. I want the data in the columns for the other two languages to be populated automatically based on the selection in the first...

When working just with single values this can easily be done using INDEX or Vertical Lookup from arrays, but the combinations with multiple select are arbitrary and I don't want to create a translation array with all possible combinations.

@vehgroshop
Copy link

vehgroshop commented Jan 17, 2022

I tried out the script a bit more and something doesn't quite work out yet.
If I select a number of items from my list in a specific order and press the "set" button in the Apps Script-app
I get the concatenated result allright, but the order is different from the order in which I selected them. (of course 25 kg should be last in line)
image

Furthermore.... if I press the "set" button once more, the order in the cell changes.
What should I do to make sure the set button will add individual values to the concatenation in the order of selection?

@GeorgeNavarre
Copy link

GeorgeNavarre commented Jan 17, 2022

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment