Is this the query syntax page?
https://support.google.com/docs/answer/3093343?hl=en
or
https://developers.google.com/chart/interactive/docs/querylanguage
?
I'm having trouble trying to get to the equivalent of an SQL UPDATE WHERE statement.
The only update operation available relies on knowing the row and column number of what you want to update.
We get (now) the row number of a freshly inserted row, but how do we get to that from another context, like updating the delivery status of a pizza order 20 minutes after initial order creation?
Databases like Oracle have a ROWID pseudocolumn you can SELECT, but not Google Sheets.
This may require a double step ADD operation, where column A is reserved for a copy of the row number of the newly ADDed row, and is updated in place immediately after the ADD returns the row number.
(I looked for an Index function in the query function language, that might return a row number by cell value, to no avail.)
Even this workaround requires us to never delete a row, for fear of breaking all following row number references.
On further thought, maybe here's a workaround, a long forgotten SQL trick:
Use an ascending datetimestamp as a primary key, and derive the row number of a given datetimestamp by taking the count of rows whose datetimestamp column is less than or equal to the given datetimestamp value. That should give us the current row number of the row with the given datetimestamp, regardless of row deletions.
Oh nice catch!. That is already a default value, but I accidentally deleted the number block. It comes with the default gridId in the test server. I'll update the screenshot right now. Thanks.
It should be the latter link.
As far as I know, there is no equivalent to SQL's update in Google's Query Language
Another workaround that you can employ would be to reserve one column, such as column A, to always be the equation "=ROW()". This will always be the row number of the cell and will also automatically update when you remove a row.
P.S. I forgot to mention this but the test server also has a ReadSheet block and GotSheetData event block in response to the request for:
I've updated the post to descrive this block under the Read Blocks header.
2 Likes
Hi! I am working on this! I do not see GoogleSheets1 under Storage. Do I have to update appInventor? Or import an extension? Thanks for any help you can give me!
Hi @ccdragos8,
You need to use the test server linked in the original post. Please be aware that this is just for testing and the API may change significantly and in potentially incompatible ways before it's released to production.
I have successfully added rows and retrieved row IDs.
I have also added the =row() formula successfully in my Add Row operation to act as a primary key for other tables' use as a foreign key.
However, I am having trouble adding more complex formulas, for example =sumif() against a table of order items matching this row number.
Here's the project and evidence. I am halfway between completing order row extra summation columns, and adding new order items.
APizzaSheet.aia (9.7 KB) APizzaSheet-b5f57497646b.json (2.3 KB)
I tried two variants of what to supply as a =sumif formula, one with single quotes around the row number test, and the other with double quotes, which works when manually inserted. Neither can be posted to a cell.
I shared the sheet for edit, so you can play.
Please don't mess up order row 2, my carefully crafted sumif formula.
Here are a couple of arrayformulas you can use, place in the header row:
For your rowID numbers (Orders:OrderNumbers, Menu:Menu Row, etc.)
={"header";arrayFormula(if(B2:B<>"",row(A2:A)-1,""))}
For your OrderTotalEstimate in Orders
={"OrderTotalEstimate";arrayFormula(if(A2:A<>"",sumif(OrderItems!$B:$B,A2:A, OrderItems!$H:$H),""))}
I have left these in place on the Orders sheet columns J & K if you want to use them
Note that the order numbers one will start at 1.
When using array forumlas it is best to remove all unused rows.
Those header arrayformulas are things of beauty!
They are good work arounds if I can't insert formulas with blocks.
They are very powerful!
Thanks!
@TIMAI2 @ABG and others, have you seen good use cases that might help @Tommy_Heng scope the work. We need to be careful that the blocks mainly support the use cases people might have for education purposes without going overboard in terms of complexity. There will always be people that want to push the bounds of what the component can do, but they're likely the 1% (if that) of App Inventor users.
In order of number of requests:
- Get data from sheet as csv (table - list of lists)
- AppendRow - upload list to a row in sheet
- Upload list of lists (table/csv) to sheet
- Get a value from a single cell in sheet
- CRUD - use google sheet "like" a database
- Query data on sheet
After that, most requests are about grabbing FileIDs after uploading files, posting to a sheet, which can then be downloaded for accessing those files in the app.
1 Like
Very good news, thank you so much.
By the way, using sql query to import csv sheet, i notice that spaces in a cell are not in the final result. Will this problem be solved in this new component?
I am still whacking away at my small Pizza Store sample, settling on collecting orders, order items, and fulfillment of the order items.
My sample is transactional, with orders and order item sheets meant to be periodically (daily/weekly) flushed.
I have given up on trying to insert formulas, except for the critical =row() value, which I need for a primary key in master records and a foreign key in detail records.
I am using arrayFormula headers, which can be set up in separate read-only sheets to provide summaries of detail items for matching master rows. Those are mostly for the WebViewer crowd. Now that I am getting into the SQL-like query language, I can probably leave that out and instead use query based filtration, grouping, and summation.
For data-only sheets, there is a conflict between the desire to keep a header row and to be able to easily clear the data in that sheet (currently a sequence of clear column calls followed by replacement of the newly cleared header row.
How many minutes or hours are you thinking of for a target student project size?
I still have a doc at https://docs.google.com/document/d/1jEx4wJsvGLfsMAjvY5ft2gT8LpTuS2ceLICO3frJaU4/edit?usp=sharing for another Pizza based sample (Fusion Tables) with a single table data model, that could be adapted for Google Sheets.
- Task allocation list for project members
- Shared Family shopping list
- Club/congregation shared contact list
- TicTac Toe (I caught a bug in a Spanish version recently)Tic Tac Toe (spanish Tutorial)
- Max scores (a perennial favorite)
- Riddle of the day
Thank you! I see it now!
(Start of wild goose chase...)
Has anything changed with the Google Sheets component since yesterday?
I am getting this error in the sheets server's Companion trying to get a sheet ...
Edit: attached is the latest .aia file:
APizzaSheet.aia (19.9 KB)
and this is the block that first triggers the error:
For lack of any other change, I suspect I have exhausted some Google-imposed limit on API access to the sheet(s).
On further study, I found this page at https://console.developers.google.com/apis/api/sheets.googleapis.com/credentials?folder=&organizationId=&project=apizzasheet
Did I miss something setting up my project?
(end of wild goose chase)
P.S. It was all me.
After I was all done soliciting help with arrayFormulas, I went to close off other users' edit capability to let me reshape the table structure. I set the Sharing option to Public With Link Can View, and forgot that it contradicted the initial setup instructions.
This error message went away after I reset the Public Sharing access to
I will have my demo error return event blocks check for 'oauth' in the error text, and issue a more useful error message pointing the developer to the public sharing settings.
This error pattern should probably go into the setup docs, in case other app authors make this mistake.
I apologize for the wild goose chase.
Les sauts de ligne ctrl+enter dans les cellules ne sont pas importés en sql query.
Ce problème sera-t-il résolu avec cette nouveautée?
I had not realized that this was an issue that could occur. Thanks for bringing this to my attention. I will definitly add this as a note in the Google Setup Docs.
I started to get strange errors from previously working code, working in the Companion on the sheets ai2 server:
This time, I did not change the sharing options of the sheets.
It worked a few hours ago, before dinner.
(the error message is enhanced by my error event code).
This is affecting all sheets blocks.
Unable to resolve host "oauth2.googleapis.com" is likely a problem with how Genymotion is talking to DNS (or rather, not talking). That's coming from further down the network stack and is telling us that the Android networking subsystem can't resolve the hostname, so it's not able to talk to DNS. From the browser in Genymotion are you able to access other Google services just fine (e.g., https://google.com?)
I tried using the native search box of my GenyMotion emulator reserved for Sheets testing, and I got an Internet connection error:
I had been using another GenyMotion emulator for ai2-test Companion testing earlier yesterday (AND/OR socket manipulation), and so this might indeed be a GenyMotion problem.
Thanks for the lead!