Use gviz to get and query google sheet data

INTRO

gviz is an abbreviation for Google Visualization API which is used to access google sheets and google charts, among other things. In this tutorial we will focus on getting and querying data on google sheets, to work with or display this data in App Inventor apps.

If our spreadsheet is set with "anyone with the link" permissions, gviz can be used directly on the google sheet. If our google sheet permission is set to private, then we would need to use a google apps script web app, created by the owner of the google sheet, in order to use gviz. (This approach is covered in another howto or this one)

Any queries use the Google Query Language, which is very similar to SQL, so if we know how to query with SQL, we are most of the way there. However, there are differences, it is therefore best to refer to the Google Query Language Reference

There are several different methods for constructing query urls using gviz, here we will use a syntax which is closely aligned to other url building methods for google sheets that we see in App inventor, for consistency.

SETUP

• The spreadsheet we will use has a set of mock data

• The spreadsheet is set to "Anyone with the link", which allows direct access to the data

• The demo app will use a web component and a webviewer (more later) along with buttons and labels for display

• The app requires the user to type in the query

Here is a screenshot of the spreadsheet. We have here highlighted the spreadsheet ID, the grid ID (gid) and the grid (sheet) name. The grid we will be working with is called "myData", and to begin with this grid is positioned as the first grid on the spreadsheet (first from left)

The basic syntax used for a gviz query is as follows:

the link to the sheet

https://docs.google.com/spreadsheets/d/

plus the spreadsheet ID

1zdF7StPiiW-jTKGHuIyKtFvjHUcwerMwHCyIrH_HD4c

plus the gviz syntax

/gviz/tq?tq=

plus the query (here an example. if left blank, all the data is returned)

SELECT *

Put together we get:

https://docs.google.com/spreadsheets/d/1zdF7StPiiW-jTKGHuIyKtFvjHUcwerMwHCyIrH_HD4c/gviz/tq?tq=SELECT *

If you run this url in your computer browser, it will probably download a file called json.js, or it may display a json in your browser window. This is because the default output for gviz is JSON. Here is an example of some of the output:

/*O_o*/

