APIs: Get data from a google docs spreadsheet

[Scott Ferguson]

This is based on the example provided by puravidaapps HERE.
An example spreadsheet was found on the internet to be used for testing.

Note that two errors have been identified in this demo:
1. the example query in the app is missing a trailing close parenthses ')'
2. https must be used rather than http in the url
---sfs

The Web, component was used to query and retrieve the total murder victims in 2006 from the above spreadsheet.

Note that 'Total' is in column A and the value for 2006 is in column D.

The query then becomes: select D where (A matches 'Total')      -use 'matches' for exact matches and 'contains' otherwise 

                                                                                            -'and' and 'or' are allowed 

                                                                                            -more than one column of data can be retrieved by separating with commas: select B,C,D where...

The spreadsheet must be public.

Note also that the letters and numbers following the 'key=' parameter in the spreadsheet's address bar. This tells the query where to get the data.

The query of the spreadsheet is performed:

The data is retrieved as a list into the text label lblResult:

The output after running the query:

Attachments (1)

GetSpreadsheetData.zip

4 KB   View   Download

This message has been deleted.

me (scottfromscott change)

Web replies disabled Mar 23

I found what I believe is the syntax reference for the Google Docs query select statements here:

http://code.google.com/apis/chart/interactive/docs/querylanguage.html#Language_Syntax

Scott

me (scottfromscott change)

Web replies disabled Mar 23

This enhancement to the above exercise allows the developer to enter a spreadsheet query on the screen to test different results. The query syntax can be found HERE.
In this exercise, I demonstrate totaling the values on a row. (Totaling columns of numbers is more easily done using the sum() function.)
The query is: select A, B + C + D + E + F where A = 'Rifles'
The output columns are column A (weapon type) and a calculation that sums the values on the table row containing the name 'Rifles'.

The modificaitons to the blocks are only to allow the user to enter the query on the screen rather than having it hard coded as in the previous example.

Not that all the UriEncode block does is to change space characters to %20 and commas to %2C. You could do the substitution manually in the URL for the query if you wanted to.

The screen designer:

AI project attached.

[Joy King]

thanks especially for this one!  it's helping me alot to get my app up & running...  more on this subject (csv files, querying on subject then subtopic too) will be gratefully appreciated!  (Even if I don't get to them as often as I'd like to!)...  Your work is glowing, Scott!  Thanks so much....

Joy

On Saturday, September 29, 2012 5:59:23 PM UTC-4, Scott Ferguson wrote:

This is based on the example provided by puravidaapps HERE.
An example spreadsheet was found on the internet to be used for testing.

[Bryan de Valdivia]

Hi Scott,

I just tried this example, and I've got the same error that I got with Taifun's example.

However, it's not the bug that was reported, Error -1, it is Error 301, Moved Permanently.

Does this mean that the MIT server bug has been fixed?  Maybe it's just a Uri syntax change now?

Best,

b.

[Scott Ferguson]

I entered the URL directly into a web browser:
http://spreadsheets.google.com/tq?tqx=out:csv&key=0AkfdpmTPWWUEdHhpSXZxMnh4VUlNVTF5WURkRU1VQVE&tq=select A,D where (A matches 'Poison' or A matches 'Narcotics')

I opened the returned csv file into notepad, the result looks correct:
"Expanded Homicide Data Table 8 Murder Victims by Weapon, 2004–2008 Weapons","2006"
"Poison",12
"Narcotics",48

So the query itself still works  -- just not in App Inventor for some reason.
---sfs

[Scott Ferguson]

In the AI project I get that the document has moved -- then why does it still work when the same url is entered into the URL box in my Firefox web browser (without URL encoding the query)???
---sfs

[Scott Ferguson]

I found the problems (2):
1. the example query in the app was missing a trailing close parenthses ')'

2. https must be used rather than http in the url

---sfs

[Bryan de Valdivia]

Awesome Scott!  Works!!

Thank You.

Bryan

