How do you use a filter with App inventor 2 and google spreadsheet?

Hi every body,

is there someone who knows which is the script to make a fiter with App script google sheet , the one for App inventor

and how to use them ?

I guess it is something like this code

else if ( e.parameter.FN == "ReadRecord" ) {
var ref = sh.getRange(parseInt(e.parameter.ROWID)+1,1,1,rg[0].length).getValues();
return ContentService.createTextOutput(JSON.stringify(ref)).setMimeType(ContentService.MimeType.JSON);
}

but how to know where I need to put the word that I only wants to be shown

Yours faithfully

instead of script why dont you try with gviz method of filtering/querying the data?

Already @TIMAI2 added multiple quides regarding how to work with spreadsheet in community and his website too

There are many different ways to do this, depending on how you want to work.

You could simply ReadAll the data, then apply select parameters to the output list, generating a new list of your filtered results.

If your spreadsheet is "anyone with the link" permissions then you can directly read/query/filter data on the sheet, if your spreadsheet is "restricted" then you will need to use google apps script web app to help you.

As @Spicy_Topics has indicated, you can use the gviz query to return a specific set of results - this is probably the easiest way.

You could also get google sheets to do the work, by setting up a second/third sheet on your spreadsheet, and develop formulas that will run queries or filters based upon your parameters, and return the output.

Given that you are already working with a google apps script web app, you might want to add a query function to your script:

// outputs results for SQL query of all data  
  else if ( fn == 'QuerySheet' ) {
    var sql = e.parameter.SQL;
    var hdr = e.parameter.HDR;
    if ( hdr == 0 ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation().replace('A1','A2');
    var qry = '=query(' + rgq + ';\"' + sql + '\";0)';
    } else if ( hdr == 1 ) {
    var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
    var qry = '=query(' + rgq + ';\"' + sql + '\";1)';
    }
    var ts = ss.insertSheet();
    var setQuery = ts.getRange(1,1).setFormula(qry);
    var getResult = ts.getDataRange().getValues();
    ss.deleteSheet(ts); 
    return ContentService.createTextOutput(JSON.stringify(getResult));
  }

then you could setup your blocks something like this:

image

2 Likes

One's a gain TIMA thanks for your fast answer an the support , I just think in do it with a ReadAll and in the parameter put the word that contains the only calu that I want to be show in the list view.

actually I am working to make an update selecting the info with specific values in a list view.

Dear TIMA,

I Just thinking in doing the filter like this,

how can I use the parameters,

let me show you some screens:

Actually the app make the foctional requirement but to make it more practise I would be super to programm this foction.

1 Like

Dear arturo thanks. I have need to this filtering option like this for my apps.
I already created google sheet data view apps using appinventor, but i can't doing create drop down filtering options for google sheet data view in my apps. it possible or not. please help anyone. Thank you.

You either keep querying the google sheet with your "filter" / query parameter, or you could do it locally, perhaps with a regex or by traversing the list doing a match/contains etc.

I am not understand. please Tell me ditails,

Examples and guides for open and restricted google sheets are available in posts 2 & 3 above, using the gviz query language.

To find matching items in a list, see here (you want Item 6, search for an item):
https://imagnity.com/tutorials/app-inventor/list-blocks-on-app-inventor/

Also...