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(', '));
}

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 ???????