Read data from Google sheets

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

Suggested reading:
Data Normalization

thinh · October 8, 2021, 6:30am

I followed you but can't look up the data, please help

TIMAI2 · October 8, 2021, 8:13am

Are you certain your sheet is called sheet1 and not Sheet1 (uppercase S) ?
You don't show a query you have tested with
Is your script correctly deployed ?

TIMAI2 · October 8, 2021, 8:19am

Also see:

thinh · October 9, 2021, 2:20am

I tried but still can't run

thinh · October 9, 2021, 2:21am

Please check the code for me, and sheet and Sheet (in uppercase and lowercase) I have corrected but still not working

thinh · October 9, 2021, 2:23am

Please check the code for me, and sheet and Sheet (in uppercase and lowercase) I have corrected but still not working

TIMAI2 · October 9, 2021, 9:41am

It appears that your sheet is called Sheet but you are sending sheet1 in the blocks

nishyanthkumar · October 10, 2021, 7:41am

Try Sheet, with a capital "S."

thinh · October 14, 2021, 1:02am

I make google sheet data query, i tried my best. however, it still does not work. Can anyone help me see if there is an error, the code, or do I need to install any additional google sheets add-ons?