Read Filtered Data Querying the Google Sheet with Google App Script and AI2

[Lancio83]

Hello,

as I managed to build an App that read and update data from a Google Sheet, I'd like now to add an important step: reading not just all the rows, but just those filtered according to a parameter.

I got some ideas from this nice page: METRIC RAT AI2 - Query Any Google Sheet with a Web App

and tried to apply to my App and Google App Script already prepared, but obviously I'm doing something wrong and the result is an error.

If someone could look at my files and help me out, it would be of great help.

Here after:

• the blocks (circled the ones where there should be the error);
• the google app script;
• the "error message".

Thanks a lot

functiondoGet(e) {
returnManageSheet(e);
}
functiondoPost(e) {
returnManageSheet(e);
}

functionManageSheet(e) {

//READ ALL RECORDS

if( e.parameter.func == "ReadAll") {
  varss = SpreadsheetApp.getActive();
  varsh = ss.getSheets()[0];
  varrg = sh.getDataRange().getValues();
  varoutString = '';
    for(varrow=0 ; row<rg.length ; ++row){
      outString += rg[row].join(',') + '\n';
    }
  returnContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
}

//READ SINGLE RECORD

elseif( e.parameter.func == "ReadRecord") {
varss = SpreadsheetApp.getActive();
varsh = ss.getSheets()[0];
varrg = sh.getDataRange().getValues();
varoutString = '';
outString += rg[parseInt(e.parameter.id)].join(',');
returnContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
}

//UPDATE SINGLE RECORD

elseif( e.parameter.func == "Update") {
varss = SpreadsheetApp.getActive();
varsh = ss.getSheets()[0];
vardata = [[ e.parameter.ritirato, e.parameter.giornoritiro, e.parameter.oraritiro, e.parameter.cognomeritiro, e.parameter.nomeritiro ]];
sh.getRange("M"+(parseInt(e.parameter.id)+1)+":Q"+(parseInt(e.parameter.id)+1)).setValues(data);
returnContentService.createTextOutput("Success");
}

//QUERY RECORDS

elseif( e.parameter.func == "Query") {
varss = SpreadsheetApp.getActive();
varsh = ss.getSheets()[0];
varrg = sh.getDataRange().getValues();
varsql = e.parameter.SQL;
varqry = '=query(' + rg + ';\"' + sql + '\";1)';

varts = ss.insertSheet();
varsetQuery = ts.getRange(1,1).setFormula(qry);
vargetResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);

returnContentService.createTextOutput(JSON.stringify(getResult));
}

}

function doGet.txt (1.7 KB)

Runtime Error
The second argument to foreachisnot a list. The second argumentis: "%3C%21DOCTYPE+html%3E%3Chtml%3E%3Chead%3E%3Clink+rel%3D%22shortcut+icon%22+href%3D%22%2F%2Fssl.gstatic.com%2Fdocs%2Fscript%2Fimages%2Ffavicon.ico%22%3E%3Ctitle%3EErrore%3C%2Ftitle%3E%3Cstyle+type%3D%22text%2Fcss%22+nonce%3D%22jwOcuMnu3Gn4se8-5xwwGg%22%3Ebody+%7Bbackground-color%3A+%23fff%3B+margin%3A+0%3B+padding%3A+0%3B%7D.errorMessage+%7Bfont-family%3A+Arial%2Csans-serif%3B+font-size%3A+12pt%3B+font-weight%3A+bold%3B+line-height%3A+150%25%3B+padding-top%3A+25px%3B%7D%3C%2Fstyle%3E%3C%2Fhead%3E%3Cbody+style%3D%22margin%3A20px%22%3E%3Cdiv%3E%3Cimg+alt%3D%22Google+Apps+Script%22+src%3D%22%2F%2Fssl.gstatic.com%2Fdocs%2Fscript%2Fimages%2Flogo.png%22%3E%3C%2Fdiv%3E%3Cdiv+style%3D%22text-align%3Acenter%3Bfont-family%3Amonospace%3Bmargin%3A50px+auto+0%3Bmax-width%3A600px%22%3ELo+script+%C3%A8+stato+completato+ma+non+ha+restituito+alcun+valore.%3C%2Fdiv%3E%3C%2Fbody%3E%3C%2Fhtml%3E"
Note: Youwillnotseeanothererrorreported for 5 seconds.

