Is it possible to have a multi select list box in Google sheet?

Is it possible to have a multi select list box on a google sheet using an app script?

I found a way that can be partially achieved by having a multi dropdown but a user need to select & wait a few seconds before selecting other one. There is also an issue where you might end up selecting multiple times same name; having a checkbox would be more efficient.

Below google sheet, POC1 is the column where I would like to have list box which would allow to select multiple names among the list of names & show in the same cell, split by delimiter.

Would be great to have something like below in POC1 column cells, but this is using excel, would like to know how can be replicated on Google Sheet as we use Gsuite at work.

Solution

Using App Script:

Here's a way to do it using apps script and custom menu

1.) Go to Extensions -> Apps Script. Rename the Code.gs to SERVER.gs. Now copy and paste the codes from below to replace everything inside

/**
 * Changes the variable validation if needed
 */

var validation = {
    sheet: 'VALIDATION',
    range: 'A2:A'
}

/**
 * Creates a menu entry in the Google Docs UI when the document is opened.
 *
 * @param {object} e The event parameter for a simple onOpen trigger. To
 *     determine which authorization mode (ScriptApp.AuthMode) the trigger is
 *     running in, inspect e.authMode.
 */
function onOpen(e) {
    SpreadsheetApp.getUi().createMenu('Sidebar')
        .addItem('Show Sidebar', 'showSidebar')
        .addToUi();
        showSidebar();
}


/**
 * Opens a sidebar in the document containing the add-on's user interface.
 */

function showSidebar() {
    SpreadsheetApp.getUi()
        .showSidebar(HtmlService.createTemplateFromFile('SIDEBAR')
            .evaluate()
            .setSandboxMode(HtmlService.SandboxMode.IFRAME)
            .setTitle('Multiple selector'));
}

function getOptions() {
    return SpreadsheetApp.getActive().getSheetByName(validation.sheet).getRange(validation.range).getDisplayValues()
        .filter(String)
        .reduce(function(a, b) {
            return a.concat(b)
        })
}

function process(arr) {
    arr.length > 0 ? SpreadsheetApp.getActiveRange().clearContent().setValue(arr.join(", ")) :
        SpreadsheetApp.getUi().alert('No options selected')
}

2.) Create a new file in the script editor by clicking the Plus(+) sign then click HTML. Name this SIDEBAR.html.

3.) Replace the content by the codes from below:

<!DOCTYPE html>
<html>
<style>
    .container,
    .buttons {
        margin: 5px;
        width: 95%;
        padding: 2px;
        font-size: 13px;
    }
</style>

<head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>

<body>
    <div class="container"></div>
    <div class="buttons">
        <p>
            <button class="action" id="action">Fill active cell</button>
            <button id="btn">Rebuild options</button>
        </p>
    </div>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
    <script src="https://cdn.rawgit.com/mdehoog/Semantic-UI/6e6d051d47b598ebab05857545f242caf2b4b48c/dist/semantic.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/semantic-ui/2.1.8/semantic.min.js"></script>
    <script>
        $(document).ready(function() {
            createList();
            var selected = [];
            $('.ui.checkbox').checkbox();
            $("#action").click(function() {
                $("input:checkbox[name=sel]:checked").each(function() {
                    selected.push($(this).val());
                    $(this).prop( "checked", false ); 
                });
                google.script.run.process(selected)
                selected.length = 0;
            });
            $("#btn").click(function() {
                createList();
            });
        });

        function options(arr) {
            $(".container").empty();
            $(arr).each(function(i, el) {
                $(".container").append('<div class="field"><div class="ui checkbox"><input type="checkbox" name="sel" value="' + el + '"><label>' + el + '</label></div></div>')
            });
        }

        function createList() {
            google.script.run.withSuccessHandler(options).getOptions()
        }
    </script>
</body>

</html>

4.) Refresh your spreadsheet you should now have the custom menu Sidebar.

5.) Add a sheet named "Validation" here is where you will put the options available to select. Start from the row 2.

6.) Click Sidebar > Show Sidebar. A side bar should open on the right with the list of options that you have added in the validation sheet.

7.) Select the cell where you want to input the selected options. You can now select multiple items from the options.

References:

https://www.youtube.com/watch?v=Gsnzgvvx2y8 , option 1
Multiple items from dropdown
Here is a sample sheet, and where you can get the apps script code: https://docs.google.com/spreadsheets/d/1cam1LNJgvWVDx_gmEVmPzuTYaITmsQWhZ7tlcHwAmCY/copy