'Creating down menu with multiple selection capabilties

I've created a dropdown menu via data validation with a list of items to select from. Traditionally, you're only able to select one item at a time, but I'm curious if there's a way to select more than one item per row. I've attempted to use the below code in the Apps Script -

function onEdit(e) {
    var oldValue;
    var newValue;
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var activeCell = ss.getActiveCell();
    if (activeCell.getColumn() == 14 && activeCell.getLastRow && ss.getActiveSheet().getName() == "Accommodations") {
    newValue = e.value;
    oldValue = e.oldValue;
    if (!e.value) {
      activeCell.setValue("");
    }
    else {
      if (!e.oldValue) {
        activeCell.setValue(newValue);
      }
      else {
        activeCell.setValue(oldValue + ', ' + newValue);
      }
    }
  }
}

However, no matter how many times I save it and refresh the sheet, it doesn't allow me to select multiple options. Here you can review the spreadsheet I'm working with And here you can review all of the different items available in one of the columns

google-apps-script

Solution 1:^[1]

Description

If you want to get fancy, here is a way to create a multi select list from dropdown using the checkbox character. This shows some uses of Named Range, Data Validation, Image Script and an installed onEdit trigger.

I create a Named Range called Items. The first item of Items is simply a default Select Item that is always displayed.

Next I create a Data Validation in cell B1 that will display the drop down list of Items.

Then I create an installed onEdit trigger called multiSelect that will toggle the checkbox character.

Finally I create an Over Cell Image button in cell C1 that I can click to display the selected items and attach the script checkSelect

Script

function multiSelect(e) {
  try {
    if( e.range.getSheet().getName() === "MultiSelect" ) {  // only if on sheet MultiSelect
      if( e.range.getA1Notation() === "B1" ) {  // only if in cell B1
        if( e.value === "Select Item" ) return;
        let ranges = e.range.getSheet().getNamedRanges();
        let items = ranges.find( range => range.getName() === "Items" );
        if( items ) {
          let values = items.getRange().getValues();
          let index = values.findIndex( item => item[0].slice(1) === e.value.slice(1) );
          let item = values[index][0];
          if( item.charAt(0) === '?' ) {
            item = item.replace('?','?');
          }
          else {
            item = item.replace('?','?');
          }
          items.getRange().getCell(index+1,1).setValue(item);
          e.range.setValue("Select Item");
        }
        else {
          SpreadsheetApp.getUi().alert("Oops what happend to Items");
        }
      }
    }
  }
  catch(err) {
    console.log("Error in multiSelect - "+err);
  }
}

function checkSelect() {
  try {
    let sheet = SpreadsheetApp.getActiveSheet();
    let ranges = sheet.getNamedRanges();
    let items = ranges.find( range => range.getName() === "Items" );
    if( items ) {
      let values = items.getRange().getValues();
      let picks = "";
      values.forEach( item => { if( item[0].charAt(0) === '?') picks = picks.concat(item[0].slice(1),',') } );
      SpreadsheetApp.getUi().alert(" You picked "+picks);
    }
    else {
      SpreadsheetApp.getUi().alert("Oops what happend to Items");
    }
  }
  catch(err) {
    console.log("Error in checkSelect - "+err);
  }
}

Reference

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	TheWizEd

'Creating down menu with multiple selection capabilties

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]