App Inventor and the Web component

How to use a Google Spreadsheet as Database

Please find the solution using the old forms, now called "Legacy Forms" here: Google Spreadsheet as Database (old solution)

using the new Forms

Bryan asked in the AI forum Well, Google has introduced a new Form, and it is unclear when the old Form [Legacy Form] will be deprecated. I have tried various things to get it to work [but unfortunately without success.]. Please find the solution below. Thank you Bryan for being the sponsor of this solution!

Version2.1: I now updated the example with an email functionality.
For details, see here: How to send a 'Contact Us' mail to a predefined email address automatically

Preparation

Important again is the correct preparation and to find the correct link to use. You need a Google spreadsheet, which also needs to be published as webpage in the internet and you need the webform to be able to enter data in the internet.

Links for this example:

1. This link is to view the Google spreadsheet from the web interface:
   https://docs.google.com/spreadsheet/ccc?key=0AoBdbKXka71-dHZTb2ctSGpBeWVzRmlXamlyTV8yLVE#gid=0
   
2. In the "Publish to Web" window you can get a link to the published data: select "webpage" and "sheet 1" to get this link: https://docs.google.com/spreadsheet/pub?key=0AoBdbKXka71-dHZTb2ctSGpBeWVzRmlXamlyTV8yLVE&single=true&gid=0&output=html. Now remove the last parameter &output=html to get the correct link for the GET command and to receive the result in csv format.
   
3. This link is to the webform that you can submit, anyone can fill in this form:
   https://docs.google.com/forms/d/1vDvP1c5Eq6-i55iNpm4KfjXGJxbMBngKsk2I6SLBv4Y/viewform
   
4. This is the link we use for the POST command, you also can get it from the page source of the webform link:
   https://docs.google.com/forms/d/1vDvP1c5Eq6-i55iNpm4KfjXGJxbMBngKsk2I6SLBv4Y/viewform
   Also you will get the columns names from the page source (see the highlighted values).
   In case you do not know how to view the page source, see here: How to view the source code from a HTML page.
   
   

Insert a Row (POST)

App Inventor Blocks

Screenshot

Select all Rows (GET)

With the GET command you will get all the data of the spreadsheet. If you prefer to work with select statements, you can use the Google Visualisation API example (see below). After posting data you have to wait until the published spreadsheet in the internet is updated before pressing GET to get the data back else you will not see your latest data.

App Inventor Blocks

Screenshot

Select Statement using the Google Visualisation API

The Google Visualisation API works for public spreadsheets and the best is, you can use it with SQLish commands. The example uses the Guardian datastore's MPs expenses spreadsheet 2007-8. Just try the different select statements yourself... The syntax is described here. Thank you Scott! And another example from Scott see here.

App Inventor Blocks

Screenshot

Download

Download zip file for App Inventor
Download zip file for App Inventor (Google Visualisation API example)

Back to top of page ...

This work by Pura Vida Apps is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License
with attribution (name=Pura Vida Apps and link to the source site) required.