So, I have a spreadsheet with sheets labeled Database, Billing, Patients,
and options that uses the script below to create multi row dependent
dropdown lists. Column E and J are both dropdown lists and the options
listed in column J depend on what is selected in column E. This script
works great.
But, I also need it to move an entire row to the sheet labeled Billing when
that option is selected in column E. Every time I try to add to this script
the dropdowns quit working.
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database");
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("options");
var options = wsOptions.getRange(2,1,wsOptions.getLastRow()-1,2).getValues();
function myFunction() {
var list = ["a","b","c","f"];
var cell = ws.getRange("J2");
applyValidationToCell(list,cell);
}
function onEdit(event){
var activeCell = event.range;
var value = activeCell.getValue();
var row = activeCell.getRow();
var column = activeCell.getColumn();
var wsName = activeCell.getSheet().getName();
if(wsName == "Database" && column === 5 && row > 1){
if(value === ""){
ws.getRange(row,10).clearContent();
ws.getRange(row,10).clearValidations();
} else{
ws.getRange(row,10).clearContent();
var filteredOptions = options.filter(function(options){ return options[0]
=== value });
var listToApply = filteredOptions.map(function(options){ return
options[1] });
Logger.log(listToApply);
var cell = ws.getRange(row,10);
applyValidationToCell(listToApply,cell);
}
}
}
function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule);
}
--
You received this message because you are subscribed to the Google Groups
"Google Spreadsheets API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/google-spreadsheets-api/6e869812-49ae-4380-a6fa-7d972a3f616bn%40googlegroups.com.
