Auto filter with selection list

AppSheet AppSheet Q&A
, ,
1

Good evening! I’m building an application based on a spreadsheet I have, and I’m having trouble making a selection with a smart filter, where I select an option and the next field gives me only the options related to that previously chosen option, in google spreadsheets I managed to do with the formula below, but in the appsheet I’m not able to do it, can you help me? I’ll send you the spreadsheet link so you can take a look.

WORKSHEET LINK: https://docs.google.com/spreadsheets/d/10UKrYPRb8Q6dnRoqaNxgGc8p–vYGPmLgYtlBktjlY0/edit?usp=sharing

SCRIPT CODE USED:

/** @OnlyCurrentDoc */

function onEdit(e) {
var GuiaAtiva = SpreadsheetApp.getActive().getSheetName();

var Celula = e.range;

var Coluna = Celula.getColumn();

if (Coluna == 3){
var Guia = Celula.getSheet();

var Linha = Celula.getRow();

if(Linha > 1){
var Fase = Celula.getValue();

var Atividade = Guia.getRange(Linha, Coluna+1);

Atividade.setValue(“”);

if(Fase==“”){
Atividade.clearDataValidations();
}else{
var Regra = SpreadsheetApp.newDataValidation();

var Atividades = PegarAtividades(Fase);

Regra.requireValueInList(Atividades, true);

Regra.setAllowInvalid(false);

Regra.setHelpText("Selecione apenas ATIVIDADES desta FASE: "+Fase);

Atividade.clearDataValidations();

Atividade.setDataValidation(Regra);
}

}
}

}

function PegarAtividades(Fase){
var Guia = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“LANÇAMENTO DE ATIVIDADES”);

var Atividades = Guia.getRange(2, 14, Guia.getLastRow()-1,2).getValues();

var Filtro = ;

for(var i=0; i<Atividades.length; i++){
if(Atividades[i][0]==Fase){
Filtro.push(Atividades[i][1]);
}
}

return Filtro;

}

2

Hi @PLANNER_H3

No code needed with AppSheet :wink:

Here is what you are looking for:

https://help.appsheet.com/en/articles/961554-dependent-dropdown

More generally:

https://help.appsheet.com/en/?q=dependent+dropdown

1 Like
3

Definitely no code needed in AppSheet.

Also in case anyone is interested you can create dependent dropdowns in Google Sheets without code as well. You would use a combination of queries, data validation and named ranges. I once built an order form on this basis before I had access to AppSheet and where I needed to avoid script because that won’t work on a mobile device.

2 Likes