I'd like to achieve the following scenario, I have a game and the setup process needs a line up connected to 2 databases, based on availability.
1. players
2. Vehicles
The lineup includes a "builder" meaning each cell has a drop down list connected to the database and shows available objects.
My goal
I need to auto-update a dropdown list as soon as I submit or select one object from the list, so the next cell won't have that object any more once I click the dropdown, also if I delete an object from the lineup this object has to go back to the dropdown list since its available again.
What have I done?
I did some formulas on the database side so the dropdown list can be created from a column that auto-updates once I assign an object to the list from the dropdown itself.
column 1: includes all available vehicles/players
column 2: includes all assigned vehicles/players
column 3: generates the dropdown list subtracting column1 from column2 using a formula =SORT(FILTER(E2:E,F2:F=""),1,true)
The Issue
while this method "works" I have some issues with the way the dropdown auto-updates If I add all available equipment and then delete and item so it could be available for another cell/slot the dropdown list won't auto-update correctly, if I delete "unit 4" the dropdown list will show me another object available "unit 8".
What can I do to solve my problem?
I am adding the link for the spreadsheet so is easier to understand my issue.
Thank you guys.
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);
}
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.
// 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 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 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.
// 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.