Data from google sheet, display per row

[rhem21]

thank you so much sir. i will read and study this.. big thanks sir..

[rhem21]

good day sir, i tried to use the html output but its not working like in tutorial..

[TIMAI2]

You will need to send the query as a URL in a web viewer.

[rhem21]

yes sir, but it is not what i wanted so i did not use this anymore.. im back to my problem where the result is delayed, where i need to search twice. any work around here sir? im thinking of circular or linear progress as delay but i get java error when dragging it to my project

[TIMAI2]

Sorry, I was on my phone, and didn't read your blocks properly.

Not sure why the html table is not displaying in your webviewer?

Let me have another look at your spreadsheet...

[TIMAI2]

Try with range A17:H188

This is where your data is, gviz will run a query on all that data and return your results as a table.

Your query should look like this for 'Rem':

SELECT A,E,G,H WHERE A contains 'Rem'

(the single quotes before and after your textbox.text are important)

full url:

https://docs.google.com/spreadsheets/d/1sZiR5MY91Uwkvez0h77Y32CgzwBcdg5FTzF4K7OHYKQ/gviz/tq?tqx=out:html&sheet=TO&range=A17:H188&tq=SELECT%20A,E,G,H%20WHERE%20A%20contains%20%27Rem%27

Output:

For 'Greg':

https://docs.google.com/spreadsheets/d/1sZiR5MY91Uwkvez0h77Y32CgzwBcdg5FTzF4K7OHYKQ/gviz/tq?tqx=out:html&sheet=TO&range=A17:H188&tq=SELECT%20A,E,G,H%20WHERE%20A%20contains%20%27Greg%27

[rhem21]

sorry sir, im kinda lost right now..

i shortcut the url and took out the variables. the search box dont send query to my form and no means to display i think.

[rhem21]

this is my ideal result sir.. hopping for your guidance so that i can have an output like this..

[rhem21]

i progress a little bit sir, i think my only problem is the html display..

[rhem21]

i tried to chenge html to csv and its working fine..

[rhem21]

how can i add or on query result sir ? i need my query to show where A and B contains name search

[TIMAI2]

Not sure why are not able to display the html output ?

I have prepared a simple demo project, based upon your spreadsheet:

demoGviz.aia (3.7 KB)

This will upcase the first letter typed in the textbox (e.g. rem > Rem)
I have edited the query to test for the search text in columns A&B
Button HTML OUTPUT will show the results in the webviewer
Button CSV OUTPUT will show the results in the label

The search term joy shows the query working on A&B.

BLOCKS

[ADDENDUM #1]

If you like to view the data as an html table (from gviz) but also need to return all the table values as data, you can run some javascript on the webpage after it has loaded:

js as text:

let oTable = document.getElementsByTagName('table')[0];
let data = [...oTable.rows].map(t => [...t.children].map(u => u.innerText))
window.AppInventor.setWebViewString(JSON.stringify(data));

This returns a stringified JSON array, which you can convert to an AI2 list using the Web component's JsonTextDecode block

Note that all data is treated as strings...

You could also run some javascript to beautify your table: colours, fonts, corners, column widths, overflow etc.

[rhem21]

you're a life saver sir.. i cant scrutinize it atm, but ill make sure to study it later. another thing sir, how can you turn the query condition into a part of a link code?it will help me in my other projects sir.. thank you so much

[rhem21]

sir can you please enlighten me in few things..

where do you get this extra blocks ? in my project i dont have this call query and upcase blocks..
how does webviewer work and let it display result without calling the web1.get?

im looking for the declaration of the variable value, but i dont see it. how did you declare the textbox value to that variable?

hoping to understand this things sir so i can integrate your work to mine. thank you sir

[ABG]

See Ch. 21 of
http://www.appinventor.org/book2
for procedure parameters.

[rhem21]

thank you sir, will read and study this.

[TIMAI2]

If you look at my demoGviz project you will see this in the procedures palette

You have to create your own procedures like this using these blocks, then the socket or method procedure becomes available in the palette

ABG has pointed you to reference for this.

As you can see the textbox.text is inside the upCaseFirst procedure. If you do not want to use upcase, then just put the textbox.text block to the query value socket, like this:

[rhem21]

sir, i have copied your blocks but still not getting result when i search..
CparAppvII.aia (120.6 KB)

[rhem21]

the visible checkbox was unchecked sorry sir... now its working very fine.. thank you so much! and @sir ABG