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

MIT App Inventor Help
, ,
1

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

2

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

3

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
4

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.

5

Dear TIMA,

I Just thinking in doing the filter like this,

image
image716×56 2.27 KB

how can I use the parameters,

let me show you some screens:

image
image500×678 59.5 KB

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

1 Like
6

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.

7

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.

8

I am not understand. please Tell me ditails,

9

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...