Data Validation: to control what a user can enter into a cell
It’s hard to set-up dependent data validation using just formulas. Prasant has a nice tutorial on how to do this with formulas.
This is a quick tutorial on how to do this using Google Apps Script.
This tutorial is in response to this question on StackOverflow. The sheet needs to validate data based on countries. If the user keys in a country name, the adjacent cell should provide a drop-down list of the cities from that country as listed in the country-city table.
You could set this up to run when triggered by a button or a menu. For this tutorial, I have set it up to run every time a cell is edited. This way the sheet is more responsive. (Ref docs for onEdit() are here)
// Runs automatically when the user edits the sheet
function onEdit(e) {Get the new value in the edited cell along with its row and column:
// Get the new value entered into the edited cell
var value = e.value;// Get the column number of the edited cell
var col = e.range.getColumn();// Get the row number of the edited cell
var row = e.range.getRow();
Check that the cell edited was in the “Country” column of the table. All other edits should be ignored.
// Make sure that the edited cell is part of the table
if (col == 2 && row >= 14 && row <= 20) {Get the data from the country-city table in B5:F8. Let’s call it the Input Table. This is what you need to create the drop-down lists.
// Get the sheet that has the table with the list of cities
var sheet = SpreadsheetApp.getActiveSheet();// Get the table with the list of cities
var dropdownData = sheet.getRange(“B5:F8”).getValues();
Match the edited cell value to the names of the countries in the Input Table.
// The top row is the list of countries
var listOfCountries = dropdownData[0];// Find the column in which the country name appears
var countryColumn = listOfCountries.indexOf(value);
Now add the names of all the cities in that country to an array. (If the script can’t find a match for the country, it will simply skip the rest of the process).
// Create an array to hold the names of the cities for the drop-down
var cityList = [];
// Loop through the Input Table and add the city names to the array
for (var dataRow = 1; dataRow < dropdownData.length; dataRow++) {
cityList.push(dropdownData[dataRow][countryColumn]);
}
Get the cell where the dropdown validation needs to be set — adjacent to the edited cell with the country name:
var cityCell = sheet.getRange(row, col + 1);Do a little housekeeping. Remove any old data-validation. Clear the cell
cityCell
.clearDataValidations() // Remove any existing data validation
.clearContent(); // Clear the cellBuild a new data validation rule with the list of cities (the array created above).
var rule = SpreadsheetApp.newDataValidation().requireValueInList(cityList, true).build();The “true” in this line is to tell the sheet to show the dropdown list. Ref doc here.
And then set the rule to the target cell.
cityCell.setDataValidation(rule);And you are set.
Make a copy to look at the script here: https://docs.google.com/spreadsheets/d/16N-_gBKprgkyKikRxWzFc_LGKCQCQ9tuajY9gU8So18/
Ask me
Is there something you want to do with Google Apps Script? Tell me and I’ll write a tutorial on it:
