How can I implement several conditional drop-down data validations on Google Script?

Question

I'm trying to implement conditional drop-down lists in the sheet you see below using Google Script (if you have any other ideas, though, feel free to share). I based most of my code on this tutorial. The idea is to update the possible list values on the third column ("Indicador"), based on the value selected on the second column of each corresponding row.

The correspondence table used to determine what list of values should be used can be found in Figure 2 (another worksheet). Note that the values that are searched for are on the first column and possibly returned lists on the last column of the table displayed in figure 2 (it's, in essence, a VLOOKUP).

Figure 1 - Sheet where drop-down will be added

So far, the code looks like this:

function getSpecVars() {

var ws_inds = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Indicadores");
var ws_support = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Apoio");
var range_valores = ws_support.getRange("A5:A9").getValues();
var range_listas = ws_support.getRange("G5:G9").getValues();

Logger.log(range_listas);

}

// Updates the value on a cell in the third column corresponding to the updated value in the same row in another column.

function onEdit(e) {
 var active_cell = e.range;
 var val = active_cell.getValue();
 var row_alter = active_cell.getRow();
 var col_alter = active_cell.getColumn();
 var row_list = range_listas.indexOf(val);
 var list = range_listas[row_list];
 var alt_cell = ws_ind.getRange(row_alter,3)

 if(col_alter === 2){
   applyValidation(list,alt_cell);
 }
}

function applyValidation(list, cell) {

  var rule = SpreadsheetApp
              .newDataValidation()
              .requireValueInList(list)
              .setAllowInvalid(false)
              .build();        
  cell.setDataValidation(rule);
}

Notice that:

The first function saves the lists from the support sheet (see Figure 2, below) in arrays for later use;
The second function actually implements the Data Validation, drawing from the variables stored in the first function and the third function.
The third function is a generic function for setting a Data Validation list in any given cell.

Figure 2 - Support sheet with lists to be added to drop-down options

What I expected to see:

I expected the cells on the third column of the first sheet (Figure 1, above) to only allow input from a drop-down list filtered/chosen according to the name of the category on the second column. In other words, I expect the OnEdit script to implement a Data Validation list on the third column whenever I modify a value on the second column.

What I am getting:

Nothing, really. It does nothing. Any ideas?

Take a look at this: sites.google.com/view/googlappsscript/hot-off-the-presses/… — Cooper
We're not here to design or debug software for you. We're here to answer specific questions. You haven't asked a specific question. Welcome to StackOverFlow please take this opportunity to take the tour and learn how to How to Ask, format code and minimal reproducible example. Google Apps Script Documentation. — Cooper
Thank you, this guidance is perfect, both for the code and website usage. — Bernardo Menescal Ferreira da

dared dared · Accepted Answer · 2020-03-27T01:09:27

function get_range_listas() {
  return SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName("Apoio")
    .getRange("A5:A9")
    .getValues() // <- getValues returns a 2d Array
    .map(function (r) {
        return r[0];
    }); // <- unwrap to Array
}

function get_range_valores() {
  return SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName("Apoio")
    .getRange("G5:G9")
    .getValues() // <- getValues returns a 2d Array
    .map(function (r) {
        return r[0];
    }); // <- unwrap to Array
}

/*
 * Updates the value on a cell in the third column corresponding to
 * the updated value in the same row in another column.
 */

function onEdit(e) {

  if (
    e.source.getActiveSheet().getName() !== "Indicadores" ||
    e.range.columnStart !== 2
  ) { return; }

  var active_cell = e.range;
  var val = e.value;
  var row_list = get_range_listas().indexOf(val);
  var list = get_range_valores()[row_list].split(",");

  var rule = SpreadsheetApp.newDataValidation()
    .setAllowInvalid(false)
    .requireValueInList(list, true)
    .build();

  e.range.offset(0, 1).setDataValidation(rule);
}

How can I implement several conditional drop-down data validations on Google Script?

What I expected to see:

What I am getting:

1 Answers