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

[Lancio83]

No, I don't think I explained my self.

The Database may have 100 rows, and I may have filtered just the n° 78 and n° 91 (for example).

Now, in the list created, these have index n° 1 and 2.

If I run the script "ReadRecord", selecting the second, it will read record n° 2 of the Database, while I'm actually expecting to read record n° 91...

[TIMAI2]

You would need to create an index in your records, then you can use that value as your ID parameter in the update part of the script.

[Lancio83]

Clever... I'll try it!

[TIMAI2]

See here, under "SHEET":

METRIC RAT AI2 - CRUD with Google Sheets, Web App and AI2

[Lancio83]

Thanks, I used your array formula and it's working... so now there is an ID column that updates automatically at every new record... Now need to figure out how to use it in the "ReadRecord" and "Update"....

[TIMAI2]

Note that with the arrayFormula, the indexes will change if you delete a record. That is, if you deleted record 3, then record 4 would become record 3.

You have to be careful and not put anything into the id field, otherwise it will break the formula.

If you are adding a new record (appendRow) just put "" for the first item, given your index is the first column. Do the same when updating.

[Lancio83]

Thanks.

Actually I put the ID at the end of the record (record is composed by 17 columns, and ID is in column n°18).

[Lancio83]

Could you now suggest me the right way to modify the "ListView1.AfterPicking" block, in order to select the new ID number? Thanks again

[TIMAI2]

I tend to avoid using ListviewElements as list to work with, so something like this ?

[Lancio83]

It gives me back this error:

*Runtime Error*

*Select list item: Attempt to get item number 18 of a list of length 1: ["LS8173ND"]*
**Note:* You will not see another error reported for 5 seconds.*

[TIMAI2]

Ah, I assumed you were returning the entire record, not just a single item from it. You will always need to return the 18th column value, as well as the data you want. (This is why if the index column is the first one (A) it will always be index 1 in your returned list.)

[Lancio83]

I need to return some specific items from the record... but I don't know which blocks to change, and how...

I guess I need to modify blocks in "Web2.GotText"? But how?

[Lancio83]

I've tried the new blocks here after in the block "when Web2.GotText", but the error message is always the same:

    *Runtime Error*
    
    *Select list item: Attempt to get item number 18 of a list of length 1: ["aqxkvw"]*
    **Note:* You will not see another error reported for 5 seconds.*

Looks like I can't recreate a list with all columns... it's remaining the list with just one column

[ABG]

You only ask for column K in your SQL Select for Web4, so that's all you got.

If you are going to look for item 18 in the returned list, you had better ask for Select A,B,C,D,E,F,G,H,I,J,K,L,M,N from...

[TIMAI2]

Some example blocks you could follow:

[Lancio83]

Thanks a lot, project completed!

I signed as the solution the last post, but I need to thank all of the great insights and suggestions provided here by @TIMAI2 , @Ramon and @ABG .

Here after I share the final block, which works as I wished.

Thank again!