Built In Google Sheets Component for App Inventor

[ABG]

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.

[ABG]

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.

[everythingability]

This looks flippin' amazin' !

Is it ready yet?

[Murray]

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.

[Taifun]

@everythingability

Is it ready yet?

no, it is not, it is under development

@Murray

More specifically: Runtime error.
Error from companion: invoke: no method named 'Application name' in class com.google.....GoogleSheets.

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.

[ABG]

The sheets test server also has its own Companion version.
You will get errors like this if you use the wrong Companion.

[Raj_B]

ABG How do I download this?

[ewpatton]

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.

[Raj_B]

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.

[Murray]

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.

[Murray]

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.

[Tommy_Heng]

@Murray

Something like "Clear(A3:G)" to clear all cells in columns A to G below row 2.

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!

[Murray]

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.

[nomad13]

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

[ABG]

@nomad13

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.

[kdmc]

Hi,

I am testing google sheet component at,

http://sheets-dot-ai2-ewpatton-temp.uc.r.appspot.com/

Whenever I am trying to build, it is giving error,

Build failed! ai2-buildserver-nightly-ewp.csail.mit.edu

Even those apps which have been built previously, if compiled again to export as .apk, same error is coming.

Thanks for the help.

Regards

kdmc

[TIMAI2]

Possible that the test server is not setup to build....

[kdmc]

Thank you TIMAI2 for the reply. But I have used this server for building until yesterday. Since today morning this error is coming.