App Inventor and the Web component

How to use a Google Spreadsheet as Database

This example uses a Google Spreadsheet as database. INSERT and SELECT statements are (more or less) simple, we use the web component and the post and get blocks for that. To be able to UPDATE and DELETE, I added some logic using Google Apps Script, see details below.

This solution also offers an email functionality.
For details, see here: How to send a 'Contact Us' mail to a predefined email address automatically

Preparation

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

last update: August 1st, 2020.

1. Open your Google Drive and create a new spreadsheet, you can rename the spreadsheet as you like.
2. Go to Tools - Create a form to create a webform. Edit your form to your needs. In the example I used 4 text fields: Name, eMail, Message, ACTION and set the question type to "short answer". The form will be saved automatically.
   
3. Now if you take a look at your spreadsheet, you will find a "Form Responses 1" sheet next to "Sheet 1". The "Form Responses 1" sheet now contains your columns and additionally a timestamp column as first column.
4. In the spreadsheet select File - Share, click the "Advanced" button and change the access from Private (which is default) to Anyone with the link and change the access type from "Can View" (Default) to "Can Edit" and click Save and then Done.
5. Now select File - Publish to the weband in the next window click Publish. A window pops up, just click OK and Close.

Links to use in the example

1. GET Url
   In your spreadsheet select File - Share and copy the link from the "Link to Share" box. Then replace the text edit?usp=sharing by export?format=csv. The correct URL will look like this:
   
2. POST Url
   In the spreadsheet select Form - Go To live form. We now have to take a look at the source code of the webform. How to view the source code of a HTML page. You can find the Post Url in the form action tag of the page source
   
   
3. entry markers
   this is the simple method by TimAI2, Thank you Tim!
   - Open the Google Form in Edit Mode
   - Click the 3 dot menu and select Get pre-filled link
   - Fill out the fields in the form opened with sample data
   - Click on Get Link button at the bottom
   - Click on the COPY LINK words in the popup
   - Paste what you have copied into a text editor
   You should then see all the "entry" markers with the numbers for each (like "entry.498951954"), and your sample entries
4. SELECT Url
   if you want to use the Visualisation API, copy the GET Url and modify it accordingly to get the SELECT Url, see screenshot.
   

INSERT, UPDATE and DELETE

Screenshots

App Inventor Blocks

UPDATE and DELETE

In case you want to UPDATE and DELETE, you can find the details how to do it here.

I used some lines of Google Apps Script to enable UPDATE and DELETE statements. The example spreadsheet had 3 columns: name, email and message text. I now added another column ACTION to the spreadsheet. Depending on that column, an UPDATE or DELETE will be triggered by the Google Apps Script. The name column is used as key. Of course you also can update or delete multiple rows!

UPDATE example

Define the 4 columns: name="Taifun2", email="my updated email", message="let's update", ACTION="UPDATE" and click "POST" in the App Inventor example app. The script will be triggered and executes the following statement (pseudo code):

   UPDATE spreadsheet SET email="my updated email", message="let's update" WHERE name="Taifun2"

DELETE example

Define the 2 columns: name="Taifun", ACTION="DELETE" and click "POST" in the App Inventor example app. The script will be triggered and executes the following statement (pseudo code):

   DELETE FROM spreadsheet WHERE name="Taifun2"

Preparation

1. In your Google Spreadsheet open the Script Editor via Tools - Script Editor and copy the 3 functions from the source code below.
2. Add a trigger via the Current Project Triggers icon.
   
3. Click the "No triggers set up. Click gere to add one now." link. In column "Run" select the function "action" from the dropdown list. In column "Events" select "From spreadsheet", "on form submit" and click save.
   
4. A window "Authoriztation required" pops up. Click "Review Permissions", choose our Google account, a window "This app isn't verified" appears, click "Advanced"
   
5. and click "Select Go to SPREADSHEET NAME (unsafe)" and in the following screen that asks for permission, select "ALLOW".
6. Ready!

Script Source Code

