Built In Google Sheets Component for App Inventor

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:
Capture Get Rows with Query Exception null

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)
Capture New Order Entry start
Initial order entry requires a name, and logs the time.
Capture New Order Item selected but not yet added

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.
Capture Top Level Menu
A Management branch of the top level menu offers a sales summary query and periodic cleanup of the data.
Capture Sales Summary Output
(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


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.

ABG How do I download this?

Every App Inventor service provides a corresponding companion. You can get the companion for sideloading on your device by going to Help > Companion Information on the test server.

Ok. Thanks. I wish I had seen this last night - may have saved some remaining hair! Suggest put this at at the start of the thread - need to uninstall existing companion and download from the Test.

Today I updated to the new Companion version and everything is working again. This is a great tool!

I miss the ability to be able to clear a range. Something like "Clear(A3:G)" to clear all cells in columns A to G below row 2.

For instance when my table has had rows removed, I want to clear the table first before writing the updated rows. Maybe there is an easy way of doing this with the query function? It seems cumbersome having to define a range of null values and then writing that to the spreadsheet.

1 Like

To kind of answer my own question: It can be done by removing individual columns and then replacing headers as in Pizza example. Still, would be nice to have more compact function for this common task.

1 Like

Consider it done! I've added a method ClearRange(String sheetName, String range) and an accompanying callback event for when it finishes.

I'll wait a little bit to see if there are any more suggestions before pushing the new method to the test server to send more features at once. I'll edit the original post (and this comment) once the test server has been updated!

1 Like

Great! This will save having to adjust column width after every save or when wanting to check data. Really enjoying this component! So cool that you can write functions such as =COUNT(K3:K) etc to paste in cells.

Is there a way of creating/adding a new sheet? I would like to add sheet based on name or date.

From a data management perspective, it is simpler to just add columns for name and date and then filter your queries by name and date.