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

dialog.html

<div style="font-family: sans-serif;">
<? var data = valid(); ?>
<form id="form" name="form">
<? if(Object.prototype.toString.call(data) === '[object Array]') { ?>
<? for (var i = 0; i < data.length; i++) { ?>
<? for (var j = 0; j < data[i].length; j++) { ?>
<input type="checkbox" id="ch<?= '' + i + j ?>" name="ch<?= '' + i + j ?>" value="<?= data[i][j] ?>"><?= data[i][j] ?><br>
<? } ?>
<? } ?>
<? } else { ?>
<p>This cell has no <a href="https://support.google.com/drive/answer/139705?hl=en">Data validation</a>.</p>
<? } ?>
<input type="button" value="Select" onclick="google.script.run.fillCell(this.parentNode)" />
<input type="button" value="Refresh validation" onclick="google.script.run.showDialog()" />
</form>
</div>

multi-select.gs

function onOpen(e) {
  SpreadsheetApp.getUi()
  .createMenu('Scripts')
  .addItem('Multi-select for this cell...', 'showDialog')
  .addToUi();
}
function showDialog() {  
  var html = HtmlService.createTemplateFromFile('dialog').evaluate();
  SpreadsheetApp.getUi()
  .showSidebar(html);
}
var valid = function(){
  try{
    return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues();
  }catch(e){
    return null
  }
}
function fillCell(e){
  var s = [];
  for(var i in e){
    if(i.substr(0, 2) == 'ch') s.push(e[i]);
  }
  if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(', '));
}

Thanks for this, working like a charm ;)

Hi,

Thanks a lot.
I was wondering if we could have a 'select all' feature in it, that would be useful.

Thanks - works well. Two questions:

1. I have an error on my spreadsheet cell "Invalid:
   Input must fall within specified range" When I set up the data validation for a range as noted in step 1, what range should I use? I assumed it was a range on another sheet where my list of items is. As the script is pulling in my data, this appears right but is generating that message.

2. Is there a way to have the Apps Script Application pop up only show when needed (i.e. when someone clicks on a cell) as opposed to all the time?

It works. However, the script runs really s-l-o-w. My list only has 6 items in it. Any thoughts to speed it up?
And, I also have the "Invalid: Input..." warning on my cells. Any way to disable that?
Looking forward to suggestions :)

Also it would be nice to have a search function when the list is long

no way to disable validation alert when we select more than one item ?

Can't seem to see the menu item "Scripts", does this still exist or has it been changed in the latest version of sheets? I'm not the owner of the spreadsheet, only an editor, wonder if this makes a difference?

This was great and just what I needed, thank you!

Nice code, Arthur - terse but readable. And it works great too! A big improvement over Alexander's. Many thanks.

Hi Arthur, thank you for sharing this code!

I have forked the gist and added the feature to prevent empty choices to be shown in the sidebar => my gist link.

I am happy to delete my fork if you plan to implement this feature.

I am also thinking of ways to:

• Avoid the "invalid" warning when the user selects more than one choice.
• Speed up the script.

I'll update this thread if I make progress.

Thanks,
Guido

Works like a charm. Thank you!!

Hello, is anyone able to provide an additional script I could use to ignore the data validation errors? I receive a dv error on each cell when using Arthur's script. Thank you.

@berrythemaker I'm assuming the script NEEDS to check if a cell has data validation before running, which is why you're getting those errors. But you can try deleting lines 10, 11, and 12 in the dialog.html script, that's the part that throws up the error message "This cell has no data validation".

I'm no expert, but nobody else is answering you, so just give that a shot. If it doesn't work, you should probably revert the changes (put those lines back in).

Here is better version with fixed bar on top, reset button which reset checkboxes and ignoring empty validation cells, using multiple cells to create checkboxes' titles, validation errors workaround and Set/Update cell feature.
https://gist.github.com/coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7

Note that since the user above has changed their username since their last post, their link above will not work. It should be accessible using their new user name at:
https://gist.github.com/coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7

Hey mate, many thanks. You have saved a lot's of time to me ;)

Hi there, when I try to save the file dialog.html, it gives me an error message: syntax error (line 10, "dialog.html")

Line 10 states this: if (!data) return

Is this no longer valid ?
Thank you otherwise, this would be really saving me loads of time.

Does this still work?

Note that since the user above has changed their username since their last post, their link above will not work. It should be accessible using their new user name at:
https://gist.github.com/coinsandsteeldev/4c67dfa5411e8add913273fc5a30f5e7

Thanks, fixed my comment!

This still works for me — but to get the 'Scripts' menu to appear I had to do Run -> Run function -> onOpen() in the Script Editor. Then the menu showed up. I had to click around a bit to figure out how it works but it wasn't that hard!

Presumably I could also have closed and reopened the sheet?

It does work well, but I would like to make the whole thing that appears on the right disappear, once the selection is made... I can make the HTML form disappear, I can make the div part of HTML disappear, but I don't know how to make the whole thing disappear, meaning the frame on the right that has the title: "Apps Script application". I can manually click on the "x" and close it, but I would like to do it programmatically...

Does anybody know how to do it?

hello,

thanks for your code

I've followed your instruction and used your script.... with no result !! After options checked, I "Select" but no data is going to cell,

any suggestion

Thanks

From the menu, select Data->Data Validation and then On Invalid Data select Show warning. Then click save

Thank you! Thank you! Thank you! All worked perfectly and did exactly what I wanted (Multi-select!). However, I did have to go into an Incognito Window to make the script editor work and to be able to do multi-select. When I left the incognito window and went into my normal window, it doesn't work! Any thoughts on this?

Everything works UNTIL I click on "select" and nothing happens :-( I am so grateful for this solution, I need to make this work... I would really appreciate any help you can give!

Hello ! Thanks a lot for your code and time. Any idea to improve filters based on multi-criteria selection?

Everything works UNTIL I click on "select" and nothing happens :-( I am so grateful for this solution, I need to make this work... I would really appreciate any help you can give!

I'm having this same problem. Select just does nothing when it's suppose to set the values.

I started a code using this method about a month ago and it worked then. Logged back into the spreadsheet today and now when I click Select nothing happens :(. Was there an update? Anyone have any other coding solutions to achieve this?

Thank you!

Edit: Resolved by:

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

Everything works UNTIL I click on "select" and nothing happens :-( I am so grateful for this solution, I need to make this work... I would really appreciate any help you can give!

I'm having this same problem. Select just does nothing when it's suppose to set the values.

@joshuajohnsont and @MeesLorch. I found this resolution on a different page and it fixed the issue for me!

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

Hope that helps! :)

Is it possible to modify this script to give the user a option of entering free Text ???????