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!
The problem was transient. I was able to Google search natively from other GenyMotion instances, then my sheets testing emulator miraculously regained its Internet access, restoring sheets block capabilities.
My current guess is that I had rushed some Genymotion network setup/teardown activity when switching emulators.
I'll file this under "remember for the future".
Thanks again.
I finished the code for my sample Sheets app, a Pizza Shop.
It turned out to be more than I bargained for, in scope.
Here's the source, and some screen shots ...
APizzaSheet.aia (37.7 KB)
Initial order entry requires a name, and logs the time.
A List Picker lets you pick from the Menu, without duplication.
An Item level Add button completes the Add operation.
Order Item Data is kept in a separate sheet, tied to the corresponding Order in the OrderData sheet by Order RowID, the row number.
Using the row number as a foreign key from the item level key forces us into a zero-delete scheme, where the deletion is done by a Manager at end of day or week. To limit calls to Google Sheets, the deletion is done by columns (a fixed number of calls ~15) instead of by rows, and the headings of the two emptied out tables are replaced afterwards.
A parallel set of ListViews provides access to Order Items, at the Order level and at the Unfilled category level:
The top level menu of the app has an option for a counterman to see all the items he has yet to fill. Selecting an item brings up the rest of the order that contains that item.
A Management branch of the top level menu offers a sales summary query and periodic cleanup of the data.
(I did not bother cleaning up the Sales Summary data.)
I relied on Google Sheets SQL to do my grouping and summarization.
It would have been nice to use my headers for the SQL instead of the column letters, but I could not get that to work.
1 Like
3 posts were split to a new topic: Google sheets: You do not have permission to access the requested document
This looks flippin' amazin' !
Is it ready yet?
Everything worked really well until I used "saved as" to generate a newer version. Now I'm getting "method" errors on original and new versions....?
More specifically: Runtime error.
Error from companion: invoke: no method named 'Application name' in class com.google.....GoogleSheets.
no, it is not, it is under development
and therefore you have to expect errors...
feel free to test...
but for production you should wait, until a stable version is released in production
Taifun
Trying to push the limits! Snippets, Tutorials and Extensions from Pura Vida Apps by 
Taifun.
The sheets test server also has its own Companion version.
You will get errors like this if you use the wrong Companion.