Read data from Google sheets

Damian_K · August 7, 2020, 10:20am

Hi,
I have table in Google Sheets, as below.

And, i want have all data from table to one od the people just like the picture below.

When i arregens blocks as shown on the picture below, all selected labels shows only one last items from the rów in the table.

Is there a way tor the app to her all informations for a given person?

TIMAI2 · August 7, 2020, 10:52am

Here is an example of how to do this ( I used a csv file instead of a google sheet download, but the principle is the same):

listOut.aia (4.4 KB)

Damian_K · August 7, 2020, 11:40am

Perfectly!
But, what i need to change in bloks to make IT works with Google Sheets?
I try change but its bot worked on sheets

Damian_K · August 7, 2020, 11:49am

I don't know if i'm doing well

TIMAI2 · August 7, 2020, 12:16pm

You do not need to call the Web1.get again in the after Selecting event (red cross)
Where is the set Web1.Url block which sets the url for the google sheet ? (red arrow)

Damian_K · August 7, 2020, 1:26pm

In blocks editor i don't create web1.url blocks. I added url link in properties web1 in designer window

Damian_K · August 7, 2020, 1:57pm

I solve it.
Sorry for the last posts.
I frogot to make the sheets protection visible to anyone with the link.
Now everything works:
Thank a lot for the hint and answers

And have a nice day!

Patryk_F · August 8, 2020, 11:49am

I don't want to start a new topic. I only have a question, is it possible to read data from a sheet that is not publicly available? I would have to add login to google drive? Is it good idea?

TIMAI2 · August 8, 2020, 12:19pm

Query Any Google Sheet with a Web App

Post Data to a Google Sheet

Get or Download a Private Image from Google Drive

I do have another project on the go at the moment that will provide a user access to their own google drive.....

Damian_K · August 9, 2020, 10:51am

One more question about the topic came to my mind.
is there any way to delete or change entries from within the application?
for example, I see incorrect or redundant information in the downloaded data that I would like to delete or correct this.

TIMAI2 · August 9, 2020, 11:00am

What you are talking about is a full CRUD (create/read/update/delete) for your spreadsheet.

A couple of methods:

How to use a Google Drive Google Spreadsheet as Database

CRUD with Google Sheets, Web App and AI2

How you do it will depend if your spreadsheet is public or private

Damian_K · August 9, 2020, 11:14am

Ok.
I'll check it.
Thanks again

Patryk_F · August 10, 2020, 6:52pm

I have theoretical question. I want to store lists of different lengths in individual lines of the sheet, one line will have, for example, 5 data (empty fields always at the end of the line) and the other line will have 10 data. Then I will download them to the application and convert csv to lists. Will the lists be of different lengths, one list of 5 items and the other of 10 items? Or maybe both lists will have 10 items, except that the next one will have 5 empty items? I am asking because I know that the csv table also contains empty cells.

TIMAI2 · August 10, 2020, 7:12pm

I believe you will get a downloaded csv with each line having the number of entries for the longest entry in the sheet:

a,b,c,d,e,f,g
a,b,c,,,,,

ABG · August 10, 2020, 7:12pm

Consider adding a new sheet to your design, with one row for each additional list item, identified by the key of its owner.

See the Order Item sheet in this Pizza Store sample, built for a (hopefully) upcoming Google Sheets component ...

Having the list items in a separate sheet, lined up in the same column, allows you to do some SQL-like grouping and summation.

Patryk_F · August 10, 2020, 7:20pm

Exactly. So the second list will have empty items at the end? Too bad there is no block for removing empty list items .

TIMAI2 · August 10, 2020, 7:22pm

But then you would have an improper csv/listofLists

If you attempted to iterate over a list of lists with different lengths, AI2 would complain....

ABG · August 10, 2020, 7:31pm