Reading Google FusionTable or Spreadsheet data into List

Google provides FusionTables and Spreadsheets web applications for maintaining tabular data. These Google applications allow users to create, update and modify data and share them with other users. Google also offers API for accessing these data using a subset of SQL statements sent over HTTP requests.

In this blog, I’ll show you to use App Inventor to issue SQL statements for accessing this data and how to convert data so that it can properly be displayed into such components as ListPicker or ListView.

Below are examples of Spreadsheet and FusionTable data that will be used for this blog.

Creating, providing sharing and getting the tableid or the key is beyond the scope of this post. However, if there are enough interest/comments, I will provide another post detailing the process.

As shown in figure below, our design will include 2 buttons to query Google Spreadsheet and FustionTable. We’ll use a Web component to send HTTP requests and receive results. We’ll use a label to display raw results and a ListView to display formatted list result.

Figure below shows the logic that is used to send query to FusionTable or Spreadsheet. It also shows the implementation that is used to convert the response from CSV Table to List. As seen in figure below, we are using URL endpoints to invoke RESTful API. The URL takes parameters such as table name, output format, SQL statement. Once the HTTP request is sent, a response will be sent back which will trigger Web GotText event handler. Please note that blocks below do not include any type of validations and in production, validation is required to ensure that response code of 200 is received back.

The responseConent that is received back is in form of CSV table like below (R = row, C = column):

• “R1C1″,”R1C2″,”R1C3”
• “R2C1″,”R2C2″,”R2C3”
• “R3C1″,”R3C2″,”R3C3”

We use “list from csv table” to convert responseContent to a list of list as shown below; keep in mind that parenthesis represent list:

((R1C1 R1C2 R1C3) (R2C1 R2C2 R2C3) (R3C1 R3C2 R3C3))

Data is now converted to list of lists and we now can set elements of our ListView to this list using ListView.Elements. However, since we are dealing with lists of lists, the elements in the ListView will include the inner list parenthesis and will look like below:

• (R1C1 R1C2 R1C3)
• (R2C1 R2C2 R2C3)
• (R3C1 R3C2 R3C3)

In order to remove the start and end parenthesis, I now use a procedure called stripParens that will loop through each list, strip the parenthesis and return result as a list:

Below shows the result querying the Google Spreadsheet

Source:

Download source HERE

Interested in Ai2LiveComplete? Please check features and link to demo site  HERE.  Alternatively, if you have problem accessing the doc, you can check document, HERE

Help with condition menu's

Hi, Is anyone able to help me with information on how to program conditional menus, 

What i am trying to do is this,

I have a make, when i select the make i get models that are specific to that make, when i choose a model i want to have a data ID ( is   Ford,  Escort,  1011)   put on the screen and then press an upload button and the dtaa is sent via bluetooth to an arduino.

I have found some info on making a two level menu but i cant find a way to make the 3 level and have the data appear on the screen.

Any help appreciated

--

Why not simplify things, create a csv file and then use:

This: Dynamic Table Layout now including Table Listpicker (!)

or

This: AI2. JavaScript. ListView with images and font. Panels with Horizontal Scroll.

If you really need to use AI2 lists then read :

⦁ How to work with Lists by Saj

⦁ How to work with Lists and Lists of lists (pdf) by appinventor.org

-- 

this would be a nice example to use the local SQLite database, see also my SQLite extension here 

SQlite Extension to access the local SQlite database

alternatively if your data changes frequently, you might want to consider a server database instead, for example a fusiontable, see the Pizza Party tutorial for details 

create a table with the following columns: make, model, dataId

then to get a list of the makes use this SELECT statement

SELECT make FROM <tablename> GROUP BY make

to get the model of a selected make use the following SELECT statement

SELECT model FROM <tablename> WHERE make = <selected make>

to get the data id fo a selected model, use the following SELECT statement

SELECT * FROM <tablename> WHERE model = <selected model>

you also could do this with data stored in JSON format and TinyDB, but this will be cumbersome, because you have to use lots of logic and blocks, see also this discussion here https://groups.google.com/d/msg/mitappinventortest/uF7G8tViRgk/w4ZVVJQiAQAJ

--

Take a look at my spreadsheet tutorial HERE to see if that helps you out.

--

But in answer to your question here are blocks and a small video

https://www.youtube.com/watch?v=-QgjTX019g4&feature=youtu.be

-- 

See this doc and app for how to code unlimited depth menus ...

Where’s MIT?

-- 

Input parameter

I have a java application like this :

case TelephonyManager.CALL_STATE_RINGING:
   // CALL_STATE_RINGING
   Toast.makeText(getApplicationContext(), incomingNumber,
         Toast.LENGTH_LONG).show();
   Toast.makeText(getApplicationContext(), "CALL_STATE_RINGING",
         Toast.LENGTH_LONG).show();
   System.out.println("IncomingNumber :" + incomingNumber);
   Intent intent = new Intent();
   Intent launchIntent = getPackageManager().getLaunchIntentForPackage("appinventor.ai_bernard_pre.IncommingCall");
   intent.setData(Uri.parse(incomingNumber));
   startActivity(intent);
   startActivity(launchIntent);

