So this solution could be optimized and cleaned out adding some functions to not repeat the code. But as a working starting point you could have.
function onEdit(e) {
var ss = SpreadsheetApp.getActive();
// Get the working Sheets
var gameplan = ss.getSheetByName("gameplan");
var vehicle = ss.getSheetByName("vehicle");
var players = ss.getSheetByName("players");
// Get current selected vehicles
var _selectedVehicles = gameplan.getRange(2, 2, gameplan.getLastRow() - 1).getValues();
var selectedVehicles = [];
for(var i = 0; i < _selectedVehicles.length; i++){
selectedVehicles.push(_selectedVehicles[i][0])
}
// Get current selected players
var _selectedPlayers = gameplan.getRange(2, 1, gameplan.getLastRow() - 1).getValues();
var selectedPlayers = [];
for(var i = 0; i < _selectedPlayers.length; i++){
selectedPlayers.push(_selectedPlayers[i][0])
}
// Get active and unselected Vehicles
var activeVehicles = [];
var vehicleValues = vehicle.getRange(2, 1, vehicle.getLastRow() - 1, 2).getValues();
for(var i =0; i < vehicleValues.length; i++){
if(vehicleValues[i][0] == "ACTIVE" && selectedVehicles.indexOf(vehicleValues[i][1]) == -1){
activeVehicles.push(vehicleValues[i][1])
}
}
// Get active and unselected players
var activePlayers = [];
var playerValues = players.getRange(2, 1, players.getLastRow() - 1, 2).getValues();
for(var i =0; i < playerValues.length; i++){
if(playerValues[i][0] == "ACTIVE" && selectedPlayers.indexOf(playerValues[i][1]) == -1){
activePlayers.push(playerValues[i][1])
}
}
// Insert data validation for vehicles
var rule = SpreadsheetApp.newDataValidation().requireValueInList(activeVehicles, true).build();
gameplan.getRange("B2:B").setDataValidation(rule);
// Insert data validation for Players
var rule = SpreadsheetApp.newDataValidation().requireValueInList(activePlayers, true).build();
gameplan.getRange("A2:A").setDataValidation(rule);
}
Explanation
Is not clear from your post if you have used Apps Script before. So just to make sure you can understand what is happening (in case you want to modify it) I will go over my code explaining all the steps and methods I have used.
Basically the code has 4 parts.
- Getting the working sheets
// Get the working Sheets
var gameplan = ss.getSheetByName("gameplan");
var vehicle = ss.getSheetByName("vehicle");
var players = ss.getSheetByName("players");
Here is just basically invoking the getSheetByName() on the spreadsheet.
- Get the already selected values
// Get current selected vehicles
var _selectedVehicles = gameplan.getRange(2, 2, gameplan.getLastRow() - 1).getValues();
var selectedVehicles = [];
for(var i = 0; i < _selectedVehicles.length; i++){
selectedVehicles.push(_selectedVehicles[i][0])
}
// Get current selected players
var _selectedPlayers = gameplan.getRange(2, 1, gameplan.getLastRow() - 1).getValues();
var selectedPlayers = [];
for(var i = 0; i < _selectedPlayers.length; i++){
selectedPlayers.push(_selectedPlayers[i][0])
}
So here is just repeating the same code two times.
Inside every sheet calling the getRange method. Then because the return type is Object[][] we need to iterate through and get every individual value and push it to the selectedVehicles (or players) array.
- Get the active and unselected Vehicles
// Get active and unselected Vehicles
var activeVehicles = [];
var vehicleValues = vehicle.getRange(2, 1, vehicle.getLastRow() - 1, 2).getValues();
for(var i =0; i < vehicleValues.length; i++){
if(vehicleValues[i][0] == "ACTIVE" && selectedVehicles.indexOf(vehicleValues[i][1]) == -1){
activeVehicles.push(vehicleValues[i][1])
}
}
So in here is the same getRange method to get the values, but in this case we are retrieving the two columns, the ACTIVE/INACTIVE and the id.
After that we iterate through the array making sure that that row has the ACTIVE status and that the id is not in the previous selected array. Look at the indexOf. And we store all the values that match those condtitions in a new array.
- Finally we impose the new data validation
// Insert data validation for vehicles
var rule = SpreadsheetApp.newDataValidation().requireValueInList(activeVehicles, true).build();
gameplan.getRange("B2:B").setDataValidation(rule);
For this last bit you need to use is the newDataValidation() method that creates a DataValidationBuilder objects. In that object you have a a lot of methods to make data behave how you want, one being requireValueInList to have a dropdown with your desired values. In the case the array that we have created in the previous step. After using build we can add this new rule to a range with sedDataValidation.