--
(you have received this message from the App Inventor Developers Library)
---
You received this message because you are subscribed to a topic in the Google Groups "App Inventor Developers Library" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/app-inventor-developers-library/c-beFFVk8PY/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to app-inventor-develope...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Fabio Antonucci]

Hello Scott,

many thanks for the useful templates and easy explanations!!!

I have a question regarding queries...

How to make the user able to search for a specific data in a column?

Something like... select B where A = 'E511' and the user may be able to change the code E511 or any other code for which there is a columns stating for it's safety or less (those codes are used to identify preservatives and colourants in EU) .

Thanks in advance for your attention and time.

Fabio

 

On Saturday, September 29, 2012 11:59:23 PM UTC+2, Scott Ferguson wrote:

This is based on the example provided by puravidaapps HERE.
An example spreadsheet was found on the internet to be used for testing.

Note that two errors have been identified in this demo:

1. the example query in the app is missing a trailing close parenthses ')'

2. https must be used rather than http in the url
---sfs

[Scott Ferguson]

According to the language reference here, this method can display filtered data but doesn't seem able to update spreadsheet data.
I believe Taifun at Puravidaapps.com has a technique or two to update spreadsheets.
See this example.
---sfs

[Fabio Antonucci]

Thanks for the replay, Scott!
I do not need to update data but just understand how to have a part of the query insert by the user.

Data are not dynamic.

[Fabio Antonucci]

Solved.

below the pic, hope may help other people too :)

maybe there is a more elegant one but this one works

https://docs.google.com/file/d/0B16RIH5xR-6aSUtPbHROZTBVVVU/edit?usp=sharing

[Fabio Antonucci]

[Scott Ferguson]

Aha! Now I see what you were talking about.
Thanks for sharing that solution :)
---sfs

[Claus Wøbbe]

Actually, the spreadsheet does not have to be public. The only requirement is that the user can read it, so it's ok just to share it with the people, who will be using the app.

I used this technique in my first "real" app (yes, it's: https://play.google.com/store/apps/details?id=appinventor.ai_ClausWobbe.MineTjeklister&feature=search_result#?t=W251bGwsMSwyLDEsImFwcGludmVudG9yLmFpX0NsYXVzV29iYmUuTWluZVRqZWtsaXN0ZXIiXQ..)

This app lets a person create and update templates for checklists that can be shared among e.g. a workgroup in a company. Or in a family. The point is, the spreadsheet in not public - only shared with relevant people.

[Scott Ferguson]

Thanks for clearing that up :-)
---sfs

[Krzysztof Piotrowski]

Hello Scott

I have question. How can I use the UPDATE? I need to replace the value in the cell the new value. Can I also delete rows (as in SQL DELETE)?

[Scott Ferguson]

My method is for filtering data only.
Taifun's method here
http://puravidaapps.com/taifunGS.php
allows adding a row and getting values, but I don't know of a way to update an existing value or to do a delete.
---sfs

[Joe Emory]

Anyone had luck querying a specific sheet in a spreadsheet?

I know I can simply move the sheet tab to be the leftmost one. I've tried adding the "#gid=1" to the end of the key but that didn't work. 

Do I have to call out the name of the sheet in the Query? Maybe: select Budget!B:B, Budget!C:C, Budget!D:D where (Budget!A:A matches 'Inclusive') ?

[Taifun]

try adding &gid=1 for the second sheet

see also here https://developers.google.com/chart/interactive/docs/spreadsheets?hl=de-DE#Google_Spreadsheets_as_a_Data_Source

Taifun

Use the URL in your visualization query. The query supports the following optional parameters:

• gid=N - Specifies which sheet in a multi-sheet document to link to, if you are not linking to the first sheet. N is the sheet's ID number, an integer zero or greater. It is one less than the number in the sheet name when it is created: for example, gid=0 for Sheet1. This parameter should be supplied for you in step 5. You can also learn the ID number by navigating to the published version of that sheet and looking for the gid=N parameter in the URL. You can use the sheet parameter instead of this parameter. Example: gid=5.
  

---

Trying to push the limits of App Inventor! Snippets and Tutorials from Pura Vida Apps by Taifun.