I have a sheet that lists all cities in my countries with their respective states. This sheet will be used in a form, but of course I don't want the user to look in a list with all the existent cities. Thus I want to filter based on state.
Logging output too large. Truncating output. [[AC, Acrelândia], [AC, Assis Brasil],
[AC, Brasiléia], [AC, Bujari], [AC, Capixaba], [AC, Cruzeiro do Sul], [AC, Epitaciolândia],
[AC, Feijó], [AC, Jordão], [AC, Mâncio Lima], [AC, Manoel Urbano], [AC, Marechal Thaumaturgo],
[AC, Plácido de Castro], [AC, Porto Walter], [AC, Rio Branco], [AC, Rodrigues Alves], [AC, Santa Rosa do Purus],
[AC, Senador Guiomard], [AC, Sena Madureira], [AC, Tarauacá], [AC, Xapuri], [AC, Porto Acre],
[AL, Água Branca], [AL, Anadia], [AL, Arapiraca], [AL, Atalaia], [AL, Barra de Santo Antônio],
[AL, Barra de São Miguel], [AL, Batalha], [AL, Belém] (...)
The first element is the state, I want to filter this array in a way I would only get values with the same state. So if the selected state is AC it would result in:
[[AC, Acrelândia], [AC, Assis Brasil], [AC, Brasiléia], [AC, Bujari], [AC, Capixaba],
[AC, Cruzeiro do Sul], [AC, Epitaciolândia], [AC, Feijó], [AC, Jordão], [AC, Mâncio Lima], [AC, Manoel Urbano], [AC, Marechal Thaumaturgo],[AC, Plácido de Castro], [AC, Porto Walter],
[AC, Rio Branco], [AC, Rodrigues Alves], [AC, Santa Rosa do Purus], [AC, Senador Guiomard], [AC, Sena Madureira], [AC, Tarauacá], [AC, Xapuri], [AC, Porto Acre], [AL, Água Branca],
[AL, Anadia], [AL, Arapiraca], [AL, Atalaia], [AL, Barra de Santo Antônio],
[AL, Barra de São Miguel], [AL, Batalha]
Description
You can simply use Array.filter() to get all the cities that are in a particular state. I have simply hard coded "AC" into the filter but you could use a variable. I've mixed up the rows to show that it is in fact only getting the cities in AC.
Script
function test() {
try {
let data = [["AC", "Acrelândia"], ["AC", "Assis Brasil"], ["AC", "Brasiléia"], ["AC", "Bujari"], ["AC", "Capixaba"],
["AL", "Anadia"], ["AL", "Arapiraca"], ["AL", "Atalaia"], ["AL", "Barra de Santo Antônio"], ["AL", "Barra de São Miguel"],
["AC", "Manoel Urbano"], ["AC", "Marechal Thaumaturgo"], ["AC", "Plácido de Castro"], ["AC", "Porto Walter"],
["AC", "Rio Branco"], ["AC", "Rodrigues Alves"], ["AC", "Santa Rosa do Purus"], ["AC", "Senador Guiomard"],
["AC", "Sena Madureira"], ["AC", "Tarauacá"], ["AC", "Xapuri"], ["AC", "Porto Acre"], ["AL", "Água Branca"],
["AL", "Batalha"], ["AL", "Belém"],
["AC", "Cruzeiro do Sul"], ["AC", "Epitaciolândia"], ["AC", "Feijó"], ["AC", "Jordão"], ["AC", "Mâncio Lima"] ];
let ac = data.filter( row => row[0] === "AC" );
console.log(ac);
}
catch(err) {
console.log(err);
}
}
Console.log
7:56:24 AM Notice Execution started
7:56:26 AM Info [ [ 'AC', 'Acrelândia' ],
[ 'AC', 'Assis Brasil' ],
[ 'AC', 'Brasiléia' ],
[ 'AC', 'Bujari' ],
[ 'AC', 'Capixaba' ],
[ 'AC', 'Manoel Urbano' ],
[ 'AC', 'Marechal Thaumaturgo' ],
[ 'AC', 'Plácido de Castro' ],
[ 'AC', 'Porto Walter' ],
[ 'AC', 'Rio Branco' ],
[ 'AC', 'Rodrigues Alves' ],
[ 'AC', 'Santa Rosa do Purus' ],
[ 'AC', 'Senador Guiomard' ],
[ 'AC', 'Sena Madureira' ],
[ 'AC', 'Tarauacá' ],
[ 'AC', 'Xapuri' ],
[ 'AC', 'Porto Acre' ],
[ 'AC', 'Cruzeiro do Sul' ],
[ 'AC', 'Epitaciolândia' ],
[ 'AC', 'Feijó' ],
[ 'AC', 'Jordão' ],
[ 'AC', 'Mâncio Lima' ] ]
7:56:25 AM Notice Execution completed
Reference
function selectCitiesForState(state = "AC") {
const stoc = [[],[]];//you type in the array with quotes
Logger.log(stoc.filter(e => e[0] == state).map(r => r[1]));
return stoc.filter(e => e[0] == state).map(r => r[1]);
}