Dependant Dropdowns

The Code

Code.gs

function onOpen() {

menu();

}

function menu() {

SpreadsheetApp.getUi().createMenu('My Tools')

.addItem('Show Districts Dialog','showDistrictsDialog' )

.addToUi();

}

function doGet() {

return HtmlService.createTemplateFromFile('districts').evaluate();

}

function getStates() {

var states=String(getGlobal('States')).split('~~~');

var sObj={vA:states,id:'selst'};

return sObj;

}

function getCounties(state) {

var state=state || 'Colorado';

var ss=SpreadsheetApp.openById(getGlobal('DataSSID'));

var sh=ss.getSheetByName('USCities');

var rg=sh.getDataRange();

var vA=rg.getValues();

var counties=[];

for(var i=0;i<vA.length;i++) {

if(vA[i][2]==state && counties.indexOf(vA[i][3])==-1) {

counties.push(vA[i][3]);

}

var sObj={vA:counties.sort(),id:'selco'};

return sObj;

}

function getCities(state,county) {

var ss=SpreadsheetApp.openById(getGlobal('DataSSID'));

var sh=ss.getSheetByName('USCities');

var rg=sh.getDataRange();

var vA=rg.getValues();

var cities=[];

for(var i=0;i<vA.length;i++) {

if(vA[i][2]==state && vA[i][3]==county && cities.indexOf(vA[i][4])==-1) {

cities.push(vA[i][4]);

}

var sObj={vA:cities.sort(),id:'selci'};

return sObj;

}

function include(filename) {

return HtmlService.createHtmlOutputFromFile(filename).getContent();

}

function showDistrictsDialog() {

var userInterface=HtmlService.createTemplateFromFile('districts').evaluate();

SpreadsheetApp.getUi().showModelessDialog(userInterface,'US Cities, Counties and States')

}

districts.html

<!DOCTYPE html>

<html>

<head>

<?!= include('res') ?>

<?!= include('css') ?>

</head>

<body>

<h1>Select State, County and City</h1>

</select>State

</select>County

</select>City

<div id="msg"><h2>Data Available from:</h2><p><a href="https://github.com/grammakov/USA-cities-and-states" target="_blank">https://github.com/grammakov/USA-cities-and-states</a></p></div>

<?!= include('script') ?>

</body>

</html>

global.gs

function getGlobals(){

var ss=SpreadsheetApp.getActive();

var sh=ss.getSheetByName('Globals');

var rg=sh.getRange(1,1,sh.getLastRow(),2);

var vA=rg.getValues();

var g={};

for(var i=0;i<vA.length;i++){

g[vA[i][0]]=vA[i][1];

}

return g;

}

function setGlobals(dfltObj){

var dfltH=Object.keys(dfltObj).length;

if(dfltObj){

var ss=SpreadsheetApp.getActive();

var sh=ss.getSheetByName('Globals');

var rg=sh.getRange(1,1,dfltH,2);

var vA=rg.getValues();

for(var i=0;i<dfltH;i++){

vA[i][1]=dfltObj[vA[i][0]];

}

rg.setValues(vA);

}

function getGlobal(name){

return getGlobals()[name];

}

function setGlobal(name,value){

var curObj=getGlobals();

if(!curObj.hasOwnProperty(name)) {

var ss=SpreadsheetApp.getActive();

var sh=ss.getSheetByName('Globals');

sh.appendRow([name,value])

}else{

curObj[name]=value;

setGlobals(curObj);

}

function cleanGlobals() {

var ss=SpreadsheetApp.getActive();

var sh=ss.getSheetByName('Globals');

if(sh.getLastColumn()>2) {

sh.getRange(1,3,sh.getLastRow(),sh.getLastColumn()-2).clearContent();

}

var rg=sh.getRange(1,1,sh.getLastRow(),2);

var vA=rg.getValues();

for(var i=0;i<vA.length;i++) {

if(!vA[i][0] || !vA[i][1]) {

var userInterface=HtmlService.createHtmlOutput('Globals Sheet Requires Maintenance...Do it know.' + ' Check Row ' + Number(i + 1));

SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Global Maintenance Required');

break;

}

dummy.html

<!DOCTYPE html>

<html>

<head>

$(function(){

google.script.host.close();

});

</script>

</head>

<body>

</body>

</html>

res.html

css.html

<style>

body {background-color:#ffffff;}

input,select{padding:2px 5px 0 0;margin:2px 5px 0 0;}

</style>

script.html

$(function(){

$('#selst').css('background-color','#ffff00');

google.script.run

.withSuccessHandler(function(sObj){

console.log(sObj.vA);

updateSelectList(sObj);

})

.getStates();

});

function updateSelectList(sObj){

var select = document.getElementById(sObj.id);

select.options.length=1;

if(sObj.vA){

for(var i=0;i<sObj.vA.length;i++)

{

select.options[i + 1] = new Option(sObj.vA[i],sObj.vA[i]);

}

$('#' + sObj.id).css('background-color','#ffffff');

}

function selectState() {

$('#selco').css('background-color','#ffff00');

var state=$('#selst').val();

google.script.run

.withSuccessHandler(function(sObj){

updateSelectList(sObj);

})

.getCounties(state);

}

function selectCounty() {

$('#selci').css('background-color','#ffff00');

var county=$('#selco').val();

var state=$('#selst').val();

google.script.run

.withSuccessHandler(function(sObj) {

updateSelectList(sObj);

})

.getCities(state,county);

}

function selectCity() {

//do nothing for now

}

console.log('My Code');

</script>

Spreadsheet Data

It will take a considerable amount of time for the US Cities page to display since there are over 63K rows. This data is also available from https://github.com/grammakov/USA-cities-and-states

Copy of US Cities,Counties and States Dropdowns

Google Sites

Report abuse