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.

You can't run these functions without the trigger which supplies the event object which is placed inside of the functions parameter which is usually chosen to be e. — Cooper
Thanks for the quick reply @Cooper. I was under the impression that the onEdit() function is triggered every time the user makes an edit to the spreadsheet. I have ran this script and have chosen values for several cells in the spreadsheet. However, it's still not allowing me to select multiple options. — twells

Cooper Cooper · Accepted Answer · 2021-01-24T00:13:58

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.

Data Validation with Multi-Select Dropdown in Google Sheets

1 Answers