Google Sheets - Data Validation - Multi-Select apps script - Error Message

user_18775865 0
Viewed 755

I am just learning Apps Scripts, I don't have a lot of experience writing code. Google Sheets in-built data validation feature allows one to choose a "single" option from a dropdown list, however I need my Google Sheet to allow for choosing multiple options for a cell from a list of options.

After browsing the Internet, I found an Apps Script for the job and it works, yay! However, when multiple items are selected, an error message appears in the cell:

Invalid: Input must be an item on the specified list

Below is the script I have been using.

function onEdit(e) {
  var oldValue;
  var newValue;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeCell = ss.getActiveCell();
  if (activeCell.getColumn() == 6 && activeCell.getLastRow && ss.getActiveSheet().getName() == "Template") {
    newValue = e.value;
    oldValue = e.oldValue;
    if (!e.value) {
      activeCell.setValue("");
    }
    else {
      if (!e.oldValue) {
        activeCell.setValue(newValue);
      }
      else {
        activeCell.setValue(oldValue + ', ' + newValue);
      }
    }
  }
}
google-apps-script google-sheets multi-select validation
1 Answers

Tried your code I think there's a slight issue with this code, you can select the same item multiple times. And also you won't be able to remove the warning.

SUGGESTION / Alternate solution

Here's another way to do it using apps script and custom menu, without an error or warning:

1.) Go to Extensions > Apps Script... You should have this enter image description here 2.) Rename the Code.gs to SERVER.gs and delete current content. Now paste here the codes from below

/**
 * 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')
}

3.) Create a new file in the script editor by clicking the Plus(+) sign then click HTML.

4.) Delete the content and paste here 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>

5.) Refresh your spreadsheet you should now have the custom menu Sidebar. enter image description here

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

enter image description here

7.) Go back to your main sheet, select the cell where you want to input the options. 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.

enter image description here

8.) You can now select multiple items from the options.

Let me know if this works!

Reference: https://www.youtube.com/watch?v=Gsnzgvvx2y8 , option 1

user_184002720
Related