And I try to get input parameter (incomingNumber) in my application, using "GetStartValue" when Screen1 Initialize but I don't receive this parameter.

Thanks for your help.

--

see here Using the Activity Starter

To pass a startup text value to the App Inventor app you are starting with the Activity Starter, ise EXTRAS to set the KEY property to the string APP_INVENTOR_START and set the VALUE Property to the text you want to pass.  The other app can then retrieve this value with Get Start Plain Text.

-- 

Thanks for your help, all is working fine

In my java code :

private static final String ARGUMENT_NAME = "APP_INVENTOR_START";
.
.
.
Intent launchIntent = getPackageManager().getLaunchIntentForPackage("appinventor.ai_bernard_pre.IncommingCall");launchIntent.putExtra(ARGUMENT_NAME, incomingNumber);
startActivity(launchIntent);

And in App inventor I use get plain text

Thanks for all !

-- 

help with bloc

Hello I want to have the link of an image in my dropbox, I followed the steps of the documentation with qurl code request but the web response is 400

Help me, or is my fault, in the blocks

--

use POST instead of GET

-- 

refer to: dropbox.aia

--

How to implement this in Browser

How to implement this animation in Browser and I have made a browser app but my phone is not recognising it as browser what can I do that my phone recognise browser app as browser.

--

Is your question related to App Inventor?
If yes, explain how you want to use it and show some blocks of what you have tried.

--

Yes my question is from mit app inventor and I am not getting how to implement the Animation that when I search and that animation comes till the web is open like all browser.

-- 

I am really sorry, but I do not understand your question. Can you show the blocks of what you are trying to do?

--

He means the state of loading from a page like in his example picture.
The blue progess label.

--

The state of loading from a page like in  example picture.
The blue progess  Line .

-- 

The reason why you shouldn't use a progress bar in this case, is because there's no way to know how much of the webpage is loaded or still pending to load, so you can't know exactly how long that bar is at any time of the loading process.

Instead of showing that progress bar (that's how it's called, for the next time you need it), you can use a little square image component with a loading circle like the one shown below at the side of the url textbox and change it's angle with a clock to make it spin, until the page is fully loaded. When the page is finished loading, disable the clock and remove the image from the image component, so it doesn't show the loading circle anymore.

Also, about setting your app as a default browser, that is not a good idea. The webviewer component in App Inventor 2 is not a full featured browser and you will have a lot of problems trying to see some websites that use advanced features. It's a good little basic browser though, but not meant to replace a "real" browser.

-- 

Thanks for your information Italo but the question is that I will I know that the page is loaded completely.

-- 

Just an idea, I never did this before:

Try a clock that is enabled when the request for a new page is placed. This clock checks every 100 ms if the page title is different from the previous one (you should have the previous one saved in a global).

If it is, it means the page is loaded (hopefully, the title gets updated when it is fully loaded, you will have to try that).

If that doesn't work, try a search in the forum for this issue.

-- 

Actually I did the search for you and Taifun did that already (checking the title of the page). So it looks like it's a go. Do it!

https://puravidaapps.com/snippets1.php#waiting

-- 

ha ha, I didn't remember, that I once did that example in the old days...

-- 

Italo I have no words how to  thank you.You  solved my problem. I really appreciate you for your help.

-- 

Thanks taifun really appreciate you work.

-- 

Thanks for your help.

-- 

refer to:
com.puravidaapps.TaifunImage.aix
Resize.aia
chunk.aia
crop.aia
rotate.aia
overlay.aia

--

ShowChooseDialog button functionality

how to make ChooseDialog button do something for example "change Label.text"

--

Use the AfterChoosing block

-- 

but how to add a text box in the DialogMessage ?

-- 

If you want your user to enter text use the ShowTextDialog, the only option there is to cancel, but you can capture the text returned, OK or Cancel

-- 

setting a custom color to screen and horizontal alignment

I am creating an app for my project. To make the colors look nicer I want to set custom colors to the buttons, screen and horizontal alignment. I have attached a file of the interface I have created so far and also the blocks I added to try to add custom colors, but its not working. Someone please tell me what I am doing wrong and point me int the wrong direction. 

--

From your blocks I cannot see why it should not work. Can you explain what the symptoms are of "it is not working" .
Next time post screen shots, using the insert image button. That would make our life easier.

-- 

It is the empty socket on the HA background Color block that is causing the problem. It needs a colour then everything works as it should.

I used a contrasting colour so you could see it!

Either remove the block and set color to none in the designer or add a colour. 

Or add a fourth item to the colour list and use the value 0. This makes the colour transparent

-- 

+Tim Carter that was one of the problems and also one more issue was that the designer mode wasnt responding to the custom colours that I was adding in the blocks. That made me doubt it. What made the problem worse was that I didnt have a common wifi for the laptop and my mobile to check it in the mobile. Turns out it was working all along, It just wasnt getting reflected in the designer mode of the appinventor.

-- 

Sure. will do from next time. Thanks for your response I can count on this community for some help. 

--