google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"1220801139","table":{"cols":[{"id":"A","label":"id","type":"number","pattern":"General"},{"id":"B","label":"first_name","type":"string"},{"id":"C","label":"last_name","type":"string"},{"id":"D","label":"city","type":"string"},{"id":"E","label":"company","type":"string"},{"id":"F","label":"car_make","type":"string"},{"id":"G","label":"country","type":"string"},{"id":"H","label":"race","type":"string"}],"rows":[{"c":[{"v":1.0,"f":"1"},{"v":"Flori"},{"v":"Baudi"},{"v":"Tangguhang"},{"v":"Tagopia"},{"v":"Volvo"},{"v":"Indonesia"},{"v":"Mexican"}]},{"c":[{"v":2.0,"f":"2"},{"v":"Nonie"},{"v":"Hovey"},{"v":"Millerovo"},{"v":"Mynte"},{"v":"Toyota"},{"v":"Russia"},{"v":"Houma"}]},{"c":[{"v":3.0,"f":"3"},{"v":"Royall"},{"v":"Wyrall"},{"v":"Cancas"},{"v":"Edgepulse"},{"v":"Lincoln"},{"v":"Peru"},{"v":"Honduran"}]},{"c":[{"v":4.0,"f":"4"},{"v":"Terrel"},{"v":"Worgan"},{"v":"Siemianowice Śląskie"},{"v":"Meejo"},{"v":"Chrysler"},{"v":"Poland"},{"v":"Choctaw"}]},{"c":[{"v":5.0,"f":"5"},{"v":"Tyson"},{"v":"Wernham"},{"v":"Ibiporã"},{"v":"Brainbox"},{"v":"Dodge"},{"v":"Brazil"},{"v":"Japanese"}]},

Not easy to read, or parse. Fortunately, we can enhance our url, and adapt the syntax to output the data in csv format, using tqx:

https://docs.google.com/spreadsheets/d/1zdF7StPiiW-jTKGHuIyKtFvjHUcwerMwHCyIrH_HD4c

plus

/gviz/tq?tqx=out:csv&tq=

plus

SELECT *

Put together this now reads:

https://docs.google.com/spreadsheets/d/1zdF7StPiiW-jTKGHuIyKtFvjHUcwerMwHCyIrH_HD4c/gviz/tq?tqx=out:csv&tq=SELECT *

and in your computer browser will return either a file named data.csv or print to screen:

"id","first_name","last_name","city","company","car_make","country","race"

"1","Flori","Baudi","Tangguhang","Tagopia","Volvo","Indonesia","Mexican"

"2","Nonie","Hovey","Millerovo","Mynte","Toyota","Russia","Houma"

"3","Royall","Wyrall","Cancas","Edgepulse","Lincoln","Peru","Honduran"

"4","Terrel","Worgan","Siemianowice Śląskie","Meejo","Chrysler","Poland","Choctaw"

"5","Tyson","Wernham","Ibiporã","Brainbox","Dodge","Brazil","Japanese"

Much more accessible. We will use tqx=out:csv when using the web component. App Inventor will not try to download a file, it will capture the data as text, and return it to the responseContent in the Web1.GotText event block. We can then work with the data as a list.

NOTE: If you want to query the value or content of a column then for a string (letters or letters & numbers) you must surround them by SINGLE quotes,

 e.g. SELECT A,B,C WHERE B = 'Steve',  or  SELECT A,B,C WHERE B = 'Dave 124'

 If you are querying numbers only then you do not need the quotes,

e.g. SELECT A,B,C WHERE A = 24

In the example above, we have applied the same basic url with csv output. The query returns just the first three columns and the first 10 rows. Note the headers are included. The gviz query will reference the first grid(sheet) from the left in the spreadsheet. This is currently "myData" - where out data is.  We have kept the label output to just responseContent for display purposes, we would usually convert to a list from csv table, and set this to a variable.

What happens then if the grid (sheet) is not first from the left? We have a choice. we can either use the grid(sheet) name, e.g. "myData", or we can use the "gid", e.g. "1820138425".

Here you can see we have moved the grid "myData" to be the 4th grid on the spreadsheet.

and below are the two methods available to access the data.

1. using the grid/sheet name:

https://docs.google.com/spreadsheets/d/1zdF7StPiiW-jTKGHuIyKtFvjHUcwerMwHCyIrH_HD4c/gviz/tq?tqx=out:csv&sheet=myData&tq=SELECT+B%2CC%2CD+where+C+contains+%27S%27

2. Using the gid:

https://docs.google.com/spreadsheets/d/1zdF7StPiiW-jTKGHuIyKtFvjHUcwerMwHCyIrH_HD4c/gviz/tq?tqx=out:csv&gid=1820138425&tq=SELECT+A%2CC%2CD+where+B+contains+%27R%27

We can also work on just a range of the dataset if we wish. We simply identify the range in A1 notation, and then the query will only reference that data. of course, if we leave the query blank, just that range will be returned. In the example below we select the sheet "myData" and the range A1:D10.

https://docs.google.com/spreadsheets/d/1zdF7StPiiW-jTKGHuIyKtFvjHUcwerMwHCyIrH_HD4c/gviz/tq?tqx=out:csv&sheet=myData&range=A1:D10&tq=SELECT+A%2CB%2CC+where+A+%3E+4

HTML

We mentioned the webviewer earlier. Another option for tqx is to output to html. This is useful if we only want to display the data in our app as a simple table. Our tqx syntax requires a simple change:

/gviz/tq?tqx=out:html

and we call the url with the webviewer goto block, instead of using the web component

https://docs.google.com/spreadsheets/d/1zdF7StPiiW-jTKGHuIyKtFvjHUcwerMwHCyIrH_HD4c/gviz/tq?tqx=out:html&sheet=myData&range=A1:D10&tq=SELECT+A%2CB%2CC

Note: if the headers are not returned in the header row, add... &headers=1 ...to the url

If you would like to view the data as an html table (from gviz) but also need to return all the table values as data, you can run some javascript on the webpage after it has loaded:

js as text:

let Table = document.getElementsByTagName('table')[0];

let data = [...Table.rows].map(t => [...t.children].map(u => u.innerText))

window.AppInventor.setWebViewString(JSON.stringify(data));

This returns a stringified JSON array, which you can convert to an AI2 list using the Web component's JsonTextDecode block

AIA and FILES

gvizQueryDemo.aia

RESOURCES

https://developers.google.com/chart/interactive/docs/querylanguage#top_of_page

https://acrl.ala.org/techconnect/post/query-a-google-spreadsheet-like-a-database-with-google-visualization-api-query-language/

https://consulity.de/content/GASSAppVsGViz.aspx