CODEWITHSUNDEEP
validationgoogle-apps-scriptgoogle-sheets
twells
twells

Reputation: 1

Data Validation with Multi-Select Dropdown in Google Sheets

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.

Upvotes: 0

Views: 6036

Answers (2)

user19427307
user19427307

Reputation: 1

An easy, yet admittedly ungraceful way around the lack of multiple selection ability is to insert a note for the cell and put your content in that. After, you can cut the content you want to use from note and paste it in your cell. Editing your content is easier as well because it obviates the need for syntax (e.g. ensuringthere are commas between options)

Upvotes: 0

Cooper
Cooper

Reputation: 64072

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.

enter image description here

Upvotes: 1

Related Questions