Data Validation with Multi-Select Dropdown in Google Sheets

Question

I have very little experience with Google Scripts, however I needed to create a Google Sheet that used data validation for restricting the content entered in specific columns via a "multi-select" dropdown list. Google Sheets has an in-built data validation feature that 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 restricted list of options.

After browsing the Internet, I found a Google Script for the job (Link HERE for website) however, when I applied this script to my spreadsheet I get the following error message.

TypeError: Cannot read property 'value' of undefined onEdit @ Code.gs:7

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() == 7  && ss.getActiveSheet().getName()=="Daniel") {
newValue=e.value;
oldValue=e.oldValue;
if(!e.value) {
activeCell.setValue("");
}
else {
if (!e.oldValue) {
activeCell.setValue(newValue);
}
else {
if(oldValue.indexOf(newValue) <0) {
activeCell.setValue(oldValue+'|'+newValue);
}
else {
activeCell.setValue(oldValue);
}
}
}
}
}

I would love to hear thoughts on possible issues with the script.

question from:https://stackoverflow.com/questions/65865765/data-validation-with-multi-select-dropdown-in-google-sheets

Answer 1

Try it this way:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (e.range.columnStart == 7 && sh.getName() == "Daniel") {
    if (!e.value) {
      e.range.setValue("");
    }
    else {
      if (!e.oldValue) {
        e.range.setValue(e.value);
      }
      else {
        if (e.oldValue.indexOf(e.value) < 0) {
          e.range.setValue(e.oldValue + '|' + e.value);
        }
        else {
          e.range.setValue(e.oldValue);
        }
      }
    }
  }
}

Remember you cannot just run functions like this from a menu or from the script editor. If you're not doing anything that requires permissions then you can run it with a simple trigger. If you're doing operations that require permission then you must pick a name other than onEdit and use an installable trigger.

Try putting this one on a blank Sheet1 and setup column 7 to have a drop down. On changes to column 7 you will be able to see the values in the event object show up in cell A1. And you'll also see when the function is working because it displays a toast in the sheet.

function onEdit(e) {
  e.source.toast('entry');
  const sh = e.range.getSheet();
  sh.getRange(1,1).setValue(JSON.stringify(e));
  if (e.range.columnStart == 7 && sh.getName() == "Sheet1") {
    if (!e.value) {
      e.range.setValue("");
    }
    else {
      if (!e.oldValue) {
        e.range.setValue(e.value);
      }
      else {
        if (e.oldValue.indexOf(e.value) < 0) {
          e.range.setValue(e.oldValue + '|' + e.value);
        }
        else {
          e.range.setValue(e.oldValue);
        }
      }
    }
  }
}

Here's a small animation of what it's doing now.