CODEWITHSUNDEEP
javascriptfunctiongoogle-sheets
David Quintanilla
David Quintanilla

Reputation: 15

Creating a dynamic dropdown list in a google form using Javascript

I am working on building out a script for a Google Spreadsheets that creates a dynamic dropdown list. This is written in JavaScript but this is one of the first ones I have worked and need help with some of the logic.

The code works but now what I want is to have Column B and Column C be dependent on the category selected in Column A.

The script below has three levels: Main Category, Sub-group (which is dependent on the main category), and a sub-subgroup (that is dependent on the sub-group).

So in my case I am tagging video content for multiple attributes. So the main category might be "Audio" and I want to tag it for two items that are in the same subgroup.

Basically how can I make column B and C only be dependent on the selection from Column A using the script below?

I am pretty sure I need to adjust something here: aColumn + 1);

    function depDrop_(range, sourceRange){
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
range.setDataValidation(rule);
}
function onEdit (){
var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
var aColumn = aCell.getColumn();
if (aColumn == 1 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}
else if (aColumn == 2 && SpreadsheetApp.getActiveSheet()){
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}
}

Upvotes: 0

Views: 752

Answers (1)

Alan Wells
Alan Wells

Reputation: 31310

Here is some code that might help. I haven't tested it, but take a look at what I've done; figure out the logic and the improvements in the code; run it and see what happens. If you get errors or unexpected results that you can't figure out, let me know.

function onEdit(e) { //Use the event - e
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //get spreadsheet - ss
  var sh = ss.getActiveSheet(); //get sheet - sh

  var cellRng = e.range; //Range of cell that was edited

  var rowEdited = cellRng.getRow();
  var columnEdited = cellRng.getColumn();
  var range;  //define a variable for a range, and leave it undefined

  if (columnEdited === 1) {//If column one was edited, change the data validation in the next column 
    //Run the function to update the validation twice, once for column B, once for column C
    range = sh.getRange(rowEdited, columnEdited + 1); //Update Column B
    depDrop_(range, cellRng);

    range = sh.getRange(rowEdited, columnEdited + 2); //Update column C
    depDrop_(range, cellRng);
  };

  //If column 1 was not edited, then do nothing
};

Upvotes: 0

Related Questions