Runtime Error.txt (1.0 KB)

[TIMAI2]

You do not appear to be sending a query, and what you are doing in Web4.GotText is unlikely to work?

[Lancio83]

Hello,

I tried to modify the blocks more similar to your project "queryAnyGoogleSheet.aia" (see image attached).

Now there is a new "Error massage".

Besides, I don't see where, and how, I should indicate that I'd like to extract just filtered values from column n°11...

Runtime Error.txt (144 Bytes)

[TIMAI2]

We need to see the entire responseContent to see what the error is coming back from AppsScript.

[TIMAI2]

I have just noticed, in the apps script you provided:

varss = SpreadsheetApp.getActive();
varsh = ss.getSheets()[0];
varrg = sh.getDataRange().getValues();
varsql = e.parameter.SQL;
varqry = '=query(' + rg + ';\"' + sql + '\";1)';

You have no spaces between var and the variable name, e.g. varss.

They should look like this:

var ss = SpreadsheetApp.getActive();
var sh = ss.getSheets()[0];
var rg = sh.getDataRange().getValues();
var sql = e.parameter.SQL;
var qry = '=query(' + rg + ';\"' + sql + '\";1)';

Check you entire script for these errors....

[Lancio83]

Hello, it was just a typing error in this chat, it was actually written correctly, like this:

//QUERY RECORDS

else if ( e.parameter.func == "Query") {

var ss = SpreadsheetApp.getActive();

var sh = ss.getSheets()[0];

var rg = sh.getDataRange().getValues();

var sql = e.parameter.SQL;

var qry = '=query(' + rg + ';"' + 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));

}

[Lancio83]

I tried to modify the blocks, similar to the other 3 "set.Web(n°).Url to", therefore joining "get global Url" and "&func=Query" at the beginning, but it still doesn't work, showing still the same error:

Runtime Error

The operation Elements cannot accept the arguments: , ["<!DOCTYPE"]
Note: You will not see another error reported for 5 seconds.

Any idea how to fix it?

[TIMAI2]

@TIMAI2

We need to see the entire responseContent to see what the error is coming back from AppsScript

As previously requested...

How to show raw responseContent from your app when debugging

Many developers often get stuck when trying to handle the response content from an online server after making a GET request using the web component. Those trying to help often ask to see the raw responseContent being returned. When using companion this can be copied and pasted to the community for those trying to help to use with debugging. This is how you might return that information / data to a label. BLOCKS [image] DATA Do It Result: ""Name","Date of Birth","Age" "Adah","06/04/1999","2…

You could also just add another worksheet to your google sheet, and test your query on the google sheet, using the QUERY= formula (which is what the apps script is ostensibly doing)

[Lancio83]

Hello, I tried to follow your suggestion and show the "responseContent" when Web4 is called (see picture).

The Error message is the following: "the script completed but did not return anything".

Any idea?

[TIMAI2]

@Lancio83

Any idea?

Thank you for doing that. This pointed me back to your script. There is an error.

This line:

var rg = sh.getDataRange().getValues();

should be

var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();

Sorry I did not notice this earlier.

[Lancio83]

Thanks for quick reply.

I updated the script to this:

//QUERY RECORDS

