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

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

Would be great to have something like below in POC1 column cells, but this is using

CodePudding user response：

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 ></div>
    <div >
        <p>
            <button  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 ><div ><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

CodePudding user response：

Assuming your data is in a named zone 'items'

var items = SpreadsheetApp.getActiveSpreadsheet().getRange('items').getValues().flat()

By script as follows

function inputMultipleCheckBoxesSidebar() {
  var page = `<!DOCTYPE html><html><head></head><body><form><table>`
  items.forEach(c => page  = `<tr><td><input type="checkbox" name="xxx" value="${c}"></td><td>${c}</td></tr>`)
  page  = `</table></form><br>
<input type="button" value="Submit"  onclick="form_data()" >
<input type="button" value="Reset" onclick="document.forms[0].reset()" />
  <script>
      function form_data(){
        var checkedValues = []; 
        var inputElements = document.getElementsByName('xxx');
        for(var i=0; inputElements[i];   i){
          if(inputElements[i].checked){
            checkedValues.push(inputElements[i].value);
          }
        }
        google.script.run.withSuccessHandler().getCheckedValues(checkedValues);
      };
</script></body></html>`
  var html = HtmlService.createHtmlOutput(page).setTitle("Your title ...")
  SpreadsheetApp.getUi().showSidebar(html);
}

then get back the values as an array

function getCheckedValues(valeur) {
  Browser.msgBox(valeur)
  // do something
}