Dynamic dependent dropdown list using google apps script is not working - Google Sheets

Question

I have written some code which is supposed to use a value in one column which shows the 'Branch', and based on that it changes the dropdown menu in 'Assigned to OE' (for context an OE is an employee), so an OE name can be selected from a dropdown list, based on which Branch they are in. The list will only show employees from the corresponding branch.

Here is a copy of my spreadsheet, with the irrelevant columns cleared: https://docs.google.com/spreadsheets/d/1dzTYQL1YPX6z6qtV4_tNl4ntBMLqcMQYoPcw6cKuyAw/edit?usp=sharing

Column A of the Sales Order sheet is where I would like the dropdown lists, in each row, and the OE names sheet is where the list of OEs (employees) will be, and the dropdown lists should be populated with these columns depending on the Branch.

The script I have written is:

function onEdit() {

var app = SpreadsheetApp;
var ss = app.openById("1UM[MASK]0M");
var OEsheet = ss.getSheetByName("OE names");
var SOsheet = ss.getSheetByName("Sales Order");
var activeCell = SOsheet.getActiveCell();

if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getSheetName() == SOsheet) {

  activeCell.offset(0, 1).clearContent().clearDataValidations();

  var OEnames = OEsheet.getRange(1, 1, 1, OEsheet.getLastColumn()).getValues();

  var OEnamesIndex = OEnames[0].indexOf(activeCell.getValue()) + 1;

  if(OEnamesIndex != 0){
 var validationRange = OEsheet.getRange(2, OEnamesIndex, OEsheet.getLastRow());
 var validationRule = app.newDataValidation().requireValueInRange(validationRange).build();

  activeCell.offset(0, -6).setDataValidation(validationRule);

}

}
}

This script does not seem to do anything, but I cannot figure out why.

Thank you

makeIndex is not defined. So the entire code inside the if(makeindex) loop won't execute. — ADW
Oh yes, I just changed it to OEnamesIndex but it still does not do anything, so I updated the code on my question. It's weird that it ran without showing that error previously. — isaaacs

ADW ADW · Accepted Answer · 2019-08-30T11:33:45

Here are some things you may want to fix:

(1) This line is pointing to a sheet other than the demo sheet. Perhaps this is the ID of your main sheet. I would suggest masking it for this post

var ss = app.openById("1UM[MASK]0M");

I would suggest using the following so making copies of the sheet won't break the code:

var ss = SpreadsheetApp.getActiveSpreadsheet();

(2) In the following line you are comparing a "sheet name" to a "sheet object".

if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getSheetName() == SOsheet) {

Instead, try:

if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getSheetName() == "Sales Order") {

(3) Based on the if statement, the column of the active cell is 1. But this line tries to offset the column by -6 columns which is impossible.

activeCell.offset(0, -6).setDataValidation(validationRule);

Perhaps you meant:

activeCell.offset(0, 1).setDataValidation(validationRule);

Dynamic dependent dropdown list using google apps script is not working - Google Sheets

1 Answers