App Inventor and the Web component


How to use a Google Drive Google Spreadsheet as Database

This is the App Inventor Classic example, it also is available for App Inventor here.

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

Version2.2: I now enhanced the example with an update and delete functionality.
For details, see here: How to UPDATE and DELETE rows in a Google Spreadsheet

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.

Do you need help with your App Inventor project?

I can help you: for one hour I charge 25$.
Please contact me at info [at] puravidaapps [dot] com for details!
For questions about App Inventor, please use the App Inventor forum. Thank you.

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

Note: Your spreadsheet must be shared to "Anyone with Link" or "Public" for this to work.

Screenshot


Download


If you find this example helpful and you are using it in one of your projects,
be nice and donate a small amount!

Thank you! Taifun
 

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

Back to top of page ...

Creative Commons License
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.


Home | Apps | Snippets | Tutorials | News | Forums | Links | Search | Contact