//  author: puravidaapps.com
//
// reference documentation
// sheet: https://developers.google.com/apps-script/reference/spreadsheet/sheet
// range: https://developers.google.com/apps-script/reference/spreadsheet/range
function action(e) {
  var key    = e.values[1]; // the entered name, column 2
  var action = e.values[4]; // the entered action (UPDATE or DELETE), column 5
  var sheet  = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  Logger.clear();
  Logger.log('action=' + action);

  if (action == "DELETE"){
    del(key, sheet, values);
  }
  if (action == "UPDATE") {
    //upd(key, sheet, values);
    upd2(key, 1, sheet, values);
  }
}

function del(key, sheet, values){
  Logger.log('DELETE ' + key);
  // http://stackoverflow.com/a/13410486/1545993
  for(var row = values.length -1; row >= 0; --row){
    if (values[row][1] == key){
      sheet.deleteRow(parseInt(row)+1); // loop is 0-indexed, deleteRow is 1-indexed
    }
  } // end: for
}

// Version 1, deprecated
function upd(key, sheet, values){
  var lastRow = sheet.getLastRow();
  for(i in values){
    if (values[i][1] == key){
      Logger.log('UPDATE i=' + i);
      var rangeToCopy = sheet.getRange(lastRow, 1, 1, 4); // getRange(row, column, numRows, numColumns), do not copy action column
      rangeToCopy.copyTo(sheet.getRange(parseInt(i)+1, 1));
    }
  } // end: for
  sheet.deleteRow(lastRow); // delete last row: this is the update statement
}

// Version 2, author: Charles
function upd2(key, key_column, sheet, values){
  var lastRow = values.length - 1;

  // Remove 'UPDATE' from column 5 (ACTION column)
  sheet.getRange(parseInt(lastRow)+1, 5).clear();

  // Remove entries with same key.
  for(var row = lastRow - 1; row >= 0; --row){
    if (values[row][key_column] == key){
      sheet.deleteRow(parseInt(row)+1); // loop is 0-indexed, deleteRow is 1-indexed
    }
  } // end: for
}  

Result after INSERT:


Result after UPDATE:


Result after DELETE:
well, nothing to see, the row disappeared ;-)

Questions and Answers

Q1: First, thank you for your tutorial. However, I think the upd() method you provided can definitely use some improvement. Here is a little background. When multiple UPDATE is called with different key (as in key in the code), the upd() is inefficient and data from the wrong key is retrieved. I had experimented with only updates of two different keys one each from a javascript on a page. This in a way provides a multi-threaded or multi-user situation, which often happen in real world.

One fact - by the time upd() is called, the row with the current UPDATE is already written. Yet in upd() code, the values in the row are being copied over again and the latest row from the UPDATE is then removed in the end. One of the issues is that you are calling sheet.getLastRow() to get the last row - and this does not always get you the UPDATE with the same key in a multi-user situation where a new row with a different key may be added by then. And, the second issue is why copy over to the original row and delete the new one when deleting the original one is faster and less error prone.

In my test that demonstrates retrieval of wrong data, the query was something like select B where F = xxxx order by A desc - in this case F is the key, and B has the data I am looking for, and A is the Google Spreadsheet added timestamp. And, I stuffed the value of the key itself in B so I can tell if I got the right data.

With the original upd(), there is a script doing UPDATE with key1 56 times, and another with key2 265 times. The error occurs fairly quickly within 10 updates. With the following upd() code, I no longer get any such error to the end of the whole tests. Please feel free to use it in your tutorial, but please note the hard-coded column may need to be changed in order to work in your tutorial.
A: Thank you very much Charles! I now added your upd() method into the example. Here, always column name is used as key.

GET (Select all rows)

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.

Screenshot

App Inventor Blocks

SELECT using the Google Visualization API

The Google Visualisation API works for public spreadsheets and the best is, you can use it with SQLish commands!
In the example I used the following query: select C,D where B matches 'Taifun'

Note: see also the detailed guide by TimAI2 here.

App Inventor Blocks

Screenshot

Test

Tested successfully on Nexus 5X running Android 8.1.

Download

Download aia file for App Inventor (to test with your own spreadsheet)
Download apk file (to test with my spreadsheet)
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.