else if ( e.parameter.func == "Query") {

var ss = SpreadsheetApp.getActive();

var sh = ss.getSheets()[0];

var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();

var sql = e.parameter.SQL;

var qry = '=query(' + rg + ';"' + 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 I have re-executed the deployment and updated the new link in the app, but still same error message:

"the script completed but did not return anything".

[TIMAI2]

Is the sheet you want to query the first sheet, the first sheet from the left?

[Lancio83]

Yes it is, the first from the left (there are just two sheets).

Here after my script.

The other 3 functions work perfectly ("//READ ALL RECORDS", "//READ SINGLE RECORD", "//UPDATE SINGLE RECORD").

In the meanwhile I tried to adjust the Query, asking to Select elements from column K when column M equals a value (see blocks).

But still errors appears:

Runtime Error

          *The operation Elements cannot accept the arguments: , ["<!DOCTYPE"]*
          **Note:* You will not see another error reported for 5 seconds.*

Google Apps Script.txt (1.8 KB)

[Lancio83]

As the other topic created for the alternative method of using gviz is considered a duplicate, I post here what I've done:

I've red this nice page, and trying this alternative method: METRIC RAT AI2 - Use gviz to get and query google sheet data

I have to say, this second method looks a bit easier to me (maybe more low-code and straightforward).

I have downloaded the "gvizQueryDemo.aia" provided in the example, and modified a bit to try understanding the logic, and it works as expected.

Now I'm trying to apply the same logic to my app, but I am getting error message in return:

        *Runtime Error*
        
        *The operation Elements cannot accept the arguments: , ["{"version":"0.6","reqId":"0","status":"error","errors":[{"reason":"invalid_query","message":"INVALID_QUERY","detailed_message":"Invalid query: NO_COLUMN: Targa"}]}"]*
        **Note:* You will not see another error reported for 5 seconds.*

If I try to indicate in the query the column name by letters instead of headers, this is the error message:

  Runtime Error
  
 *The operation Elements cannot accept the arguments: , ["{"version":"0.6","reqId":"0","status":"error","errors":[{"reason":"invalid_query","message":"INVALID_QUERY","detailed_message":"Invalid query: NO_COLUMN: NO_Lancioni"}]}"]
 **Note:* You will not see another error reported for 5 seconds.

Any idea? Thanks

[Ramon]

Have you tried with single quotes?

SELECT K WHERE M = '<string_to_compare>'

Another thing to keep in mind is that it is case sensitive.

[Lancio83]

Thanks for the interest and the reply!

I think you have a point: I have now updated the blocks in order to enter the string to compare between single quotes (see image).

It does give me error, but another kind:

        *Runtime Error*
        
        *The operation Elements cannot accept the arguments: , [""Targa" "naceas" "AV624DF" "EA729LK""]*
        **Note:* You will not see another error reported for 5 seconds*

From the elements listed in the error, the first is the header of the Column to filter, the other 3 are indeed the data filtered... I don't understand why it shows an error, but I think it gets the "filtering" function, as it extract just those data...

[Ramon]

In GotText block try with this:

Then, if you don't want the first item (the column name) remove the first item from the generated list.

[Lancio83]

Hi, we are getting close, but still some trouble arising...

The filter is now working, and I manage to exclude the first item (the header), adding the block "all but first of list". Perfect!

Now the problem is the following, with the Script "ReadRecord".

Before, when in the ListView1 appeared all the records, selecting one, thanks to the Script "ReadRecord" I could see in the labels fields n° 2-3-12-13 (Web2.GotText).

Now instead, it doesn't read the selected row, but it starts from row 1 of the Database... meaning, if I pick in ListView1 the third element, the Script doesn't read its record, but always the third record of the database...

Any idea how to fix this? Thanks a lot

I'm uploading both the new blocks, and new google app script

GAS_updated.txt (1.3 KB)

[Lancio83]

Ok I think I figured out the logic of the mistake... but I don't know how to fix it!

Once created a filtered list, with "ListView1.SelectionIndex", I'm creating a new list with index starting at 1: so, even if in the original Database the element is n°50, if in the new list appears first, it has index n° 1. Therefore, the scripts "ReadRecord" and "Update", are working on index n° 1 of the Database... which is wrong.

I need to understand if it's possible to indicate to those scripts the right index of the selected element...

Any idea? Thanks!

[Ramon]

Like you have removed the first row (col header) you need to add 1 (+1) to the SelectionIndex to meet with the row you want.

The first element in the ListView has index 1, but it is the row 2 in the sheet.

EDIT: Ok, Sorry, now I understand the problem...you have a filtered list where the order is not the row number.

Then you need to use a unique value to be able to identify the row.

Other option could be to get all the rows in a list, displaying in the ListView only the filtered data, and when an element is selected, you can find its position in the whole list to do the new query.